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 public static ienumerable<customers> GetCustomersFunc1 (string[] keywords)
2{
3 Dataclassesdatacontext dc = new Dataclassesdatacontext ();
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);
Ten foreach (string keyword in keywords)
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")),
typeof (String). GetMethod ("Contains", new type[]{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 will Expression condition = Expression.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>> (conditi On, new parameterexpression[]{c});
27
28//Use the criteria you just built to query
var result = DC. Customers.where (end);
return result;
31}
+
2. Using System.Linq.Dynamic
1 public static ienumerable<customers> GETCUSTOMERSFUNC2 (string[] keywords)
2{
3//need to reference System.Linq.Dynamic. Dynamic.cs file can be found in linqsamples
4
5 Dataclassesdatacontext dc = new Dataclassesdatacontext ();
6 String queryString = "";
7 foreach (string keyword in keywords)
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";
Return DC. Customers.where (queryString);
16}
-
3. Stitching the SQL statement in the Cloak of LINQ
1 public static ienumerable<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 = new Dataclassesdatacontext ();
5 String sqlquery = "SELECT [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address],";
6 sqlquery + = "[City], [region], [Postalcode],[country], [Phone], [传真] from [dbo]. [Customers] WHERE ";
7 foreach (string keyword in keywords)
8{
9 SQLQuery + = "([CompanyName] like '% ' + keyword +"% ') or ";
10}
One sqlquery + = "(1=0)";
Return DC. Executequery<customers> (sqlquery);
13}
14
theSource: http://www.cnblogs.com/snowdream/archive/2008/07/18/1246308.html
LINQ to SQL runtime dynamically builds query conditions
