Union DataTable

Question

Could you check the following, what is wrong with that.

I need a union of this, but it returns 6 records instead of 5 (because "Amir" occurs twice)

DataTable dt1 = new DataTable(); dt1.Columns.Add(new DataColumn("Name")); dt1.Rows.Add(dt1.NewRow()["Name"] = "Imran"); dt1.Rows.Add(dt1.NewRow()["Name"] = "Amir"); dt1.Rows.Add(dt1.NewRow()["Name"] = "Asif"); DataTable dt2 = new DataTable(); dt2.Columns.Add(new DataColumn("Name")); dt2.Rows.Add(dt2.NewRow()["Name"] = "Tandulkar"); dt2.Rows.Add(dt2.NewRow()["Name"] = "Amir"); dt2.Rows.Add(dt2.NewRow()["Name"] = "Sheqwag"); DataTable dtUnion = dt1.AsEnumerable() .Union(dt2.AsEnumerable()).CopyToDataTable<DataRow>(); 

Answer 1

The problem is that Linq does not know that you want to compare Name . Instead, it does what it does for all types of objects, it compares a hash that is different for two different instances.

What you need todo is to tell the Union method how to compare the two elements. You can do this by creating a custom IEqualityComparer that compares two rows of data the way you want it.

Here is an example implementation:

 class CustomComparer : IEqualityComparer<DataRow> { #region IEqualityComparer<DataRow> Members public bool Equals(DataRow x, DataRow y) { return ((string)x["Name"]).Equals((string)y["Name"]); } public int GetHashCode(DataRow obj) { return ((string)obj["Name"]).GetHashCode(); } #endregion } 

When calling Union you need to pass an instance of this comparator:

 var comparer = new CustomComparer(); DataTable dtUnion = dt1.AsEnumerable() .Union(dt2.AsEnumerable(), comparer).CopyToDataTable<DataRow>(); 

See here for more information:
http://msdn.microsoft.com/en-us/library/bb358407.aspx

Tip: Linq is best for custom data classes, but DataRow is not. It is better to have the actual Name property in the class, only then Linq can really shine.
If you don't want the flexibility of a dynamic schema, you should stay away from the DataTable and implement custom classes that resemble exactly what you need, since the DataTable extremely bloated and slowed down.

Answer 2

If your DataTables schemas are the same, you can simply use the existing DataRowComparer.Default, for example:

 DataTable dtUnion = dt1.AsEnumerable().Union(dt2.AsEnumerable()).Distinct(DataRowComparer.Default).CopyToDataTable<DataRow>(); 

And the Aggregate function is very convenient when you need to combine more than two tables, for example:

 // Create a table "template" DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("Name")); // Create a List of DataTables and add 3 identical tables List<DataTable> dtList = new List<DataTable>(); dtList.AddRange(new List<DataTable>() { dt.Clone(), dt.Clone(), dt.Clone()}); // Populate the 3 clones with some data dtList[0].Rows.Add("Imran"); dtList[0].Rows.Add("Amir"); dtList[0].Rows.Add("Asif"); dtList[1].Rows.Add("Tandulkar"); dtList[1].Rows.Add("Amir"); dtList[1].Rows.Add("Sheqwag"); dtList[2].Rows.Add("John"); dtList[2].Rows.Add("Sheqwag"); dtList[2].Rows.Add("Mike"); // Union the 3 clones into a single DataTable containing only distinct rows DataTable dtUnion = dtList .Select(d => d.Select().AsEnumerable()) .Aggregate((current, next) => current.Union(next)) .Distinct(DataRowComparer.Default) .CopyToDataTable<DataRow>();