Compare Two Data tables using LINQ in C#
Sometimes we have requirement to compare two data tables and get the extra records from one table which are not there in another data table. We can easily compare the two data tables with less effort by using LINQ in C#.
For example we have tow XML files as data sources for two datatables and we have a requirement to get the extra records from first xml file which are not there in second xml file. Create two XMl files as shown below.
FirstTable.xml
<?xmlversion="1.0"encoding="utf-8" ?>
<Employees>
<Employee>
<Id>1</Id>
<Name>A</Name>
</Employee>
<Employee>
<Id>2</Id>
<Name>B</Name>
</Employee>
<Employee>
<Id>3</Id>
<Name>C</Name>
</Employee>
<Employee>
<Id>4</Id>
<Name>D</Name>
</Employee>
</Employees>
SecondTable.xml
<?xmlversion="1.0"encoding="utf-8" ?>
<Employees>
<Employee>
<Id>1</Id>
<Name>A</Name>
</Employee>
<Employee>
<Id>3</Id>
<Name>C</Name>
</Employee>
</Employees>
Now read these xml files into two dataset objects and display these xml data sources using gridviews as shown below.
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="LINQCompareDataTables._Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Compare two Datatables using LINQ in C#</title>
</head>
<body>
<form id="form1" runat="server">
<div style="width:700px;margin-left:200px;">
<div style="float:left;">
<label>First Datatable</label>
<asp:GridView ID="gv1" runat="server"></asp:GridView>
</div>
<div style="float:right;">
<label>Second Datatable</label>
<asp:GridView ID="gv2" runat="server"></asp:GridView>
</div>
</div>
<div style="clear:both;height:10px;"></div>
<asp:Button ID="btn" runat="server" Text="Compare Two Datatables based on Id" OnClick="btn_Click" />
<div style="clear:both;height:10px;"></div>
<label>Data from First Table which is not there in Second Table</label>
<div style="margin-left:"300px;">
<asp:GridView ID="gv3" runat="server"></asp:GridView>
</div>
</form>
</body>
</html>
Default.aspx.cs
using System;
using System.Data;
namespace LINQCompareDataTables
{
public partial class _Default : System.Web.UI.Page
{
DataSet ds1 = new DataSet();
DataSet ds2 = new DataSet();
protected void Page_Load(object sender, EventArgs e)
{
LoadTables();
}
private void LoadTables()
{
try
{
ds1.ReadXml(Server.MapPath("FirstTable.xml"));
ds2.ReadXml(Server.MapPath("SecondTable.xml"));
gv1.DataSource = ds1.Tables[0];
gv1.DataBind();
gv2.DataSource = ds2.Tables[0];
gv2.DataBind();
}
catch (Exception ex)
{ }
}
}
}
Compare these two xml files through datatable objects on button click by calling CompareTables() user-defined method as shown below.
protected void btn_Click(object sender, EventArgs e)
{
CompareTables(ds1.Tables[0], ds2.Tables[0]);
}
private void CompareTables(DataTable dt1, DataTable dt2)
{
try
{
var dt1Records = dt1.AsEnumerable().Select(e1 => new { Id = e1["Id"].ToString(), Name = e1["Name"].ToString() });
var dt2Records = dt2.AsEnumerable().Select(e2 => new { Id = e2["Id"].ToString(), Name = e2["Name"].ToString() });
var extraRecords = dt1Records.Except(dt2Records);
gv3.DataSource = extraRecords;
gv3.DataBind();
}
catch (Exception ex)
{ }
}
As shown above we are reading the two datatable objects into two annonyomus types dt1Records, dt2Records and comparing these two objects using Except method in Asp.Net. dt1Records.Except(dt2Records) provides the recors from dt1Records object which are not there in dt2Records object and we can bind result set in another gridview as shown above or we can loop each record by using for-each statment as shown below.
private void CompareTables(DataTable dt1, DataTable dt2)
{
try
{
var dt1Records = dt1.AsEnumerable().Select(e1 => new { Id = e1["Id"].ToString(), Name = e1["Name"].ToString() });
var dt2Records = dt2.AsEnumerable().Select(e2 => new { Id = e2["Id"].ToString(), Name = e2["Name"].ToString() });
var extraRecords = dt1Records.Except(dt2Records);
string Id;
string Name;
foreach (var emp in extraRecords)
{
Id = emp.Id;
Name = emp.Name;
}
}
catch (Exception ex)
{ }
}
