Tuesday, March 27, 2012

C# - Implement MINUS Operation in DataTables using LINQ

LINQ can be used to effectively implement the MINUS operation in DataTables.
Suppose we have 2 DataTables A & B and if we need to remove the rows which are present in DataTable B from the DataTable A, then this can be achieved using a LINQ Query

DataTable A
DataTable B
DataTable Result = (A - B)


C# Example
DataTable dtIncludeCodes, dtExcludeCodes, dtResultCodes;
DataRow dr;
//
dtIncludeCodes = new DataTable();
dtIncludeCodes.Columns.Add("Code", typeof(string));
//
dr = dtIncludeCodes.NewRow();
dr["Code"] = "1";
dtIncludeCodes.Rows.Add(dr);
dr = dtIncludeCodes.NewRow();
dr["Code"] = "2";
dtIncludeCodes.Rows.Add(dr);
dr = dtIncludeCodes.NewRow();
dr["Code"] = "3";
dtIncludeCodes.Rows.Add(dr);
dr = dtIncludeCodes.NewRow();
dr["Code"] = "4";
dtIncludeCodes.Rows.Add(dr);
dr = dtIncludeCodes.NewRow();
dr["Code"] = "5";
dtIncludeCodes.Rows.Add(dr);
dr = dtIncludeCodes.NewRow();
dr["Code"] = "6";
dtIncludeCodes.Rows.Add(dr);
dr = dtIncludeCodes.NewRow();
dr["Code"] = "7";
dtIncludeCodes.Rows.Add(dr);
dr = dtIncludeCodes.NewRow();
dr["Code"] = "8";
dtIncludeCodes.Rows.Add(dr);
dr = dtIncludeCodes.NewRow();
dr["Code"] = "9";
dtIncludeCodes.Rows.Add(dr);
dr = dtIncludeCodes.NewRow();
dr["Code"] = "10";
dtIncludeCodes.Rows.Add(dr);
//
dtExcludeCodes = new DataTable();
dtExcludeCodes.Columns.Add("Code", typeof(string));
dr = dtExcludeCodes.NewRow();
dr["Code"] = "4";
dtExcludeCodes.Rows.Add(dr);
dr = dtExcludeCodes.NewRow();
dr["Code"] = "5";
dtExcludeCodes.Rows.Add(dr);
dr = dtExcludeCodes.NewRow();
dr["Code"] = "6";
dtExcludeCodes.Rows.Add(dr);
//
var resRows = from r in dtIncludeCodes.AsEnumerable()
where !dtExcludeCodes.AsEnumerable().Any(r2 => r["Code"].ToString().Trim().ToLower() == r2["Code"].ToString().Trim().ToLower() && r["Code"].ToString().Trim().ToLower() == r2["Code"].ToString().Trim().ToLower())
select r;
//
dtResultCodes = resRows.CopyToDataTable();

Here the DataTable dtIncludeCodes has values 1,2,3,4,5,6,7,8,9,10.
The DataTable dtExcludeCodes has values 4,5,6.

After executing the LINQ expression the resulting DataTable dtResultCodes will contain the values 1,2,3,7,8,9,10

That's it, very simple to implement MINUS using LINQ.

Search Flipkart Products:
Flipkart.com

2 comments:

Anonymous said...

Thanks,
Really its helpful.

From:
http://sharepointbuild.blogspot.com/

Unknown said...

which version of .net did you use? .net 4.0?