Several common examples of LINQ to Dataset

This article is a reference to the related section of the "decisive battle. Net -.net Framework 3.5" written by Mr. Huang zhongcheng,

And applied it to your own project, and found that it is very useful, so I will share several examples.

If you encounter such problems in the project: ableable1 and datatable2 need to be combined into a ableable3 according to certain rules.

Question 1: datatable1 is not the result of reading a database table, but a merged dataset. Therefore, it cannot be queried using a combination of SQL statements.

Question 2: datatable1 and ableable2 are very complex queries and the result set is very large, so if datatable1 and ableable2

Combined Query is prone to SQL Execution timeout.

When the above problem occurs, the first idea is to put the two datatables into the memory and reorganize them to get the datatable3.

After the release of. net, you can use this more concise and easy-to-understand SQL-like syntax to solve the above problems. Below are some examples for sharing.

Using system. LINQ;
Using system. text;
Using system. Data;

 

1. Basic Methods:

Dataset DS = new dataset ();
Adapter. Fill (DS, "MERs"); // read the database

VaR result = From S1 in DS. Tables ["MERs"]. asenumerable ()

Where s1.field <string> ("region") = "SP" // convert to string for comparison. If it is dbnull, an empty string is returned.
Select S1;

Foreach (VAR item in result)
Console. writeline (item ["mermerid"]);
Console. Readline ();

 

2. Specify datarowversion:

VaR result = From S1 in DS. Tables ["MERs"]. asenumerable ()
Where! S1.isnull ("region") & (string) S1 ["region"] = "SP"
Select S1;

 

3. join query:

VaR result = From S1 in DS. Tables ["MERs"]. asenumerable ()
Join S2 in DS. Tables ["orders"]. asenumerable () on
S1.field <string> ("customerid") equals s2.field <string> ("customerid ")
Where s2.field <datetime> ("orderdate")> datetime. parse ("1997/1/1 ")&&
S2.field <datetime> ("orderdate") <datetime. parse ("1997/5/31 ")
Select New
{
Orderid = s2.field <int> ("orderid "),
Customername = s1.field <string> ("companyName "),
Orderdate = s2.field <datetime> ("orderdate ")
};

4. Group

VaR result = from S in DS. Tables ["orders"]. asenumerable ()
Group S by S. Field <string> ("customerid") into G
Select New
{
Orderid = G. First (). Field <int> ("orderid "),
Customerid = G. Key
};

5. Return the first 10 rows of data

VaR table = (From S1 in DS. Tables ["MERs"]. asenumerable () Select S1). Take (10 );