Linq to ADO.NET parent/child query help

Question

Hello

I want to use Linq to ADO.NET to fetch all rows that match the criteria below from a DataTable.

• Select all rows where "parentId" equals "id" of a row where "parentId" equals null.
• Order by "Name".

Can someone tell me how to accomplish this (preferably using both Query Syntax and Method Syntax), and possibly point me to where I can read more about this topic?

Answer 1

There is no such thing as "Linq to ADO.NET" (perhaps you're confusing with ADO.NET Entity Framework). In your case, you seem to be referring to Linq to DataSets

You could do something like that :

Query syntax :

var parents = from row in table.AsEnumerable()
              where row.IsNull("parentId")
              select parents;

var children = from row in table.AsEnumerable()
               where parents.Any(p => p.Field<int>("id") = row.Field<int>("parentId"))
               orderby row.Field<string>("Name")
               select row;

Method syntax :

var parents = table.AsEnumerable()
              .Where(row => row.IsNull("parentId"));

var children = table.AsEnumerable()
               .Where(row => parents.Any(p => p.Field<int>("id") = row.Field<int>("parentId")))
               .OrderBy(row => row.Field<string>("Name"));