Thursday, November 5, 2009

Join, Intersec, Expect options on Data Table / Dataset using 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 datatables.
*/
        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.
*/


1 comment:

Followers

Search This Blog