Use Expression to filter any data field (1). expression Field

Use Expression to filter any data field (1). expression Field

Data Tables are often used in projects. Currently, BS usually uses the front-end to use a js Grid control, load data from the background through ajax, and then bind the data to the Grid. Data is usually not displayed on one page, so you need to add pages. Then, you need to sort the data based on the key fields and filter the data. As a backend engineer, you must consider how to implement the paging, sorting, and filtering functions elegantly.

This article first describes filtering. Because the paging, sorting, and filtering actions must first process the filtered results and then sort them.

The following two issues must be considered before filtering:

1) Field Type

2) Comparison Method

The following simulated data is used as an example (this data is used for server performance monitoring, including the monitoring result and time of the processor and memory ).

ServerName	ProcessorMaxValue	ProcessorMinValue	ProcessorAvgValue	MemoryMaxValue	MemoryMinValue	MemoryAvgValue	DateTime
Server1	8	3	3.29	82.18	82.11	82.14	2016/10/1
Server1	10	3	3.29	82.23	82.12	82.17	2016/10/2
Server1	11	3	3.32	82.21	82.15	82.18	2016/10/3
Server1	10	3	3.29	82.21	82.10	82.16	2016/10/4
Server1	10	3	3.42	82.20	82.12	82.15	2016/10/5
Server2	10	3	3.40	82.20	82.12	82.16	2016/10/6
Server2	9	3	4.08	82.22	82.11	82.15	2016/10/7
Server2	10	3	3.69	82.20	82.12	82.16	2016/10/8
Server3	11	3	4.13	82.21	82.14	82.16	2016/10/9
Server3	11	3	4.03	82.20	82.15	82.17	2016/10/10

 

For users, all fields may be used for filtering. For example, "ServerName like 'server'", "ProcessorMaxValue> 10", "DateTime <'2014/1/9 '".

 

In summary, common field types include string, value, date, and boolean value. Why do we need to emphasize the field type, because the comparison results are different for the same value under different field types, such as the number 11> 2, but the string "11" <"2 ".

Second, consider the comparison method. Common examples include "greater than, greater than or equal to, equal to, less than or equal to, less than, not equal to", followed by "in (... Set) "; string types may include" include "," Start match ", and" End match.

 

If the requirements are relatively fixed, it is not necessary to process the filtering of limited fields in order in the code. However, this is rare in actual projects. What's more, the customer will add this condition later and that condition later. If all of them are honestly added one by one, the code will be very bloated and even out of control.

The Expression scheme is recommended in this article. Since Expression is used to operate on a set, it is not used to spell SQL and then use SQLCommand. Applicable:

1) Using EntityFramework as the orm framework

2) directly deal with the complete set

First feel the code

1 public class CriteriaCollectionHandler: ICollectionHandler 2 {3/* By Harvey Hu. @ 2016 */4 5 protected string PropertyName {get; set;} 6 7 protected ComparerEnum Comparer {get; set;} 8 9 protected object Target {get; set ;} // 10 11 public CriteriaCollectionHandler (string propertyName, object target, ComparerEnum comparer) 12 {13 this. propertyName = propertyName; 14 this. comparer = compa Rer; 15 this. target = target; 16} 17 18 private IQueryable <T> Filter <T> (IQueryable <T> source, string propertyName, ComparerEnum comparer, object target) 19 {20 var type = typeof (T); 21 var property = type. getProperty (propertyName, BindingFlags. public | BindingFlags. instance | BindingFlags. ignoreCase); 22 23 24 25 var parameter = Expression. parameter (type, "p"); 26 Expression propertyAccess = Exp Ression. makeMemberAccess (parameter, property); 27 if (property. propertyType. isGenericType & property. propertyType. getGenericTypeDefinition () = typeof (Nullable <>) 28 {29 var getValueOrDefault = property. propertyType. getMethods (). first (p => p. name = "GetValueOrDefault"); 30 propertyAccess = Expression. call (propertyAccess, getValueOrDefault); 31} 32 var constExpression = Expression. constant (Con Verbose (target, property. propertyType); // converts to the target type for comparison with 33 Expression comparisionExpression; 34 switch (comparer) 35 {36 case ComparerEnum. eq: 37 comparisionExpression = Expression. equal (propertyAccess, constExpression); 38 break; 39 case ComparerEnum. ne: 40 comparisionExpression = Expression. notEqual (propertyAccess, constExpression); 41 break; 42 case ComparerEnum. lt: 43 comparisionExpressi On = Expression. lessThan (propertyAccess, constExpression); 44 break; 45 case ComparerEnum. gt: 46 comparisionExpression = Expression. greaterThan (propertyAccess, constExpression); 47 break; 48 case ComparerEnum. le: 49 comparisionExpression = Expression. lessThanOrEqual (propertyAccess, constExpression); 50 break; 51 case ComparerEnum. ge: 52 comparisionExpression = Expression. greaterThanOrEqual (propert YAccess, constExpression); 53 break; 54 case ComparerEnum. StringLike: 55 if (property. PropertyType! = Typeof (string) 56 {57 throw new NotSupportedException ("StringLike is only suitable for string type property! "); 58} 59 60 61 var stringContainsMethod = typeof (CriteriaCollectionHandler ). getMethod ("StringContains"); 62 63 comparisionExpression = Expression. call (stringContainsMethod, propertyAccess, constExpression); 64 65 break; 66 default: 67 comparisionExpression = Expression. equal (propertyAccess, constExpression); 68 break; 69} 70 71 72 var compareExp = Expression. lambda (comparisionExpression, parame Ter); 73 var typeArguments = new Type [] {type}; 74 var methodName = "Where"; // sortOrder = SortDirection. Ascending? "OrderBy": "OrderByDescending"; 75 var resultExp = Expression. call (typeof (Queryable), methodName, typeArguments, source. expression, Expression. quote (compareExp); 76 77 return source. provider. createQuery <T> (resultExp); 78} 79 80 public static bool StringContains (string value, string subValue) 81 {82 if (value = null) 83 {84 return false; 85} 86 87 return value. contains (subValue); 88} 89 90 91 protected object convertize (object convertibleValue, Type targetType) 92 {93 if (null = convertibleValue) 94 {95 return null; 96} 97 98 if (! TargetType. isGenericType) 99 {100 return Convert. changeType (convertibleValue, targetType); 101} 102 else103 {104 Type genericTypeDefinition = targetType. getGenericTypeDefinition (); 105 if (genericTypeDefinition = typeof (Nullable <>) 106 {107 var temp = Convert. changeType (convertibleValue, Nullable. getUnderlyingType (targetType); 108 var result = Activator. createInstance (targetType, temp); 109 return result; 110} 111} 112 throw new InvalidCastException (string. format ("Invalid cast from type \" {0} \ "to type \" {1 }\". ", convertibleValue. getType (). fullName, targetType. fullName); 113} 114 115 116 public virtual ICollection <T> Execute <T> (ICollection <T> values) 117 {118 var result = Filter (values. asQueryable (), this. propertyName, this. comparer, this. target ). toList (); 119 return result; 120} 121 122}

 

Example (pseudo code ):

1 var criteria1 = New CriteriaCollectionHandler(“ServerName”, “server”, ComparerEnum.StringLike);  // serverName like 'server'”2 var criteria2 = New CriteriaCollectionHandler(“ProcessorMaxValue”, 10, ComparerEnum.Gt);3 var criteria3 = New CriteriaCollectionHandler(“Datetime”, Datetime.Parse("2016/12/9"), ComparerEnum.lt);4 ICollection<T> result =  criteria3.Execute(5                                             criteria2.Execute(6                                                      criteria1.Execute(YourDataCollection)));

 

 

The core is the Filter () method -- IQueryable <T> Filter <T> (IQueryable <T> source, string propertyName, ComparerEnum comparer, object target ).

ICollectionHandler is an interface used to process Collection objects. The paging, sorting, and filtering operations mentioned above can be applied to this interface. The Execute method of this interface processes a set and returns a set. Filtering is also the logic, so this interface is applicable.

In the Filter () method, a Lamda Expression is constructed by Expression, such as p => p. Property = target. There are several issues with this expression:

1) how to obtain p. Property? Obtained Through Type reflection.

2) how to obtain the judgment operation? Comparer enumeration. If it is a regular comparison, the system calls the Expression-related method generation directly, for example, Expression. Equal (). If it is special, it calls the custom method generation through Expression. Call, for example, StringLike

3) What is the type of the Compare value?? Obtain the p. Property type and forcibly convert target to this type. For more information, see Convertor.

4) Whether the Nullable type is supported? Yes. But this is a pitfall. Because Nullable <T> does not support direct comparison with T, the target cannot be converted to the Nullable <T> type. It can only be of the T type, therefore, lamda expressions can only use p => p. property. getValueOrDefault () = target specification. Therefore, in the Convertor () method, the nullable <T> type is also determined.

After the Lamda expression is constructed, you can use the Where method of Linq to implement filtering. This method is also applicable to the Call method of Expression. Finally, use IQueryProvider of IQuaryable

.

5) are other comparison operations supported? I think it can be implemented through proper extension. For example, StringLike is our own extension comparison method. Of course, this method is not provided by EntityFramework, so it does not support Queryable of EF.

 

Code implementation analysis is now available in this section. In actual use, each condition is encapsulated into a CriteriaCollectionHandler object, and then the "logical and" operations can be completed by calling them in turn. See the preceding implementation example.

What should I do if I want to implement "logic or? The current consideration is to process two sets of intersect. If you have any better solutions, please reply to the discussion.

 

Next, I will discuss some special fields, such as non-Public Property filtering.

 

Note: When Using Expression, you can also refer to the articles of other friends in the blog. I hope to help some friends.