DataTable Comparison Using LINQ Except, Intersect and Union

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 declares 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");
    }            
}
}

For more details on Program.cs implementation click here

Except to get unmatched data rows

You can use Except to get unmatched records from the 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. This code 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 the 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 the 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 a 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.

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