Post
Ask Question

Using LINQ to Query a DataSet

A DataTable can be queried with LINQ, just as any other IEnumerable<T> list.

Note: DataTable does not implement IEnumerable<T>. You have to call AsEnumerable, which is an extension method for DataTable, to obtain a wrapper that implements that interface.

The list is made of DataRow objects; thus, you must access DataRow member properties to get a field value. This arrangement allows the call of any DataRow member instead of using a query expression over a DataTable. You can use the Field<T> accessor method instead of using a direct cast on the result of the standard DataRow accessor (such as o["OrderDate"]). The query shown in below code gets the orders that show a date of 1998 or later.

Querying a DataTable with LINQ

  1. DataSet ds = LoadDataSetUsingDataAdapter();  
  2. DataTable orders = ds.Tables["Orders"];DataTable orderDetails = ds.Tables["OrderDetails"];  
  3. var query = from o in orders.AsEnumerable()  
  4. where o.Field < DateTime > ("OrderDate").Year >= 1998  
  5. orderby o.Field < DateTime > ("OrderDate") descending  
  6. select o;  
Note: AsEnumerable and Field<T> are two custom extension methods for DataTable and DataRow types. They are defined in System.Data.DataTableExtensions and System.Data.DataRowExtensions, respectively.

When you have several DataTable objects in a DataSet, you might want to use some type of join. The query shown in below code calculates the total order amount for each order from 1998 to the present.

Joining two DataTable objects with LINQ
  1. DataSet ds = LoadDataSetUsingDataAdapter();  
  2. DataTable orders = ds.Tables["Orders"];  
  3. DataTable orderDetails = ds.Tables["OrderDetails"];  
  4. var query = from o in orders.AsEnumerable()  
  5. join od in orderDetails.AsEnumerable()  
  6. on o.Field < int > ("OrderID") equals od.Field < int > ("OrderID")  
  7. into orderLines  
  8. where o.Field < DateTime > ("OrderDate").Year >= 1998  
  9. orderby o.Field < DateTime > ("OrderDate") descending  
  10. select new  
  11. {  
  12.     OrderID = o.Field < int > ("OrderID"),  
  13.         OrderDate = o.Field < DateTime > ("OrderDate"),  
  14.         Amount = orderLines.Sum(  
  15.             od => od.Field < decimal > ("UnitPrice") *  
  16.             od.Field < short > ("Quantity"))  
  17. };  
In the previous examples, you specified the relationship between orders and orderDetails through the join syntax. If the DataSet contains information about existing relationships between entities, a LINQ query can take advantage of this. In below ex, we use GetChildRows to get the lines for the order details instead of explicitly joining the two tables.

Leveraging DataSet relationships in LINQ queries:
  1. DataSet ds = LoadDataSetUsingDataAdapter();  
  2. DataTable orders = ds.Tables["Orders"];  
  3. DataTable orderDetails = ds.Tables["OrderDetails"];  
  4. ds.Relations.Add("OrderDetails", orders.Columns["OrderID"], orderDetails.Columns["OrderID"]);  
  5. var query = from o in orders.AsEnumerable()  
  6. where o.Field < DateTime > ("OrderDate").Year >= 1998  
  7. orderby o.Field < DateTime > ("OrderDate") descending  
  8. select new  
  9. {  
  10.     OrderID = o.Field < int > ("OrderID"),  
  11.         OrderDate = o.Field < DateTime > ("OrderDate"),  
  12.         Amount = o.GetChildRows("OrderDetails").Sum(  
  13.             od => od.Field < decimal > ("UnitPrice") *  
  14.             od.Field < short > ("Quantity"))  
  15. };