DataTable / DataSet and Linq

1. Querying DataTable using Linq 
    Here i am going to explain how you can use linq to get data from DataTable.
    You need to use AsEnumerable() method of DataTable object to query datatable using linq.
Example :  
DataTable dt = new DataTable();
dt.Columns.Add("ID");
dt.Columns.Add("Name");
DataRow dr = dt.NewRow();
dr["ID"] = "1";
dr["Name"] = "Test1";
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["ID"] = "2";
dr["Name"] = "Test2";
dt.Rows.Add(dr);

var result = from user in dt.AsEnumerable()
                     where user.Field<string>("ID") == "1"
                     select user;

2. Join Two DataTable using Linq.
     Example : Here i take two datatable.

        DataTable dt = new DataTable();
        dt.Columns.Add("ID");
        dt.Columns.Add("Name");
        DataRow dr = dt.NewRow();
        dr["ID"] = "1";
        dr["Name"] = "Test1";
        dt.Rows.Add(dr);
        dr = dt.NewRow();
        dr["ID"] = "2";
        dr["Name"] = "Test2";
        dt.Rows.Add(dr);

        DataTable dt1 = new DataTable();
        dt1.Columns.Add("ID");
        dt1.Columns.Add("Product");
        dr = dt1.NewRow();
        dr["ID"] = "1";
        dr["Product"] = "Test-Product";
        dt1.Rows.Add(dr);

Linq Query:

var c = from p in dt.AsEnumerable()
join d in dt1.AsEnumerable() on p.Field<string>("ID") equals d.Field<string>("ID")
select new { ID = p.Field<string>("ID"), Name = p.Field<string>("Name"), Product = d.Field<string>("Product") };

3. Except and Intersect operation between two DataTable using Linq .

        DataTable dt = new DataTable();
        dt.Columns.Add("ID");
        dt.Columns.Add("Name");
        DataRow dr = dt.NewRow();
        dr["ID"] = "1";
        dr["Name"] = "Test1";
        dt.Rows.Add(dr);
        dr = dt.NewRow();
        dr["ID"] = "2";
        dr["Name"] = "Test2";
        dt.Rows.Add(dr);

        DataTable dt1 = new DataTable();
        dt1.Columns.Add("ID");
        dt1.Columns.Add("Name");
        dr = dt1.NewRow();
        dr["ID"] = "1";
        dr["Name"] = "Test1";
        dt1.Rows.Add(dr);

        var  exceptresult = dt.AsEnumerable().Except(dt1.AsEnumerable(), System.Data.DataRowComparer.Default);

         var intersectresult  = dt.AsEnumerable().Intersect(dt1.AsEnumerable(), System.Data.DataRowComparer.Default);

For both of above operation you need to specify DataRowComparer . System.Data.DataRowComparer is a static sealed class and its static property Default contain comparer reference.  You must have to specify comparer for this operation on Reference datatype.

Next blog i will give you CustomDataRow Comparer to make it more efficient.