LINQ to SQL runtime dynamically builds query conditions

In the data query, often encountered the need to dynamically build query conditions. Using LINQ to implement this requirement may be more cumbersome than stitching up SQL statements in the past. This article describes 3 ways to dynamically build query conditions at run time. The examples in this article all end up with the same functionality, searching from the Northwind database Customers table for the CompanyName column with any element in the keywords. Keywords is an array of strings, and the length of the array is not deterministic at compile time. Ideas and method descriptions are written in the code comments.

1. expression tree

1 PublicStaticIenumerable<customers> GetCustomersFunc1 (string[] keywords)
2 {
3 Dataclassesdatacontext DC =NewDataclassesdatacontext ();
4
5//Create a parameter expression with static type customers
6 parameterexpression C = Expression.parameter (typeof(Customers), "C");
7
8//Create an expression that is constant equal to false, which is used to draw the set with the following expression
9 Expression condition = expression.constant (false);
10foreach(stringKeywordinchKeywords
11 {
12//This expression is used to determine whether the value of the CompanyName property of a Customers class contains a keyword keyword
Expression con = Expression.call (
Expression.property (c,typeof(Customers). GetProperty ("CompanyName")),
15typeof(string). GetMethod ("Contains",NewType[] {typeof(string) }),
Expression.constant (keyword));
17
18//With the previous condition expression for logic or operation.
19//If the item you are looking for needs to contain all the keywords in keywords, you can use Expression.and (con, condition) 20//and expression condition = E Xpression. Constant (FALSE);
21//change to Expression condition = Expression.constant (true);
condition = Expression.or (con, condition);
23}
24
25//Create a delegate that takes a customers class as a parameter and returns a bool type
Expression<func<customers,BOOL>> end = Expression.lambda<func<customers,BOOL>> (condition,NewParameterexpression[] {c});
27
28//Use the criteria you just built to query
var result = DC. Customers.where (end);
30returnResult
31}
32

2. using System.Linq.Dynamic

1 PublicStaticIenumerable<customers> GetCustomersFunc2 (string[] keywords)
2 {
3//need to reference System.Linq.Dynamic. Dynamic.cs file can be found in linqsamples
4
5 Dataclassesdatacontext DC =NewDataclassesdatacontext ();
6stringqueryString = "";
7foreach(stringKeywordinchKeywords
8 {
9//prototype (C=>c.companyname.contains (keyword1)) | | (C=>c.companyname.contains (keyword2)) | |
Ten queryString + = "Companyname.contains (\" "+ keyword +" \ ") or";
11}
12
13//With false logic or operation, in order to avoid the last or syntax error in querystring
QueryString + = "1=0";
15returndc. Customers.where (queryString);
16}
17

3. stitching the SQL statement in the cloak of LINQ

1 PublicStaticIenumerable<customers> GetCustomersFunc3 (string[] keywords)
2 {
3//This method is actually pseudo-LINQ, the core is still stitching SQL statements, so there is not much to explain
4 Dataclassesdatacontext DC =NewDataclassesdatacontext ();
5stringsqlquery = "SELECT [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address],";
6 sqlquery + = "[City], [region], [Postalcode],[country], [Phone], [传真] from [dbo].  [Customers] WHERE ";
7foreach(stringKeywordinchKeywords
8 {
9 SQLQuery + = "([CompanyName] like '% ' + keyword +"% ') or ";
10}
One sqlquery + = "(1=0)";
12returndc. Executequery<customers> (sqlquery);
13}
14
15

LINQ to SQL runtime dynamically builds query conditions