Those things of LINQ (5)-dynamic query

This article discusses how to execute dynamic queries in linq2sql.

A dynamic query is a query that can be determined only when the query conditions or query values are running. This means that we cannot define the query variable in hard-code. We can only splice the variables based on the conditions passed during the query. Next, let's look at the queries with several combinations of different conditions.

1) user input query condition: City is "London" and contactname contains "Thomas"

public IQueryable<Customer> GetCustomers(string city, string contactName){var context = GenerateContext();IQueryable<Customer> result = context.Customers;if (!string.IsNullOrEmpty(city)){result = result.Where(c => c.City == city);}if (!string.IsNullOrEmpty(contactName)){result = result.Where(c => c.ContactName.Contains(contactName));}return result;}

2) The user enters the query condition: the city is "London" or "Paris"

Since the connection between where and where represents the relationship between and, we cannot use the 1) method to express this query. One available query operator is union:

var context = GenerateContext();IQueryable<Customer> result = null;string[] cities = { "London", "Paris" };foreach (string item in cities){    string tmp = item;    result = result == null ?    context.Customers.Where(c => c.City == tmp) :    result.Union(context.Customers.Where(c => c.City == tmp));}context.Log = Console.Out;Console.WriteLine(result.Count());

Although the results meet our requirements, you can see the expression tree constructed in this way through the output SQL statement, SQL is not simplified as we want (we expect t0.city = 'London 'or t0.city = 'Paris '). The output SQL statement is as follows:

SELECT COUNT(*) AS [value]FROM (    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    UNION    SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax]    FROM [dbo].[Customers] AS [t1]    WHERE [t1].[City] = @p1    ) AS [t2]

The other method is to construct an expession for where based on all query conditions and query values. First, let's look at the where signature:

IQueryable<T> Where<T>(this IQueryable<T> source, Expression<Func<T, bool>> predicate)

If you are familiar with the expression tree constructor, you can write the expression by yourself. The author of "C #3.0 in nutshell" wrote a small prediatebuilder, which can help us quickly construct this expression using Lambda expression:

var context = GenerateContext();Expression<Func<Customer, bool>> filter = PredicateBuilder.False<Customer>();string[] cities = { "London", "Paris" };foreach (string item in cities){    string tmp = item;    filter = filter.Or(c => c.City == tmp);}context.Log = Console.Out;context.Customers.Where(filter).Count().Dump();

The output SQL is also the expected result.

SELECT COUNT(*) AS [value]FROM [dbo].[Customers] AS [t0]WHERE ([t0].[City] = @p0) OR ([t0].[City] = @p1)

 

Finally, if you still have forgotten the previous method of splicing SQL statements, you can use dynamic LINQ to refer to socttgu's blog.

References:

Predicatebuilder http://www.albahari.com/nutshell/predicatebuilder.aspx

Dynamic LINQ http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

Link

1. What about LINQ (1)-define the ing from a relational database to an entity class

2. What about LINQ (2)-Crud operations on simple objects and cascade operations on Association

3. What about LINQ (3)-transaction and concurrent conflict Processing

4. What about LINQ (4)-query expression and query operator

5. What about LINQ (5)-dynamic query

6. What about LINQ (6)-object lifecycle management of datacontext

7. What about LINQ (7)-extended LINQ by customizing ienumerable <t>

8. What about LINQ (8)-extended by customizing iqueryable <t> and iqueryableprovider