Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to understand LINQ dynamic query". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to understand LINQ dynamic query.

LINQ dynamic queries have a scenario where an application may provide a user interface that users can use to specify one or more predicates to filter data. In this case, you don't know the details of the query at compile time, and LINQ dynamic queries can be very useful.

In LINQ, Lambda expressions are the basis of many standard query operators, and the compiler creates lambda expressions to capture calculations defined in underlying query methods such as Where, Select, Order By, Take While, and others. The expression directory tree is used for structured queries against data sources that implement IQueryable.

For example, the LINQ to SQL provider implements the IQueryable interface, which is used to query relational data stores. The C # and Visual Basic compilers compile queries for such data sources into code that generates an expression directory tree at run time. The query provider can then traverse the expression directory tree data structure and convert it into a query language suitable for the data source.

The expression directory tree is used in LINQ to represent Lambda expressions assigned to variables of type Expression. It can also be used to create dynamic LINQ queries.

The System.Linq.Expressions namespace provides API for manually generating the expression directory tree. The Expression class contains static factory methods that create a specific type of expression directory tree node, such as ParameterExpression (for a named parameter expression) or MethodCallExpression (for a method call).

The root of the expression directory tree generated by the compiler is always in the node of type Expression, where TDelegate is any TDelegate delegate that contains up to five input parameters; that is, its root node represents an lambda expression.

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

Select of 1.LINQ dynamic query

The following example shows how to use the expression tree to construct a dynamic query based on the IQueryable data source, query out the ContactName of each customer, and use the GetCommand method to get it to generate SQL statements.

/ / construct a query IQueryable custs = db.Customers; / / build an expression tree based on the IQueryable data source to create a parameter ParameterExpression param = Expression.Parameter (typeof (Customer), "c"); / / build an expression tree: c.ContactName Expression selector = Expression.Property (param, typeof (Customer) .GetProperty ("ContactName")); Expression pred = Expression.Lambda (selector, param) / / build expression tree: Select (c = > c.ContactName) Expression expr = Expression.Call (typeof (Queryable), "Select", new Type [] {typeof (Customer), typeof (string)}, Expression.Constant (custs), pred); / / use expression tree to generate dynamic query IQueryable query = db.Customers.AsQueryable () .Provider.CreateQuery (expr) / / use the GetCommand method to get 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]

Where of 2.LINQ dynamic query

The following example is to "build" the Where usage to dynamically query the city's customers in London.

IQueryable custs = db.Customers; / / create a parameter c ParameterExpression param = Expression.Parameter (typeof (Customer), "c"); / / c.Citycats = "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.Citycats = "London") Expression expr = Expression.Call (typeof (Queryable), "Where", new Type [] {typeof (Customer)}, Expression.Constant (custs), pred); / / generate dynamic query IQueryable query = db.Customers.AsQueryable () .Provider.CreateQuery (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

This example realizes both sorting function and filtering function.

IQueryable custs = db.Customers; / / create a parameter c ParameterExpression param = Expression.Parameter (typeof (Customer), "c"); / / c.Citycats = "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.Citycats = "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); / / generate dynamic query IQueryable query = db.Customers.AsQueryable () .Provider.CreateQuery (orderByCallExpression)

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 ORDER BY [t0]. [ContactName]-- @ p0: Input NVarChar (Size = 6) Prec = 0; Scale = 0) [London] 4.Union

The following example uses the expression tree LINQ to dynamically query cities where customers and employees are present.

/ / e.City IQueryable custs = db.Customers; ParameterExpression param1 = Expression.Parameter (typeof (Customer), "e"); Expression left1 = Expression.Property (param1, typeof (Customer) .GetProperty ("City")); Expression pred1 = Expression.Lambda (left1, param1); / / c.City IQueryable 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 Q1 = db.Customers.AsQueryable () .Provider.CreateQuery (expr1); IQueryable Q2 = db.Employees.AsQueryable () .Provider.CreateQuery (expr2); / / 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 logo

Not mentioned in the previous point, the ID logo is discussed separately here as an advanced feature.

This example shows that when we store a new record, ContactID is used as the primary key identification, and the system automatically assigns the identification seed to 1, so it automatically adds one each time.

/ / ContactID is the primary key ID, insert a piece of data, the system automatically assigns ID Contact con = new Contact () {CompanyName = "New Era", Phone = "(123)-456-7890"}; db.Contacts.InsertOnSubmit (con); db.SubmitChanges (); at this point, I believe you have a deeper understanding of "LINQ dynamic query understanding", might as well come to the actual operation! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.