DataTable Comparison Using LINQ Except, Intersect and Union

This article describes you how to compare DataTables to get matched, unmatched, combine list of all rows in both tables.

You can use DataTable extension methods like Except to get matched DataRows, Intersect to get unmatched DataRows and Union to get unique combine list of DataRows from all DataTables.

For this tutorial I am using two DataTables filled with DataRows with details of students.

Open your visual studio and create a console application. In Program.cs file declare two DataTables one for students of Math classes and second for students of English classes.

Open Program.cs file and below code to create DataTables and add student details


namespace CompareDataTables
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dtMaths = new DataTable("Maths");
            dtMaths.Columns.Add("StudID", typeof(int));
            dtMaths.Columns.Add("StudName", typeof(string));
            dtMaths.Rows.Add(1, "Mike");
            dtMaths.Rows.Add(2, "Mukesh");
            dtMaths.Rows.Add(3, "Erin");
            dtMaths.Rows.Add(4, "Roshni");
            dtMaths.Rows.Add(5, "Ajay");

            DataTable dtEnglish = new DataTable("English");
            dtEnglish.Columns.Add("StudID", typeof(int));
            dtEnglish.Columns.Add("StudName", typeof(string));
            dtEnglish.Rows.Add(6, "Arjun");
            dtEnglish.Rows.Add(2, "Mukesh");
            dtEnglish.Rows.Add(7, "John");
            dtEnglish.Rows.Add(4, "Roshni");
            dtEnglish.Rows.Add(8, "Kumar");
        }            
    }
}
    
        

Except to get unmatched data rows

You can use Except to get unmatched records from first table. You will be using DataTable.AsEnumerable().Except().

Add below code in program.cs file after adding student details in DataTable.

            
Console.WriteLine("Students enrolled only for Maths");

DataTable dtOnlyMaths = dtMaths.AsEnumerable().Except(
        dtEnglish.AsEnumerable(), DataRowComparer.Default).
            CopyToDataTable();

foreach(DataRow dr in dtOnlyMaths.Rows)
{
    Console.WriteLine(string.Format("StudentID: {0},
      StudentName: {1}", dr[0].ToString(), dr[1].ToString())); 
}
            
        

This will give you all rows which are added into DataTable dtMaths but not in dtEnglish. It should return Student details with ID 1,3 and 5.

To get rows from dtEnglish which are not in dtMaths. Use below code

                 
Console.WriteLine("Students enrolled only for English");
DataTable dtOnlyEnglish = dtEnglish.AsEnumerable().Except
        (dtMaths.AsEnumerable(), DataRowComparer.Default).
            CopyToDataTable(); 
                           
foreach (DataRow dr in dtOnlyEnglish.Rows)
{
    Console.WriteLine(string.Format("StudentID: {0}, 
     StudentName: {1}", dr[0].ToString(), dr[1].ToString()));
}
            

This should return Student details with ID 6,7 and 8.


Intersect to get matched rows of DataTables

You will use Intersect extension method on DataTable in similar way as you did in previous step. Intersect will give you all rows which are in both the tables or list. It compares all fields and if all values of all fields are similar in both tables include in result set.

Use below code to get all rows which are similar in dtMaths and dtEnglish.


Console.WriteLine("Student enrolled in both Math and English");
DataTable dtIntersect = dtMaths.AsEnumerable().Intersect
    (dtEnglish.AsEnumerable(), DataRowComparer.Default).
    CopyToDataTable();

foreach (DataRow dr in dtIntersect.Rows)
{
    Console.WriteLine(string.Format("StudentID: {0}, 
    StudentName: {1}", dr[0].ToString(), dr[1].ToString()));
}
    
            

This should return Student details with ID 2 and 4.


Union to get unique combined list

Union method gives you combined list from all DataTables. It does not repeat the DataRow even it exists in multiple DataTables.

Add below code to get list of students from dtMaths and dtEnglish as well.


Console.WriteLine("List of all students");
DataTable dtBoth = dtMaths.AsEnumerable().Union
    (dtEnglish.AsEnumerable(), DataRowComparer.Default).
    CopyToDataTable();

foreach (DataRow dr in dtBoth.Rows)
{
    Console.WriteLine(string.Format("StudentID: {0},
    StudentName: {1}", dr[0].ToString(), dr[1].ToString()));
}
    
            

You should see below output.

compare data tables linq except, intersect, union

You can also convert your DataTables to various formats. For more info see Convert DataTable to CSV or List Or JSON String.

Download source code.

Speak your mind :
Leave a comment for this article on dotnetbloogers.com