LINQ to SQL runtime dynamically builds query conditions

Original address: Http://msdn.microsoft.com/zh-cn/dd567295.aspx

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

1PublicStatic Ienumerable<customers> GetCustomersFunc1 (string[] keywords)
2 {
3 Dataclassesdatacontext DC =New Dataclassesdatacontext ();
4
5Create a parameter expression with a static type of customers
6 parameterexpression C = Expression.parameter (typeof (Customers),"C");
7
8Creates 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 (string keywordIn keywords)
11 {
12The 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")),
15typeofString). GetMethod ("Contains",New type[] {typeofString)}),
Expression.constant (keyword));
17
18Logical OR operation with the previous condition expression.
19If 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 = Expression.const Ant (False);
21stChange to Expression condition = Expression.constant (true);
condition = Expression.or (con, condition);
23}
24
25Creates a delegate that takes a customers class as a parameter and returns a bool type
26   expression<func<customers,  Bool>> end = expression.lambda<func<customers, bool >> (Condition, NEW&NBSP;PARAMETEREXPRESSION[]&NBSP;&NBSP;{&NBSP;C&NBSP;});   
 27&NBSP;&NBSP; 
 28          //use the conditions just built to query   
 29   &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;VAR&NBSP;RESULT&NBSP;=&NBSP;DC. Customers.where (end);   
 30          RETURN&NBSP;RESULT;&NBSP; 
 31    &NBSP;&NBSP;}&NBSP 
 32         

2. Using System.Linq.Dynamic

1PublicStatic Ienumerable<customers> GetCustomersFunc2 (string[] keywords)
2 {
3You need to reference System.Linq.Dynamic. Dynamic.cs file can be found in linqsamples
4
5 Dataclassesdatacontext DC =New Dataclassesdatacontext ();
6String queryString ="";
7foreach (string keywordIn keywords)
8 {
9The prototype is (C=>c.companyname.contains (keyword1)) | | (C=>c.companyname.contains (keyword2)) | |
Ten queryString + ="Companyname.contains (\" "+ keyword + " \ ") or";
11}
12
+ //with false for logic or operation, in order to avoid the last or syntax error in querystring
QueryString + = "1=0";
return DC. Customers.where (queryString);
16}
            -

3. Stitching the SQL statement in the Cloak of LINQ

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

LINQ to SQL runtime dynamically builds query conditions