Expression expression Tree dynamic query, Expression expression Expression

Expression expression Tree dynamic query, Expression expression Expression

You can query a data list in either of the following ways:

In this way, the query can be implemented. I did this before, because there are very few query conditions. Using linq, you can upload the attributes of all query conditions to the backend, and then use where for query based on whether the attributes have values. Using Stored Procedures, you also need to upload the attributes of all query conditions to the background,

Then, the SQL statement is spliced based on whether the attribute has a value. In this way, the query conditions are not affected when there are few query conditions, but one day when I perform a query list, I encountered a query condition of nearly 10, in this way, the above method can also be used, but multiple

The condition is determined and then multiple where queries are used. If SQL statements are concatenated in the database, a long SQL query code may be generated, print must be used for debugging to see the complete query conditions. So I was wondering if I could write a way to dynamically generate query conditions.

No matter whether there are 10 or dozens of query conditions in the list, I only need to upload the query attribute as an object to the background, and then I will throw the object of this query attribute to this method, let it dynamically generate the query expression, and then use the where clause in linq to query it? So I stepped onto the exploration Expression table.

The path to the tree! Below I will write my own code, hoping to communicate and learn with everyone!

 

After querying materials and research, I finally wrote the following code to achieve the goal:

1. define a query feature class. _ displayName indicates the attribute names of the classes used to specify the query conditions to compare with the attributes of the element object in the data source list (note: _ displayName must be the same as each attribute name in the data source), and _ operation is used to specify the comparison operation.

Type, the code of the class is as follows:

1 public class SearchAttribute: Attribute 2 {3 // <summary> 4 // Attribute name, used for comparison query 5 /// </summary> 6 private string _ displayName; 7 /// <summary> 8 /// operation type 9 /// </summary> 10 private OperationType _ operation; 11 12 public string DisplayName13 {14 get {return _ displayName;} 15} 16 public OperationType Operation17 {18 get {return _ operation;} 19} 20 21 public SearchAttribute (string displayName, operationType operation) 22 {23 _ displayName = displayName; 24 _ operation = operation; 25} 26 27 /// <summary> 28 // the value of this constructor parameter = OperationType is called when the condition is not queried. none29 // </summary> 30 // <param name = "operation"> </param> 31 public SearchAttribute (OperationType operation) 32 {33 _ operation = operation; 34} 35}

2. Define an enumeration type for a comparison Operation Type

1 /// <summary> 2 /// query Operation Type 3 /// </summary> 4 public enum OperationType 5 {6 /// <summary> 7 // No query 8 /// </summary> 9 None, 10 /// <summary> 11 /// compare whether the value of this query attribute is Equal to the value of metadata data, that is, = 12 in SQL /// </summary> 13 Equal, 14 /// <summary> 15 // compare whether the value of the metadata data contains the value of this query attribute, that is, like16 in SQL /// </summary> 17 Like, 18 /// <summary> 19 /// greater than 20 /// </summary> 21 GreaterThan, 22 /// <summary> 23 // less than 24 /// </summary> 25 LessThan, 26 /// <summary> 27 ///> = 28 /// </summary> 29 GreaterThanOrEqual, 30 /// <summary> 31 /// <= 32 /// </summary> 33 LessThanOrEqual34}

3. The core code is coming. Haha

1 public static class Query 2 {3 public static IQueryable <TSource> Search <TSource, T> (this IQueryable <TSource> queryList, T searchOptions) 4 {5 return queryList. where (Search <TSource, T> (searchOptions); 6} 7 8 private static Expression <Func <TSource, bool> Search <TSource, T> (T searchOptionEntity) 9 {10 var dataSouceType = typeof (TSource); // data source list Element Object Type 11 var dataSource = new12 {13 Type = dataSouce Type, // Type of the element object in the data source list 14 Properties = dataSouceType. getProperties (), // data source List Element Object attribute set 15}; 16 17 // List <string> sourcePropertyName = sourceProperties. select (p => p. name ). toList (); 18 19 PropertyInfo [] searchProperties = searchOptionEntity. getType (). getProperties (); // query the attribute set of the selector object 20 21 var pe = Expression. parameter (dataSource. type, "p"); // create a ParameterExpression node that identifies the parameter or variable 22 var expressi In the Expression Tree On = Expression. equal (Expression. constant (true), Expression. constant (true); 23 24 // traverses the attribute set of the query selector object 25 foreach (var property in searchProperties) 26 {27 var propertySearchAttribute = property. getCustomAttributes (true) [0] as SearchAttribute; // gets the custom property object 28 var propertySearchVlaue = property of the query selector attribute. getValue (searchOptionEntity, null); // obtain the query selector attribute value 29 var propertySearchAttributeName = propertySearchAttri Invalid. displayName; // obtain the comparative query field name of the custom property object of the query selector 30 31 // query the custom comparison query field name of the property in the selector in the data source list object in the property set & query selector, this property is the query condition & query selector. The value of this property! = Null or "32 if (Array. Exists (dataSource. Properties, p => p. Name = propertySearchAttributeName) & propertySearchAttribute. Operation! = OperationType. None & propertySearchVlaue! = Null & propertySearchVlaue! = (Object) string. empty) 33 {34 var propertyReference = Expression. property (pe, propertySearchAttributeName); 35 var sourcePropertyType = dataSource. properties. firstOrDefault (p => p. name = propertySearchAttributeName ). propertyType; // obtain the attribute type of a single attribute of the data source list Element Object 36 ConstantExpression constantReference = null; 37 Expression Expr = null; 38 39 bool isGenericType = sourcePropertyType. isGenericType & sourcePropertyType. getGenericTypeDefinition () = typeof (Nullable <>); // search whether sourcePropertyType can be empty 40 if (isGenericType) 41 constantReference = Expression. constant (Convert. changeType (propertySearchVlaue, Nullable. getUnderlyingType (sourcePropertyType), sourcePropertyType); // if it can be empty, set the propertySearchVlaue type to 42 else43 constantReference = Expression. constant (Convert. changeType (propertySearchVlaue, sourcePropertyType); 44 // perform different operations based on the query conditions of the attribute in the query selector. 46 switch (propertySearchAttribute. operation) 47 {48 case OperationType. equal: 49 Expr = Expression. equal (propertyReference, constantReference); 50 break; 51 case OperationType. greaterThan: 52 Expr = Expression. greaterThan (propertyReference, constantReference); 53 break; 54 case OperationType. lessThan: 55 Expr = Expression. lessThan (propertyReference, constantReference); 56 break; 57 case OperationType. greaterThanOrEqual: 58 Expr = Expression. greaterThanOrEqual (propertyReference, constantReference); 59 break; 60 case OperationType. lessThanOrEqual: 61 Expr = Expression. lessThanOrEqual (propertyReference, constantReference); 62 break; 63 case OperationType. like: 64 Expr = Expression. call (propertyReference, typeof (String ). getMethod ("Contains", new Type [] {typeof (string)}), constantReference); 65 break; 66 default: break; 67 68} 69 70 expression = Expression. andAlso (expression, Expr); // The final query condition 71} 72} 73 return Expression. lambda <Func <TSource, bool> (expression, pe); 74} 75}

Note: The Query Class and its member methods must be defined as static. for specific reasons, search for the C # extension method definition.
Finally, you only need to call the Search (query condition object) function on the IQueryable object and input parameters.

This is my own query condition class.

 1 public class ProjectInfoDTO 2     { 3         [Search("CompanyName",OperationType.Like)] 4         public string CompanyName { get; set; } 5  6          7         [Search("SYS_CreateTime",OperationType.GreaterThanOrEqual)] 8         public DateTime? CreateTimeStart { get; set; } 9 10         [Search("SYS_CreateTime", OperationType.LessThanOrEqual)]11         public DateTime? CreateTimeEnd { get; set; }12     }

The property name of the class does not have to be the same as the value of the displayName parameter of the Search method above the property, but the value of the displayName parameter must be the same as the name of the property in the query list object.

This is the list I queried.

1 var result = (from a in db.Company_BasicInfo.Where(p => p.CompanyID > 0)2                               select new3                               {4                                   a.CompanyID,5                                   a.CompanyName,6                                   a.SYS_CreateTime7                               }).Search(searchOption).OrderByDescending(p => p.SYS_CreateTime).Take(10000).ToList();

OK. Now I don't need to worry about what the query conditions are. I just need to input the corresponding value to the query attribute object.