Linq to DataSet

In contrast, the LINQ to DataSet is the smallest piece of LINQ technology, although it is an off-line operation model extracted from the DB, but after all, the object is also a memory of an object. So most of the operations are the same as LINQ to object, except that the fields are marked according to the structure of the dataset,datatable. The following is a simple list of the features that LINQ to DataSet should pay attention to compared to the LINQ to object.

Query untyped DataSet

When the query object is a untyped dataset compared to the normal LINQ, using field<t> and setfield<t> to read and write the different column fields, here is a simple example:

 
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;

Here's a general note of three.

1. Because the untyped dataset does not implement the interface of Ienumerable<t> and iqueryable<t>, it is necessary to use asenumerable () if you want to make it a query object. or asqueryable () to convert it to a ienumerable<t> or Iqueryable<t> object in order to query with LINQ. such as: Fromo in orders. AsEnumerable ()

2. It is generally used using field<t> ("column A") and setfield<t> ("column A") to read and write the corresponding element of the different Column fields, using it to access the DS relative to the previous one. tables["Orders"]. One of the great benefits of row["Rowa" ["Column A"] is that the exception generated by the null type can be avoided. We used to fetch the data from the dataset, if it was null, it would throw exception, so we often make similar if (ds. tables["Orders"]. Row[the judgment of "Rowa" ["Column A"]!=null) to wrap our further logic, but using field<t> ("column A") can avoid this kind of trouble. Because Field<t> ("Column A") is nullable. The origin of this feature is the use of the generic <T>, such as when you take the int type data, if you think it may be null, then you can use Field<int?> ("Column A") to fetch, This prevents the exception from being thrown.

3. Field<t> and setfield<t> are used in a query that is not limited to LINQ and can be used elsewhere in the program, and can be used instead of the way we used to access a dataset, such as:

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

Query Typed DataSet

It's even easier. For a dataset that defines a type, we can query it as if it were a generic object in memory. For example:

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

There is also a different place from the untyped dataset where you do not need to use asenumerable () or asqueryable () as a conversion method when querying it. Because all well-defined datasets inherit the base class of typedtablebase<t>, this base class has implemented the Ienumerable<t> interface

Query DataSet in the relation

The dataset is sometimes relation, as in db, for example by adding relation to the following dataset:

DataTable orders = ds.Tables["Orders"];
DataTable orderDetails = ds.Tables["OrderDetails"];
ds.Relations.Add( "OrderDetails",
                  orders.Columns["OrderID"],
                  orderDetails.Columns["OrderID"]);

If we want to use relation to access a table in the same way as LINQ to SQL, using the GetChildRows method to get the DataRows in the table associated with the current table, and returns it as a Iqueryable<t> object that can be queried. For example:

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

So we can access the object table through relation.

Source: >

Linq to DataSet