LINQ experience (17) -- dynamic query of LINQ to SQL statements

Document directory

• 1. Select
• 2. Where
• 3. OrderBy
• 4. Union

Advanced features

This article introduces the advanced features of LINQ, including the usage of dynamic queries that everyone cares about, and briefly introduces the knowledge of ID identification.

Dynamic query

An application may provide a user interface that allows you to specify one or more predicates to filter data. In this case, the details of the query are not known during compilation, and dynamic query is very useful.

In LINQ, Lambda expressions are the basis of many standard query operators. The Compiler creates lambda expressions to capture basic query methods (such as Where, Select, Order By, Take While, and other methods). The expression directory tree is used for structured queries on data sources. These data sources implement IQueryable <T>. For example, the LINQ to SQL provider implements the IQueryable <T> interface to query relational data storage. The C # and Visual Basic compilers compile queries for such data sources into code, which will generate an expression directory tree at runtime. Then, the query provider can traverse the expression directory tree data structure and convert it into a query language suitable for the data source.

The Expression directory tree is a Lambda Expression that is used to represent the variable assigned to Expression <TDelegate>. It can also be used to create dynamic LINQ queries.

The System. Linq. Expressions namespace provides APIs used to manually generate the expression directory tree. The Expression class contains the static factory method for creating a specific type of Expression directory tree node, for example, ParameterExpression (indicating a named parameter Expression) or MethodCallExpression (indicating a method call ). The root of the Expression directory tree generated by the compiler is always in the node of Type Expression <TDelegate>, where TDelegate is any TDelegate delegate containing up to five input parameters; that is, its root node represents a lambda expression.

The following examples describe how to use the expression directory tree to create a dynamic LINQ query.

1. Select

The following example shows how to use the expression tree to construct a dynamic query based on the IQueryable data source, query the ContactName of each customer, and use the GetCommand method to obtain the generated SQL statement.

// Construct a query IQueryable <Customer> custs = db based on the IQueryable data source. MERs MERS; // create an Expression Tree to create a parameter ParameterExpression param = Expression. parameter (typeof (Customer), "c"); // construct the Expression Tree: c. contactNameExpression selector = Expression. property (param, typeof (Customer ). getProperty ("ContactName"); Expression pred = Expression. lambda (selector, param); // construct the Expression Tree: Select (c => c. contactName) Expression expr = Expression. call (typeof (Queryable), "Select", new Type [] {typeof (Customer), typeof (string)}, Expression. constant (custs), pred); // use the expression tree to generate a dynamic query IQueryable <string> query = db. MERs. asQueryable (). provider. createQuery <string> (expr); // use the GetCommand method to obtain the SQL statement System. data. common. dbCommand cmd = db. getCommand (query); Console. writeLine (cmd. commandText );

The generated SQL statement is:

SELECT [t0].[ContactName] FROM [dbo].[Customers] AS [t0]

2. Where

The following example shows how to create a Where statement to dynamically query customers in a city in London.

IQueryable <Customer> custs = db. MERs MERS; // create a parameter cParameterExpression param = Expression. parameter (typeof (Customer), "c"); // c. city = "London" Expression left = Expression. property (param, typeof (Customer ). getProperty ("City"); Expression right = Expression. constant ("London"); Expression filter = Expression. equal (left, right); Expression pred = Expression. lambda (filter, param); // Where (c => c. city = "London") Expression expr = Expression. call (typeof (Queryable), "Where", new Type [] {typeof (Customer)}, Expression. constant (custs), pred); // generate dynamic query IQueryable <Customer> query = db. MERs. asQueryable (). provider. createQuery <Customer> (expr );

The generated SQL statement is:

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]FROM [dbo].[Customers] AS [t0] WHERE [t0].[City] = @p0-- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [London]

3. OrderBy

In this example, both sorting and filtering are implemented.

IQueryable <Customer> custs = db. MERs MERS; // create a parameter cParameterExpression param = Expression. parameter (typeof (Customer), "c"); // c. city = "London" Expression left = Expression. property (param, typeof (Customer ). getProperty ("City"); Expression right = Expression. constant ("London"); Expression filter = Expression. equal (left, right); Expression pred = Expression. lambda (filter, param); // Where (c => c. city = "London") MethodCallExpression whereCallExpression = Expression. call (typeof (Queryable), "Where", new Type [] {typeof (Customer)}, Expression. constant (custs), pred); // OrderBy (ContactName => ContactName) MethodCallExpression orderByCallExpression = Expression. call (typeof (Queryable), "OrderBy", new Type [] {typeof (Customer), typeof (string)}, whereCallExpression, Expression. lambda (Expression. property (param, "ContactName"), param); // generates an IQueryable <Customer> query = db. MERs. asQueryable (). provider. createQuery <Customer> (orderByCallExpression );

The following shows how to dynamically generate a dynamic query.

The generated SQL statement is:

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region],[t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]FROM [dbo].[Customers] AS [t0] WHERE [t0].[City] = @p0ORDER BY [t0].[ContactName]-- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [London]

4. Union

The following example uses the expression tree to dynamically query the cities in which customers and employees are located.

// E. cityIQueryable <Customer> custs = db. MERs MERS; ParameterExpression param1 = Expression. parameter (typeof (Customer), "e"); Expression left1 = Expression. property (param1, typeof (Customer ). getProperty ("City"); Expression pred1 = Expression. lambda (left1, param1); // c. cityIQueryable <Employee> employees = db. employees; ParameterExpression param2 = Expression. parameter (typeof (Employee), "c"); Expression left2 = Expression. property (param2, typeof (Employee ). getProperty ("City"); Expression pred2 = Expression. lambda (left2, param2); // Select (e => e. city) Expression expr1 = Expression. call (typeof (Queryable), "Select", new Type [] {typeof (Customer), typeof (string)}, Expression. constant (custs), pred1); // Select (c => c. city) Expression expr2 = Expression. call (typeof (Queryable), "Select", new Type [] {typeof (Employee), typeof (string)}, Expression. constant (employees), pred2); // generate dynamic query IQueryable <string> q1 = db. MERs. asQueryable (). provider. createQuery <string> (expr1); IQueryable <string> q2 = db. employees. asQueryable (). provider. createQuery <string> (expr2); // returns the Union var q3 = q1.Union (q2 );

The generated SQL statement is:

SELECT [t2].[City]FROM (    SELECT [t0].[City] FROM [dbo].[Customers] AS [t0]    UNION    SELECT [t1].[City] FROM [dbo].[Employees] AS [t1]    ) AS [t2]

ID

As mentioned above, ID IDs are described separately as advanced features.

This example shows that when we store a new record, ContactID is used as the primary key ID. The system automatically assigns the ID and the seed value is 1. Therefore, one is automatically added each time.

// ContactID is the primary key ID, insert a piece of data, the system automatically allocates IDContact con = new Contact () {CompanyName = "New Era", Phone = "(123) -456-7890 "}; db. contacts. insertOnSubmit (con); db. submitChanges ();

This series of links: navigation to the LINQ Series

Recommended resources of LINQ

Special topics: http://kb.cnblogs.com/zt/linq/ on all aspects of the entry, advanced, in-depth articles on LINQ.
LINQ group: a good place to learn questions or questions about http://space.cnblogs.com/group/linq.

This article is based on the signature 2.5 mainland China license agreement. You are welcome to reprint, interpret, or use it for commercial purposes. However, you must keep this article's signature Li yongjing (including the link). For more information, see here. If you have any questions or authorization negotiation, please leave a message for me.