Find Intersecting DataRows in a Data Table List

Question

I have a list. I would like to filter all the rows in the list of tables to find all the rows that are in each list of data in the list.

If possible, the comparison should be in the "ID" column, which is on each row.

I tried to solve this with Linq, but was stuck. This is what I have so far:

List<DataTable> dataTables = new List<DataTable>(); // fill up the list List<DataRow> dataRows = dataTables.SelectMany(dt => dt.Rows.Cast<DataRow>().AsEnumerable()). Aggregate((r1, r2) => r1.Intersect(r2)); 

Any suggestions?

Answer 1

Not a simple question. Here's the solution (which seems to me too complicated, but it works).

• Get Id value from each row using Linq in DataSets
• Cross multiple lists to find all common values
• Find a single occurrence of a row in all rows that have one of the corresponding identifiers

To use Linq in a DataTable, see this article to get started.

You can get identifiers from one table, for example

 var ids = dt.AsEnumerable().Select (d => d.Field<int>("ID")).OfType<int>(); 

and from several tables

 var setsOfIds = dataTables.Select ( t => t.AsEnumerable().Select (x => x.Field<int>("ID")).OfType<int>()); 

To cross multiple lists, try this article . Using one of the methods, you can get the intersection of all identifiers.

Using the Jon Skeet helper method

 public static class MyExtensions { public static List<T> IntersectAll<T>(this IEnumerable<IEnumerable<T>> lists) { HashSet<T> hashSet = new HashSet<T>(lists.First()); foreach (var list in lists.Skip(1)) { hashSet.IntersectWith(list); } return hashSet.ToList(); } } 

we can write

 var commonIds = setsOfIds.InsersectAll(); 

Now smooth out all the rows from the DataTables and filter the common identifiers:

 var rows = dataTables.SelectMany (t => t.AsEnumerable()).Where( r => commonIds.Contains(r.Field<int>("ID"))); 

Now group by id and take the first instance of each row:

 var result = rows.GroupBy (r => r.Field<int>("ID")).Select (r => r.First ()); 

Answer 2

Try to find this intersection between the two lists:

 r1.Join(r2, r1 => r1.Id, r2 => r2.Id, (r1, r2) => r1);