ArticleDirectory
- LINQ to dataset Overview
- Query untyped Dataset
- Query Typed Dataset
- Relation in query Dataset
LINQ to dataset Overview
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 and setfield 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 oInOrders. asenumerable ()
WhereO. 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 interfaces of ienumerable and iqueryable, if you want to use it as a queryable object, you must first convert it with asenumerable () or asqueryable, only the iqueryable or iqueryable object can be converted to ienumerable. For example, from o in orders. asenumerable ()
2. generally, field ("column A") and setfield ("column A") are used to read and write the elements corresponding to different column fields. 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 ("column A") can avoid this problem. Because field ("Column A") is nullable. This feature comes from the use of this generic type. For example, if you think it may be null when you take int data, you can use field ("column ") to avoid throwing an exception.
3. The use of field and setfield is not limited to the query of LINQ.ProgramCan also be used in other places, you can use it to replace the previous way we access 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, and this base class has implemented the interface of ienumerable
Relation in query Dataset
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 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.