Linq To DataSet, linqtodataset

Linq To DataSet, linqtodataset

Relatively speaking, linq to DataSet is the smallest piece in The LINQ technology. Although it is an offline operation model extracted from the database, after all, the object is only an object in the memory. Therefore, most operations are the same as those of linq to Object. The difference is that fields must be indicated according TO the DataSet and able structure. The following briefly lists some of the notes that need TO be paid attention TO compared TO the special functions of linq to DataSet.

Query UnTyped DataSet

Compared with General LINQ, when the query object is untyped DataSet, Field <T> and SetField <T> are used to read and write different column fields. Below 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;

Pay attention to three points here

1. because untyped DataSet does not implement the interface of IEnumerable <T> and IQueryable <T>, to use it as an object that can be queried, use AsEnumerable () or AsQueryable () convert it to an IEnumerable <T> or IQueryable <T> object to be queried using LINQ. For example:From o in orders. AsEnumerable ()

2. generally, Field <T> ("Column A") and SetField <T> ("Column A") are used to read and write the elements corresponding to different column fields, it is used to access. tables ["Orders"]. compared to the access mode of Row ["RowA"] ["Column A"], A major benefit is to avoid exceptions of the null type. When we used to retrieve data from DataSet, if the obtained data is null, an exception is thrown. Therefore, we often perform operations similar to if (ds. tables ["Orders"]. row ["RowA"] ["Column A"]! = Null), but Field <T> ("Column A") can avoid this problem. Because Field <T> ("Column A") is nullable. The reason for this feature is <T> the usage of this generic type. For example, if you think it may be null when you take the int type data, then you can use Field <int?> ("Column A") to get, so that exception throws can be avoided.

3. field <T> and SetField <T> are not limited to the query of LINQ. They can also be used elsewhere in the program. They can be used to replace the previous method for accessing DataSet, for example:

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

Query Typed DataSet

This is simpler. For DataSet with a defined type, we can query it like an object in the 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 ) };

Another difference from untyped DataSet is that you do not need to use the conversion method like AsEnumerable () or AsQueryable () When querying it. Because all the defined DataSet inherit the base class TypedTableBase <T>, and this base class has implemented the interface of IEnumerable <T>

Query DataSetInRelation

Sometimes there is relation in DataSet, just like DB. For example, add 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 access a table related TO the table through relation in the same way as in linq to SQL, we can use the GetChildRows method TO obtain the DataRows In the table associated with the current table, and return it as an 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" ) ) };

In this way, we can access the object table through relation.

Source: http://www.cnblogs.com/ruciffa/archive/2008/03/08/1096007.html>