|
|
|
|
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 );
}
}
|
|
|
|