LINQ to DataSet

The .NET native System.Data.DataSet is an in-memory representation of a set of data. It is useful to get a disconnected copy of data that comes from an external data source. Regardless of the data source, the internal representation of a DataSet follows the relational model, including tables, constraints, and relationships among the tables. In other words, you can consider the DataSet as a sort of in-memory relational database. This makes it a good target for a LINQ implementation.

Using LINQ to Load a DataSet

A DataSet can be loaded by querying a relational database. One possible way to do this is through a DataAdapter, as shown in Listing 5-27.

Listing 5-27: Loading DataSet using a DataAdapter

  DataSet ds = new DataSet("CustomerOrders"); SqlDataAdapter da = new SqlDataAdapter( QueryOrders, ConnectionString ); da.SelectCommand.Parameters.AddWithValue( "@CustomerID", "QUICK" ); da.TableMappings.Add( "Table", "Orders" ); da.TableMappings.Add( "Table1", "OrderDetails" ); da.Fill( ds ); const string ConnectionString = "Database=Northwind;Trusted_Connection=yes"; const string QueryOrders = @" SET @CustomerID = 'QUICK' SELECT  OrderID, OrderDate, Freight, ShipName,         ShipAddress, ShipCity, ShipCountry FROM    Orders WHERE   CustomerID = @CustomerID SELECT     od.OrderID, od.UnitPrice, od.Quantity, od.Discount,            p.[ProductName] FROM       [Order Details] od INNER JOIN Orders o       ON   o.[OrderID] = od.[OrderID] LEFT JOIN  Products p      ON    p.[ProductID] = od.[ProductID] WHERE      o.CustomerID = @CustomerID"; 

The previous code combines two DataTable instances into one DataSet, which corresponds to the orders placed by a specific customer.

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 Listing 5-28 gets the orders that show a date of 1998 or later.

Listing 5-28: Querying a DataTable with LINQ

  DataSet ds = LoadDataSetUsingDataAdapter(); DataTable orders = ds.Tables["Orders"]; DataTable orderDetails = ds.Tables["OrderDetails"]; var query =     from    o in orders.AsEnumerable()     where   o.Field<DateTime>( "OrderDate" ).Year >= 1998     orderby o.Field<DateTime>( "OrderDate" ) descending     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 Listing 5-29 calculates the total order amount for each order from 1998 to the present.

Listing 5-29: Joining two DataTable objects with LINQ

  DataSet ds = LoadDataSetUsingDataAdapter(); DataTable orders = ds.Tables["Orders"]; DataTable orderDetails = ds.Tables["OrderDetails"]; var query =     from    o in orders.AsEnumerable()     join    od in orderDetails.AsEnumerable()             on o.Field<int>( "OrderID" ) equals od.Field<int>( "OrderID" )             into orderLines     where   o.Field<DateTime>( "OrderDate" ).Year >= 1998     orderby o.Field<DateTime>( "OrderDate" ) descending     select  new { OrderID = o.Field<int>( "OrderID" ),                   OrderDate = o.Field<DateTime>( "OrderDate" ),                   Amount = orderLines.Sum(                                od => od.Field<decimal>( "UnitPrice" )                                      * od.Field<short>( "Quantity" ) ) }; 

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 Listing 5-30, we use GetChildRows to get the lines for the order details instead of explicitly joining the two tables.

Listing 5-30: Leveraging DataSet relationships in LINQ queries

  DataSet ds = LoadDataSetUsingDataAdapter(); DataTable orders = ds.Tables["Orders"]; DataTable orderDetails = ds.Tables["OrderDetails"]; ds.Relations.Add( "OrderDetails",                   orders.Columns["OrderID"],                   orderDetails.Columns["OrderID"]); var query =     from    o in orders.AsEnumerable()     where   o.Field<DateTime>( "OrderDate" ).Year >= 1998     orderby o.Field<DateTime>( "OrderDate" ) descending     select  new { OrderID = o.Field<int>( "OrderID" ),                   OrderDate = o.Field<DateTime>( "OrderDate" ),                   Amount = o.GetChildRows( "OrderDetails" ).Sum(                                od => od.Field<decimal>( "UnitPrice" )                                      * od.Field<short>( "Quantity" ) ) }; 

Using LINQ to Query a Typed DataSet

A typed DataSet can be queried with a simpler syntax because it is not necessary to use the Field<T> accessor and the AsEnumerable method.

Note 

If you create the typed DataSet with Visual Studio, your typed DataTable classes will be derived from the TypedTableBase<T> class, which implements the IEnumerable<T> interface. For this reason, it is not required to call AsEnumerable to get a wrapper.

The previous query, which we also used to leverage the existing DataSet relationships, can be written as shown in Listing 5-31, which uses a typed DataSet.

Listing 5-31: Querying a typed DataSet with LINQ

  var query =     from    o in ds.Orders     where   o.OrderDate.Year >= 1998     orderby o.OrderDate descending     select  new { o.OrderID, o.OrderDate,                   Amount = o.GetOrder_DetailsRows().Sum(                                od => od.UnitPrice * od.Quantity ) }; 

As you can see, the query syntax is much simpler and similar to the one we used earlier to query other type of entities. However, you must use a predefined schema (the typed DataSet) to query DataSet in such a way, and this prevents the use of this syntax with DataSet containing a flexible schema defined at execution time. This does not mean that you should use an untyped DataSet; it only emphasizes that untyped DataSets can be queried only with the Field<T> accessor.

Accessing Untyped DataSet Data

Accessing data in an untyped DataSet requires the use of the Field<T> and SetField<T> accessors to get and set field values, respectively. These accessors are important because a null value in a DataSet is represented by the IsNull method returning true. You should check this condition each time you access a column just to avoid potential cast errors. The use of these accessors is allowed in any DataTable or DataRow access, even outside a query expression, as you can see in Listing 5-32.

Listing 5-32: Querying an untyped DataSet with LINQ

  foreach( DataRow r in orderDetails.Rows ) {     if (r.Field<decimal>( "UnitPrice" ) < 10 ){         r.SetField<decimal>( "UnitPrice", 10 );     } }