Expression expression tree dynamic query

In the query of data list, we usually use two ways to query:
  1. linq query
  2. Database sql statement query

This way, of course, can achieve the query, I have done so before, because the query conditions are very few. With linq, the attributes of all query conditions can be transferred to the background, and then queried by where according to whether the attributes have value or not. With stored procedures, the attributes of all query conditions also need to be transferred to the background.

Then, according to whether the attribute has value, the sql statements are stitched together. This will not have any effect when the query conditions are very few, but one day when I do the query list, I encountered a query condition as high as nearly 10 cases, so it can be achieved by using the above method, but may use more than one.

Conditions to determine, and then use multiple where queries, if the database using sql statements splicing, may produce a very long sql splicing query code, in debugging time also need to print to see the complete query conditions, so I wonder if we can write a way to dynamically generate query conditions.

Whether there are ten or dozens of query conditions in the list, I just need to pass the query attributes as an object to the background, and then throw the object of the query attributes to this method, so that it can generate query expressions dynamically, and then use where in linq to query? So I set out to explore the Expression table.

The way of Da Style Tree! Next, I write my own code, and I hope that we can communicate and learn together!

 

After inquiry and research, the following code is finally written to achieve the goal:

1. Define a query's feature class, _displayName is used to specify the properties of the query condition class to compare with the properties of the data source list element object. (Note that the value of _displayName must be the same as that of each attribute in the data source), _operation is used to specify the operation of comparison.

Type, class code is as follows:

 1 public class SearchAttribute : Attribute
 2 {
 3         /// <summary>
 4         /// Property name for comparison queries
 5         /// </summary>
 6         private string _displayName;
 7         /// <summary>
 8         /// Type of operation
 9         /// </summary>
10         private OperationType _operation;
11 
12         public string DisplayName
13         {
14             get { return _displayName; }
15         }
16         public OperationType Operation
17         {
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         /// Call this constructor parameter value when it is not a query condition=OperationType.None
29         /// </summary>
30         /// <param name="operation"></param>
31         public SearchAttribute(OperationType operation)
32         {
33             _operation = operation;
34         }
35 }

2. Define an enumeration type for a comparative operation type
 1     /// <summary>
 2     /// Query operation type
 3     /// </summary>
 4     public enum OperationType
 5     {
 6         /// <summary>
 7         /// No queries
 8         /// </summary>
 9         None,
10         /// <summary>
11         /// Comparing the value of the query attribute with the value of metadata data is sql in=
12         /// </summary>
13         Equal,
14         /// <summary>
15         /// Comparing whether the value of metadata data contains the value of the query attribute is sql in like
16         /// </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         LessThanOrEqual
34     }

3. Here comes the core code, 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);  //Types of Element Objects in Data Source List
11             var dataSource = new
12             {
13                 Type = dataSouceType,  //Types of Element Objects in Data Source List
14                 Properties = dataSouceType.GetProperties(),  //Attribute Set of Element Objects in Data Source List
15             };            
16 
17             //List<string> sourcePropertyName = sourceProperties.Select(p => p.Name).ToList();
18 
19             PropertyInfo[] searchProperties = searchOptionEntity.GetType().GetProperties();  //Set of attributes for query selector objects
20 
21             var pe = Expression.Parameter(dataSource.Type, "p"); //Create a ParameterExpression Node, which can be used to identify parameters or variables in the expression tree
22            var expression = Expression.Equal(Expression.Constant(true), Expression.Constant(true));
23 
24             //Traversing the property set of query selector objects
25             foreach (var property in searchProperties)
26             {
27                 var propertySearchAttribute = property.GetCustomAttributes(true)[0] as SearchAttribute;  //Getting custom feature objects for query selector attributes
28                 var propertySearchVlaue = property.GetValue(searchOptionEntity, null);  //Get the value of the query selector property
29                 var propertySearchAttributeName = propertySearchAttribute.DisplayName;  //Gets the field name of the comparison query for the custom property object of the query selector property
30 
31                 //The field name of the custom comparison query for this property in the query selector in Attribute Set of Data Source List Objects && This property in the query selector is a condition of the query  &&  Query selector The value of this property!=null perhaps""
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;  //Gets the attribute type of a single attribute of a data source list element object
36                     ConstantExpression constantReference = null;
37                     Expression Expr = null;
38 
39                     bool isGenericType = sourcePropertyType.IsGenericType && sourcePropertyType.GetGenericTypeDefinition() == typeof(Nullable<>);  //search sourcePropertyType Can it be empty?
40                     if (isGenericType)
41                         constantReference = Expression.Constant(Convert.ChangeType(propertySearchVlaue, Nullable.GetUnderlyingType(sourcePropertyType)), sourcePropertyType);  //If it can be null, the propertySearchVlaue Type set to empty type
42                     else
43                         constantReference = Expression.Constant(Convert.ChangeType(propertySearchVlaue, sourcePropertyType));
44 
45                     //Different operations are performed according to 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);  //Final Query Conditions
71                 }
72             }
73             return Expression.Lambda<Func<TSource, bool>>(expression, pe);
74         }
75     }
Note: Query class and its member methods must be defined as static. For specific reasons, please search for the definition of C# extension method.
Finally, you just need to call Search (Query Conditional Object) on the IQueryable object and pass in parameters.

This is my own defined 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 attribute name of the class does not necessarily have to be the same as the displayName parameter of the Search method above the attribute, but the displayName parameter must have the same value as the attribute name in the query list object.

This is my list of queries.
1 var result = (from a in db.Company_BasicInfo.Where(p => p.CompanyID > 0)
2                               select new
3                               {
4                                   a.CompanyID,
5                                   a.CompanyName,
6                                   a.SYS_CreateTime
7                               }).Search(searchOption).OrderByDescending(p => p.SYS_CreateTime).Take(10000).ToList();

OK, now I don't need to care what my query condition is, just pass in the corresponding value to the object of the query attribute.

Tags: ASP.NET Attribute SQL Database less

Posted on Wed, 03 Jul 2019 18:18:34 -0700 by joe1986