Implement complex data query using LINQ to dataset [Part 1]

5.2 implement complex data query using LINQ to Dataset
LINQ to dataset integrates LINQ with ADO. net. It obtains data through ADO. NET and then queries data through LINQ, so as to perform complex queries on the dataset. This section describes how to use LINQ to dataset to operate data in a dataset.

5.2.1 use LINQ to Dataset
To query the data stored in dataset, you can simply understand that the data stored in dataset is not much different from the data saved in the LINQ query described in Chapter 7th. The usage of LINQ to dataset usually includes the following steps:

(1) obtain the dataset/datatable data source. To query data in dataset/able through LINQ, you must first prepare the dataset/datatable data source. You can use ADO. NET technology can be obtained from the database, XML technology can be obtained from the XML file, can also be obtained from any form of data sources, or even directly create and fill the dataset/able object in the memory.

(2) convert the datatable to ienumerable <t> type. From Chapter 7th, we learned that only query operations can be performed on ienumerable <t> or iqueryable <t> interface objects, while datatable does not implement these two interfaces and cannot directly query. In LINQ to dataset, get an equivalent ienumerable <t> object from datatable through the asenumerable () method extended by datatableextensions.

(3) Write a query using the LINQ syntax. You can use the query syntax and method syntax to compile a query in the LINQ to dataset. You can perform any query operations that are allowed by ienumerable <t>.

(4) use the query results. After the query results are generated, you can use the query results (an ienumerable <t> object). For example, you can use foreach to traverse all elements and use max () for numerical calculation, use it as a data source for secondary queries.

The following sections describe the specific usage of LINQ to dataset through instances. However, for easier understanding, dataset in these examples is written directly in the memory and not obtained from the database.

Note: Because dataset itself is a collection of able, it can contain one or more datatable and their relationships, and LINQ to dataset actually queries the data of datatable, dataset is not queried.

5.2.2 query a single data table
A dataset usually contains one or more data tables and a set of relationships between them. In fact, it can be seen as a microcosm of the database. LINQ to dataset is also used to query one or more data tables. These data tables can be from a single data set or multiple data sets.

Section 5.2.1 describes how to query the elements in a able. When querying data in a datatable, you must use the asenumerable () method of the datatable class, this method converts a able to an enumerative data set of ienumerable <datarow> type. Its definition is as follows:

Public static enumerablerowcollection <datarow> asenumerable (

This datatable source)

Therefore, if the element type obtained from datatable is datarow, You need to further access the specific field data recorded in the data table, you need to use an extended generic method of datarow -- field <t> () to obtain the data of a field of datarow, which includes six overloaded versions, the following three are most commonly used.

Public static T field <t> (this datarow row, datacolumn column)

Public static T field <t> (this datarow row, int columnindex)

Public static T field <t> (this datarow row, string columnname)

The column parameter indicates the data column and the field to return data. The columnindex parameter indicates the index of the index column starting from 0. Columnname indicates the name of the field to return data. To make the code more generic, we recommend that you use a field name to specify the fields to be returned.

In the sample code 5-1, the buildonedtdataset () method creates a data set named "peopleds" in the memory. It contains only one data table named "peopledt" and the data table contains three fields: name, gender, and age ). In the useselect () method, first create a dataset through buildonedtdataset (), and then obtain the data table named "peopledt" through the dataset. Tables attribute. You can use the able. asenumerable () method to convert a datatable to an ienumerable <t> type data set in query1 and query2 for query. Query1 queries all elements, while query2 queries only the name field.

Sample Code 5-1

// Randomly create a dataset containing data

Static dataset buildonedtdataset ()

{

// Optional name, gender, and age, used to create student data to the data table

String [] nameset = {"Wang Xia", "Zhang San", "Li Si", "Li Hua", "Wang Wu", "Lu 6", "xia Qi ", "Wu ba "};

String [] xbset = {"female", "male", "male", "female", "male "};

Int [] ageset = {18, 20, 21, 22, 19, 20, 25, 24 };

Dataset DS = new dataset ("peopleds"); // create a DataSet object named peopleds

Datatable dt = new datatable ("peopledt"); // create a peopable object named peopledt

DS. Tables. Add (DT); // Add the data table DT to the data set Ds.

// Create a datatable column (field) information, including three fields:

// Name: name, string type

// Gender: xingbie, string type

// Age: age, int type

DT. Columns. addrange (

New datacolumn []

{

New datacolumn ("name", type. GetType ("system. String ")),

New datacolumn ("xingbie", type. GetType ("system. String ")),

New datacolumn ("Age", type. GetType ("system. int32 ")),

});

// Create multiple student information using the optional nameset, age set, and Gender xbset defined earlier

For (INT I = 0; I <nameset. length; I ++)

{

// Automatically create a row in the data table based on the current number and generate a row of data

// Add this row to the data table DT through datatable. Rows. Add ()

Datarow ROW = DT. newrow ();

Row ["name"] = nameset;

Row ["Age"] = ageset;

Row ["xingbie"] = xbset;

DT. Rows. Add (ROW); // Add to data table dt

}

Return Ds; // return Dataset

}

Static void useselect ()

{

Dataset DS = buildonedtdataset (); // get the DS Dataset

Datatable dt = Ds. Tables ["peopledt"]; // obtain the "peopledt" data table dt from the dataset Ds.

// Querying query1 indicates querying all records in the datatable. This shows how to use asenumerable ().

VaR query1 =

From PL in DT. asenumerable ()

Select pl;

// I = Ds. Tables [0]. Rows [0] ["name"]
// Domain

System. Console. writeline ("query1 :");

Foreach (VAR item in query1) // print the query result of query1

{

// Demonstrate the use of the field <t> Method

System. Console. writeline ("Name: {0}, Gender: {1}, age: {2 }",

Item. Field <string> ("name"), item. Field <string> ("xingbie"), item. Field <int> ("Age "));

}

// Querying query2 indicates querying the names of all users in the datatable, demonstrating the use of asenumerable () and field <t>

VaR query2 =

From PL in DT. asenumerable ()

Select pl. Field <string> ("name ");

System. Console. writeline ("query2:"); // print the query result of query1

Foreach (VAR item in query2)

{

System. Console. Write ("{0}", item );

}

System. Console. writeline ();

}

The output of sample code 5-1 is as follows. The query result of query1 is all the complete records in the table, including name, gender, and age. Query results of query2 only include the set of "name" fields in the table.

Query1:

Name: Wang Xia, Gender: female, age: 18

Name: Zhang San, Gender: male, age: 20

Name: Li Si, Gender: male, age: 21

Name: Li Hua, Gender: female, age: 22

Name: Wang Wu, Gender: male, age: 19

Name: Lu 6, Gender: male, age: 20

Name: Xia Qi, Gender: male, age: 25

Name: Wu Ba, Gender: male, age: 24

Query2:

Wang Xia Zhang San Li Si Li Hua Wang Wu Lu Liu Xia Qi Wu Ba

In addition to the SELECT statement, you can perform operations such as where filtering, orderby sorting, and groupby grouping on datatable records. As shown in the sample code 5-2, the orderby and where clauses are used in both query3 and query4 queries, and the filtering and sorting operations are performed at the same time. Query3 queries all records older than 22 years old, sorted by age from low to high. Query4 query all ages between 20 and 20 ~ Records Between 25, sorted by age from high to low.

Sample Code 5-2

Static void useorderbywhere ()

{

Dataset DS = buildonedtdataset (); // get the DS Dataset

Datatable dt = Ds. Tables ["peopledt"]; // obtain the "peopledt" data table dt from the dataset Ds.

// Query query3 query all the persons whose ages are greater than 22 and sort by age from low to high

VaR query3 =

From PL in DT. asenumerable ()

Orderby pl. Field <int> ("Age ")

Where pl. Field <int> ("Age")> 22

Select pl;

System. Console. writeline ("query3 :");

Foreach (VAR item in query1) // print the query3 Query Result

{

System. Console. writeline ("Name: {0}, Gender: {1}, age: {2 }",

Item. Field <string> ("name"), item. Field <string> ("xingbie"), item. Field <int> ("Age "));

}

// Query query4 query all the persons whose ages are greater than 20 and less than 25 in the data table, sorted by age from high to low

VaR query4 =

From PL in DT. asenumerable ()

Orderby pl. Field <int> ("Age") descending

Where pl. Field <int> ("Age")> 20

Where pl. Field <int> ("Age") <25

Select pl;

System. Console. writeline ("query4 :");

Foreach (VAR item in query2) // print the query4 Query Result

{

System. Console. writeline ("Name: {0}, Gender: {1}, age: {2 }",

Item. Field <string> ("name"), item. Field <string> ("xingbie"), item. Field <int> ("Age "));

}

}

The output of the sample code 5-2 is as follows. The query3 output is a record older than 22 years old, and the query4 output is a record older than 20-22 years old ~ Between 25 records.

Query3:

Name: Wu Ba, Gender: male, age: 24

Name: Xia Qi, Gender: male, age: 25

Query4:

Name: Wu Ba, Gender: male, age: 24

Name: Li Hua, Gender: female, age: 22

Name: Li Si, Gender: male, age: 21

Tip: to query the data of a able using a LINQ to dataset, you can simply divide the data into two parts. First, you can convert the data set into an ienumerable <t> data set, and then perform operations on ienumerable <t>, this step fully applies all the LINQ query operations described in Chapter 7th.

5.2.3 query multiple data tables
Generally, a data set (Dataset) contains multiple data tables, and data tables are associated with each other to represent a relational database. Using LINQ to dataset, you can easily query data in multiple data tables. This usually requires multiple from clauses for composite queries, and the where clause is used to judge the relationship between multiple tables.

In this example, use the data set created in code 5-3 and the builddataset () method to create a data table named students, which contains two data tables: Students and scores. The former records student information, including: name, gender (xingbie), age, score (scoreid ). The latter records students' scores, including: score number (scoreid), math, Chinese, and English ). The score number of a field is an associated field of two tables. This field can be used to query the score information of a student.

Sample Code 5-3

Static dataset builddataset ()

{

Dataset DS = new dataset ("Students"); // create a students Dataset

// Create the students data table dtstu and add it to the dataset DS

// Students data table contains student information

Datatable dtstu = new datatable ("Students ");

DS. Tables. Add (dtstu );

// Add the column information of the student information record, including four columns of data:

// Name: name, string type

// Gender: xingbie, string type

// Age: age, int type

// Score No.: scoreid, int type

Dtstu. Columns. addrange (New datacolumn [] {

New datacolumn ("name", type. GetType ("system. String ")),

New datacolumn ("xingbie", type. GetType ("system. String ")),

New datacolumn ("Age", type. GetType ("system. int32 ")),

New datacolumn ("scoreid", type. GetType ("system. int32 ")),

});

// Add five student information to the dtstu table, including name, gender, age, and score number.

Dtstu. Rows. Add ("James", "male", 20, 1 );

Dtstu. Rows. Add ("Li Si", "male", 19, 2 );

Dtstu. Rows. Add ("Wang Xia", "female", 21, 3 );

Dtstu. Rows. Add ("Zhao Min", "female", 22, 4 );

Dtstu. Rows. Add ("Wu an", "male", 18, 5 );

// Create a scores data table and add it to the dataset

// The scores data table contains student Score records

Datatable dtscore = new datatable ("scores ");

DS. Tables. Add (dtscore );

// Add the column (field) information of the score record table, which contains four fields:

// Score number: scoreid, int type, which corresponds to the scoreid field in the students table

// Mathematical score: Math, int type

// Chinese score: Chinese, int type

// English score: English, int type

Dtscore. Columns. addrange (New datacolumn [] {

New datacolumn ("scoreid", type. GetType ("system. int32 ")),

New datacolumn ("math", type. GetType ("system. int32 ")),

New datacolumn ("Chinese", type. GetType ("system. int32 ")),

New datacolumn ("English", type. GetType ("system. int32 ")),

});

// Add Student Score records, including score number, mathematics score, Chinese score, and English score

Dtscore. Rows. Add (1, 80, 75, 78 );

Dtscore. Rows. Add (3, 88, 80, 60 );

Dtscore. Rows. Add (4, 75, 90, 80 );

Dtscore. Rows. Add (5, 59, 80, 75 );

Return Ds; // return the dataset

}

To query data from multiple data tables, you can use multiple from clauses to perform a joint query. Each from clause corresponds to a data table, And the WHERE clause is used to represent the relationship between multiple data tables, generally, a single WHERE clause represents the relationship between two tables. Before querying multi-table data, you must clarify the following issues:

(1) in which data tables do you Want to query? How to write the from clause?

(2) which data table fields are included in the query results? How to Write a select clause?

(3) how to associate the relations between data tables? How to Write a where clause?

(4) Do I need other operations, such as sorting (orderby clause) and grouping (group clause?

(5) Is this query implemented using a simple single query or multiple query combinations?

As shown in code 5-4, in the querystuscores () method, the builddataset () method is used to obtain the dataset and the data table to be queried. dtstu indicates the student information data table, and dtscore indicates the Student Score data table. Query query1 is used to query the scores of all students in the dataset. If the students have no scores, the results are not returned.

In query1, the 1st from clauses query student information records from the table dtstu and save them to the Temporary Variable Stu. The 2nd from clauses query the score records from the table dtscore and save them to the temporary score traversal. The where clause is used to associate two tables, that is, the score number (scoreid) is equal. The Select clause indicates that the name field of the table dtstu and the math, Chinese, and English fields of dtscore are used as the query results.

Sample Code 5-4

Static void querystuscores ()

{

Dataset DS = builddataset (); // get the DS Dataset

Datatable dtstu = Ds. Tables ["Students"]; // obtain the students table dtstu from the DS Dataset

Datatable dtscore = Ds. Tables ["scores"]; // obtain the scores table dtscore from the DS dataset.

VaR query1 = // query query1 query the scores of all students

From Stu in dtstu. asenumerable () // query from students table and scores table

Join score in dtscore. asenumerable ()

On Stu. Field <int> ("scoreid") equals score. Field <int> ("scoreid ")

// Where Stu. Field <int> ("scoreid") = score. Field <int> ("scoreid") // score number (scoreid) equal

Select New // The anonymous type is the type of the query result element, which includes four members.

{

Name = Stu. Field <string> ("name "),

Maths = score. Field <int> ("math "),

Chinese = score. Field <int> ("Chinese "),

English = score. Field <int> ("English ")

};

System. Console. writeline ("query1-all student scores :");

Foreach (VAR item in query1) // print the query result of query1

{

System. Console. writeline ("Name: {0}, mathematics: {1}, language: {2}, English: {3 }",

Item. Name, item. Maths, item. Chinese, item. English );

}

}

The output of sample code 5-4 is as follows. It can be seen that the student "Li Si" has no score, so it is not in the query results of query1.