Preface
Linq is a very useful set processing library in C, which can help us simplify a large number of smelly and long nested loops and make the processing logic clear. EF queries also rely on Linq. But Linq has some disadvantages compared with sql, the most important is the difficulty of dynamic query construction. sql only needs simple string splicing, so it is very difficult to operate (of course, it is quite easy to make mistakes). Because Linq expression depends on strong type expression tree, dynamic construction of query expression is basically equivalent to handwritten AST (abstract syntax tree), which can be said to be extremely difficult.
AST has entered the field of compilation principle. The understanding of computer system needs to be several orders of magnitude higher than that of general crud writing business code, which also causes many people to think EF is not easy to use. In order to write a dynamic query, the cost of learning compilation principle is quite high. Later, there are some class libraries like DynamicLinq that can write dynamic queries with expression strings.
In the spirit of learning, I studied for a while and wrote a helper class that can dynamically construct any complex Where expression within my imagination. The filter condition of this auxiliary class uses the data structure of JqGrid's advanced query, which is the first js table plug-in that I know can generate complex nested queries and query data can be easily parsed by json. You can seamlessly generate Where expressions based on JqGrid's advanced queries.
text
Realization
JqGrid advanced query data structure definition is used to deserialize:
1 public class JqGridParameter 2 { 3 /// <summary> 4 /// Search or not, it should be bool，true 5 /// </summary> 6 public string _search { get; set; } 7 /// <summary> 8 /// Number of requests sent, convenient for the server to handle repeated requests 9 /// </summary> 10 public long Nd { get; set; } 11 /// <summary> 12 /// Number of data items on current page 13 /// </summary> 14 public int Rows { get; set; } 15 /// <summary> 16 /// Page number 17 /// </summary> 18 public int Page { get; set; } 19 /// <summary> 20 /// Sort column, sort column name for multi column sorting+Blank space+Sort by, separating multiple columns with commas. Example: id asc,name desc 21 /// </summary> 22 public string Sidx { get; set; } 23 /// <summary> 24 /// Sorted columns after separation 25 /// </summary> 26 public string[][] SIdx => Sidx.Split(", ").Select(s => s.Split(" ")).ToArray(); 27 /// <summary> 28 /// Sort by: asc,desc 29 /// </summary> 30 public string Sord { get; set; } 31 /// <summary> 32 /// Advanced search criteria json 33 /// </summary> 34 public string Filters { get; set; } 35 36 /// <summary> 37 /// Serialized advanced search object 38 /// </summary> 39 public JqGridSearchRuleGroup FilterObject => Filters.IsNullOrWhiteSpace() 40 ? new JqGridSearchRuleGroup { Rules = new[] { new JqGridSearchRule { Op = SearchOper, Data = SearchString, Field = SearchField } } } 41 : JsonSerializer.Deserialize<JqGridSearchRuleGroup>(Filters ?? string.Empty); 42 43 /// <summary> 44 /// Simple search fields 45 /// </summary> 46 public string SearchField { get; set; } 47 /// <summary> 48 /// Simple search keywords 49 /// </summary> 50 public string SearchString { get; set; } 51 /// <summary> 52 /// Simple search operation 53 /// </summary> 54 public string SearchOper { get; set; } 55 56 } 57 58 /// <summary> 59 /// Advanced search criteria group 60 /// </summary> 61 public class JqGridSearchRuleGroup 62 { 63 /// <summary> 64 /// Condition combination method: and,or 65 /// </summary> 66 public string GroupOp { get; set; } 67 /// <summary> 68 /// Search criteria collection 69 /// </summary> 70 public JqGridSearchRule[] Rules { get; set; } 71 /// <summary> 72 /// Search criteria group collection 73 /// </summary> 74 public JqGridSearchRuleGroup[] Groups { get; set; } 75 } 76 77 /// <summary> 78 /// Advanced search criteria 79 /// </summary> 80 public class JqGridSearchRule 81 { 82 /// <summary> 83 /// Search field 84 /// </summary> 85 public string Field { get; set; } 86 /// <summary> 87 /// Big hump naming of search field 88 /// </summary> 89 public string PascalField => Field?.Length > 0 ? Field.Substring(0, 1).ToUpper() + Field.Substring(1) : Field; 90 /// <summary> 91 /// Search operation 92 /// </summary> 93 public string Op { get; set; } 94 /// <summary> 95 /// Search keywords 96 /// </summary> 97 public string Data { get; set; } 98 }
Where condition generator, the code is a little more, a little more complex. However, there are a lot of comments. It should be easy to understand if you are a little bit patient:
1 /// <summary> 2 /// JqGrid Search expression extension 3 /// </summary> 4 public static class JqGridSearchExtensions 5 { 6 //The front-end (not) belong to the condition search needs to pass a json Array string as argument 7 //In order to avoid the separator is part of the search content when searching the string, which causes the search keyword to make mistakes 8 //No matter what delimiter is defined, this awkward situation cannot be avoided completely, so standard json Spare all later trouble 9 /// <summary> 10 /// Construct according to search criteria where Expressions, supporting JqGrid Advanced search 11 /// </summary> 12 /// <typeparam name="T">Object type searched</typeparam> 13 /// <param name="ruleGroup">JqGrid Search criteria group</param> 14 /// <param name="propertyMap">Attribute mapping, mapping the name of the search rule to the attribute name. If the attribute is a complex type, use the point number to continue to access the internal attribute</param> 15 /// <returns>where Expression</returns> 16 public static Expression<Func<T, bool>> BuildWhere<T>(JqGridSearchRuleGroup ruleGroup, IDictionary<string, string> propertyMap) 17 { 18 ParameterExpression parameter = Expression.Parameter(typeof(T), "searchObject"); 19 20 return Expression.Lambda<Func<T, bool>>(BuildGroupExpression<T>(ruleGroup, parameter, propertyMap), parameter); 21 } 22 23 /// <summary> 24 /// An expression that constructs a search criteria group (a group may contain several sub criteria groups) 25 /// </summary> 26 /// <typeparam name="T">Object type searched</typeparam> 27 /// <param name="group">Condition group</param> 28 /// <param name="parameter">Parameter expression</param> 29 /// <param name="propertyMap">Attribute mapping</param> 30 /// <returns>Return bool Expression for condition group of</returns> 31 private static Expression BuildGroupExpression<T>(JqGridSearchRuleGroup group, ParameterExpression parameter, IDictionary<string, string> propertyMap) 32 { 33 List<Expression> expressions = new List<Expression>(); 34 foreach (var rule in group.Rules ?? new JqGridSearchRule[0]) 35 { 36 expressions.Add(BuildRuleExpression<T>(rule, parameter, propertyMap)); 37 } 38 39 foreach (var subGroup in group.Groups ?? new JqGridSearchRuleGroup[0]) 40 { 41 expressions.Add(BuildGroupExpression<T>(subGroup, parameter, propertyMap)); 42 } 43 44 if (expressions.Count == 0) 45 { 46 throw new InvalidOperationException("structure where Clause exception, generated 0 comparison condition expressions."); 47 } 48 49 if (expressions.Count == 1) 50 { 51 return expressions[0]; 52 } 53 54 var expression = expressions[0]; 55 switch (group.GroupOp) 56 { 57 case "AND": 58 foreach (var exp in expressions.Skip(1)) 59 { 60 expression = Expression.AndAlso(expression, exp); 61 } 62 break; 63 case "OR": 64 foreach (var exp in expressions.Skip(1)) 65 { 66 expression = Expression.OrElse(expression, exp); 67 } 68 break; 69 default: 70 throw new InvalidOperationException($"Creation not supported{group.GroupOp}Logical expression of type"); 71 } 72 73 return expression; 74 } 75 76 private static readonly string[] SpecialRuleOps = {"in", "ni", "nu", "nn"}; 77 78 /// <summary> 79 /// Construct conditional expression 80 /// </summary> 81 /// <typeparam name="T">Object type searched</typeparam> 82 /// <param name="rule">condition</param> 83 /// <param name="parameter">parameter</param> 84 /// <param name="propertyMap">Attribute mapping</param> 85 /// <returns>Return bool Conditional expression of</returns> 86 private static Expression BuildRuleExpression<T>(JqGridSearchRule rule, ParameterExpression parameter, 87 IDictionary<string, string> propertyMap) 88 { 89 Expression l; 90 91 string[] names = null; 92 //If the entity property name is inconsistent with the front-end name, or the property is a custom type, you need to continue to access its internal properties, separated by dots 93 if (propertyMap?.ContainsKey(rule.Field) == true) 94 { 95 names = propertyMap[rule.Field].Split('.', StringSplitOptions.RemoveEmptyEntries); 96 l = Expression.Property(parameter, names[0]); 97 foreach (var name in names.Skip(1)) 98 { 99 l = Expression.Property(l, name); 100 } 101 } 102 else 103 { 104 l = Expression.Property(parameter, rule.PascalField); 105 } 106 107 Expression r = null; //Value expression 108 Expression e; //Return bool Comparison expressions for 109 110 //Belong to and not belong to comparison is multi value comparison, need to call Contains Method instead of calling the comparison operator 111 //Null and non null right values are constants null，No construction required 112 var specialRuleOps = SpecialRuleOps; 113 114 var isNullable = false; 115 var pt = typeof(T); 116 if(names != null) 117 { 118 foreach(var name in names) 119 { 120 pt = pt.GetProperty(name).PropertyType; 121 } 122 } 123 else 124 { 125 pt = pt.GetProperty(rule.PascalField).PropertyType; 126 } 127 128 //If the property type is nullable, take the internal type 129 if (pt.IsDerivedFrom(typeof(Nullable<>))) 130 { 131 isNullable = true; 132 pt = pt.GenericTypeArguments[0]; 133 } 134 135 //Create a constant value expression (that is, a r) 136 if (!specialRuleOps.Contains(rule.Op)) 137 { 138 switch (pt) 139 { 140 case Type ct when ct == typeof(bool): 141 r = BuildConstantExpression(rule, bool.Parse); 142 break; 143 144 #region Written words 145 146 case Type ct when ct == typeof(char): 147 r = BuildConstantExpression(rule, str => str[0]); 148 break; 149 case Type ct when ct == typeof(string): 150 r = BuildConstantExpression(rule, str => str); 151 break; 152 153 #endregion 154 155 #region Signed integer 156 157 case Type ct when ct == typeof(sbyte): 158 r = BuildConstantExpression(rule, sbyte.Parse); 159 break; 160 case Type ct when ct == typeof(short): 161 r = BuildConstantExpression(rule, short.Parse); 162 break; 163 case Type ct when ct == typeof(int): 164 r = BuildConstantExpression(rule, int.Parse); 165 break; 166 case Type ct when ct == typeof(long): 167 r = BuildConstantExpression(rule, long.Parse); 168 break; 169 170 #endregion 171 172 #region Unsigned integer 173 174 case Type ct when ct == typeof(byte): 175 r = BuildConstantExpression(rule, byte.Parse); 176 break; 177 case Type ct when ct == typeof(ushort): 178 r = BuildConstantExpression(rule, ushort.Parse); 179 break; 180 case Type ct when ct == typeof(uint): 181 r = BuildConstantExpression(rule, uint.Parse); 182 break; 183 case Type ct when ct == typeof(ulong): 184 r = BuildConstantExpression(rule, ulong.Parse); 185 break; 186 187 #endregion 188 189 #region decimal 190 191 case Type ct when ct == typeof(float): 192 r = BuildConstantExpression(rule, float.Parse); 193 break; 194 case Type ct when ct == typeof(double): 195 r = BuildConstantExpression(rule, double.Parse); 196 break; 197 case Type ct when ct == typeof(decimal): 198 r = BuildConstantExpression(rule, decimal.Parse); 199 break; 200 201 #endregion 202 203 #region Other common types 204 205 case Type ct when ct == typeof(DateTime): 206 r = BuildConstantExpression(rule, DateTime.Parse); 207 break; 208 case Type ct when ct == typeof(DateTimeOffset): 209 r = BuildConstantExpression(rule, DateTimeOffset.Parse); 210 break; 211 case Type ct when ct == typeof(Guid): 212 r = BuildConstantExpression(rule, Guid.Parse); 213 break; 214 case Type ct when ct.IsEnum: 215 r = Expression.Constant(rule.Data.ToEnumObject(ct)); 216 break; 217 218 #endregion 219 220 default: 221 throw new InvalidOperationException($"Creation not supported{pt.FullName}Data expression of type"); 222 } 223 } 224 225 if (r != null && pt.IsValueType && isNullable) 226 { 227 var gt = typeof(Nullable<>).MakeGenericType(pt); 228 r = Expression.Convert(r, gt); 229 } 230 231 switch (rule.Op) 232 { 233 case "eq": //Be equal to 234 e = Expression.Equal(l, r); 235 break; 236 case "ne": //Not equal to 237 e = Expression.NotEqual(l, r); 238 break; 239 case "lt": //less than 240 e = Expression.LessThan(l, r); 241 break; 242 case "le": //Less than or equal to 243 e = Expression.LessThanOrEqual(l, r); 244 break; 245 case "gt": //greater than 246 e = Expression.GreaterThan(l, r); 247 break; 248 case "ge": //Greater than or equal to 249 e = Expression.GreaterThanOrEqual(l, r); 250 break; 251 case "bw": //Starts with (string) 252 if (pt == typeof(string)) 253 { 254 e = Expression.Call(l, pt.GetMethod(nameof(string.StartsWith), new[] {typeof(string)}), r); 255 } 256 else 257 { 258 throw new InvalidOperationException($"Creation not supported{pt.FullName}Start of type in expression"); 259 } 260 261 break; 262 case "bn": //Not at the beginning (string) 263 if (pt == typeof(string)) 264 { 265 e = Expression.Not(Expression.Call(l, pt.GetMethod(nameof(string.StartsWith), new[] {typeof(string)}), r)); 266 } 267 else 268 { 269 throw new InvalidOperationException($"Creation not supported{pt.FullName}Of type does not start with expression"); 270 } 271 272 break; 273 case "ew": //Ending with (string) 274 if (pt == typeof(string)) 275 { 276 e = Expression.Call(l, pt.GetMethod(nameof(string.EndsWith), new[] {typeof(string)}), r); 277 } 278 else 279 { 280 throw new InvalidOperationException($"Creation not supported{pt.FullName}End of type in expression"); 281 } 282 283 break; 284 case "en": //Ending not (string) 285 if (pt == typeof(string)) 286 { 287 e = Expression.Not(Expression.Call(l, pt.GetMethod(nameof(string.EndsWith), new[] {typeof(string)}), r)); 288 } 289 else 290 { 291 throw new InvalidOperationException($"Creation not supported{pt.FullName}Of type does not end in an expression"); 292 } 293 294 break; 295 case "cn": //Include (string) 296 if (pt == typeof(string)) 297 { 298 e = Expression.Call(l, pt.GetMethod(nameof(string.Contains), new[] {typeof(string)}), r); 299 } 300 else 301 { 302 throw new InvalidOperationException($"Creation not supported{pt.FullName}Include expression for type"); 303 } 304 305 break; 306 case "nc": //Does not contain (string) 307 if (pt == typeof(string)) 308 { 309 e = Expression.Not(Expression.Call(l, pt.GetMethod(nameof(string.Contains), new[] {typeof(string)}), r)); 310 } 311 else 312 { 313 throw new InvalidOperationException($"Creation not supported{pt.FullName}Include expression for type"); 314 } 315 316 break; 317 case "in": //Of (is one of the candidate list) 318 e = BuildContainsExpression(rule, l, pt); 319 break; 320 case "ni": //Does not belong to (not one of the candidate list) 321 e = Expression.Not(BuildContainsExpression(rule, l, pt)); 322 break; 323 case "nu": //Empty 324 r = Expression.Constant(null); 325 e = Expression.Equal(l, r); 326 break; 327 case "nn": //Not empty 328 r = Expression.Constant(null); 329 e = Expression.Not(Expression.Equal(l, r)); 330 break; 331 case "bt": //Section 332 throw new NotImplementedException($"Creation not implemented{rule.Op}Comparison expression of type"); 333 default: 334 throw new InvalidOperationException($"Creation not supported{rule.Op}Comparison expression of type"); 335 } 336 337 return e; 338 339 static Expression BuildConstantExpression<TValue>(JqGridSearchRule jRule, Func<string, TValue> valueConvertor) 340 { 341 var rv = valueConvertor(jRule.Data); 342 return Expression.Constant(rv); 343 } 344 } 345 346 /// <summary> 347 /// structure Contains Call expression 348 /// </summary> 349 /// <param name="rule">condition</param> 350 /// <param name="parameter">parameter</param> 351 /// <param name="parameterType">Parameter type</param> 352 /// <returns>Contains Call expression</returns> 353 private static Expression BuildContainsExpression(JqGridSearchRule rule, Expression parameter, Type parameterType) 354 { 355 Expression e = null; 356 357 var genMethod = typeof(Queryable).GetMethods() 358 .Single(m => m.Name == nameof(Queryable.Contains) && m.GetParameters().Length == 2); 359 360 var jsonArray = JsonSerializer.Deserialize<string[]>(rule.Data); 361 362 switch (parameterType) 363 { 364 #region Written words 365 366 case Type ct when ct == typeof(char): 367 if (jsonArray.Any(o => o.Length != 1)) {throw new InvalidOperationException("Wrong candidate in character type candidate list");} 368 e = CallContains(parameter, jsonArray, str => str[0], genMethod, ct); 369 break; 370 case Type ct when ct == typeof(string): 371 e = CallContains(parameter, jsonArray, str => str, genMethod, ct); 372 break; 373 374 #endregion 375 376 #region Signed integer 377 378 case Type ct when ct == typeof(sbyte): 379 e = CallContains(parameter, jsonArray, sbyte.Parse, genMethod, ct); 380 break; 381 case Type ct when ct == typeof(short): 382 e = CallContains(parameter, jsonArray, short.Parse, genMethod, ct); 383 break; 384 case Type ct when ct == typeof(int): 385 e = CallContains(parameter, jsonArray, int.Parse, genMethod, ct); 386 break; 387 case Type ct when ct == typeof(long): 388 e = CallContains(parameter, jsonArray, long.Parse, genMethod, ct); 389 break; 390 391 #endregion 392 393 #region Unsigned integer 394 395 case Type ct when ct == typeof(byte): 396 e = CallContains(parameter, jsonArray, byte.Parse, genMethod, ct); 397 break; 398 case Type ct when ct == typeof(ushort): 399 e = CallContains(parameter, jsonArray, ushort.Parse, genMethod, ct); 400 break; 401 case Type ct when ct == typeof(uint): 402 e = CallContains(parameter, jsonArray, uint.Parse, genMethod, ct); 403 break; 404 case Type ct when ct == typeof(ulong): 405 e = CallContains(parameter, jsonArray, ulong.Parse, genMethod, ct); 406 break; 407 408 #endregion 409 410 #region decimal 411 412 case Type ct when ct == typeof(float): 413 e = CallContains(parameter, jsonArray, float.Parse, genMethod, ct); 414 break; 415 case Type ct when ct == typeof(double): 416 e = CallContains(parameter, jsonArray, double.Parse, genMethod, ct); 417 break; 418 case Type ct when ct == typeof(decimal): 419 e = CallContains(parameter, jsonArray, decimal.Parse, genMethod, ct); 420 break; 421 422 #endregion 423 424 #region Other common types 425 426 case Type ct when ct == typeof(DateTime): 427 e = CallContains(parameter, jsonArray, DateTime.Parse, genMethod, ct); 428 break; 429 case Type ct when ct == typeof(DateTimeOffset): 430 e = CallContains(parameter, jsonArray, DateTimeOffset.Parse, genMethod, ct); 431 break; 432 case Type ct when ct == typeof(Guid): 433 e = CallContains(parameter, jsonArray, Guid.Parse, genMethod, ct); 434 break; 435 case Type ct when ct.IsEnum: 436 e = CallContains(Expression.Convert(parameter, typeof(object)), jsonArray, enumString => enumString.ToEnumObject(ct), genMethod, ct); 437 break; 438 439 #endregion 440 } 441 442 return e; 443 444 static MethodCallExpression CallContains<T>(Expression pa, string[] jArray, Func<string, T> selector, MethodInfo genericMethod, Type type) 445 { 446 var data = jArray.Select(selector).ToArray().AsQueryable(); 447 var method = genericMethod.MakeGenericMethod(type); 448 449 return Expression.Call(null, method, new[] { Expression.Constant(data), pa }); 450 } 451 } 452 }
Use
This is used in Razor Page. Other auxiliary classes and front-end page codes used internally will not be pasted. If you are interested, you can find the GitHub project link at the end of my article:
1 public async Task<IActionResult> OnGetUserListAsync([FromQuery]JqGridParameter jqGridParameter) 2 { 3 var usersQuery = _userManager.Users.AsNoTracking(); 4 if (jqGridParameter._search == "true") 5 { 6 usersQuery = usersQuery.Where(BuildWhere<ApplicationUser>(jqGridParameter.FilterObject, null)); 7 } 8 9 var users = usersQuery.Include(u => u.UserRoles).ThenInclude(ur => ur.Role).OrderBy(u => u.InsertOrder) 10 .Skip((jqGridParameter.Page - 1) * jqGridParameter.Rows).Take(jqGridParameter.Rows).ToList(); 11 var userCount = usersQuery.Count(); 12 var pageCount = Ceiling((double) userCount / jqGridParameter.Rows); 13 return new JsonResult( 14 new 15 { 16 rows //Data set 17 = users.Select(u => new 18 { 19 u.UserName, 20 u.Gender, 21 u.Email, 22 u.PhoneNumber, 23 u.EmailConfirmed, 24 u.PhoneNumberConfirmed, 25 u.CreationTime, 26 u.CreatorId, 27 u.Active, 28 u.LastModificationTime, 29 u.LastModifierId, 30 u.InsertOrder, 31 u.ConcurrencyStamp, 32 //Below is JqGrid Required fields in 33 u.Id //The unique identification of records can be configured as other fields in the plug-in, but it must be able to be used as the unique identification of records and cannot be repeated 34 }), 35 total = pageCount, //PageCount 36 page = jqGridParameter.Page, //Current page number 37 records = userCount //Total number of records 38 } 39 ); 40 }
Access / Identity/Manage/Users/Index after starting the project to try.
epilogue
Through this practice, I have learned a lot about the expression tree. The expression tree is still a high-level structure in the compilation process. IL is really dizzy. It is no better than the original assembly. C Chen is really interesting. It's easy to get started, but it's very deep inside. It's totally two languages in Xiaobai and Dashen's hands. Java adds the Stream and Lambda expression functions in Java 8. It is benchmarking Linq at first glance, but it's hard to say the name. It's quite unpleasant to see that the code is written like a lump in the throat. Due to the lack of expression tree in Stream system, this function of dynamically constructing query expression is impossible to support from the beginning. In addition, Java has no anonymous type, no object initializer, which makes it hard to use Stream every time. The data structure of the intermediate process also needs to specially write classes, and each intermediate class needs to own a file, which is almost dizzy. Failed to copy!
C ා the core of introducing the VaR keyword is to serve anonymous types. After all, it is the type automatically generated by the compiler. When writing code, there is no name at all. What can I do without var? The simplified variable initialization code is only incidental. As a result, Java copied half of the code, or the least important half, to simplify variable initialization code. I don't know what the Java guys are thinking.
Reprint please keep the following content completely and mark it in a conspicuous position. If you delete the following content without authorization for reprint and embezzlement, you will be entitled to pursue legal liability!
Address: https://www.cnblogs.com/coredx/p/12423929.html
Full source code: Github
There are all kinds of small things in it. This is just one of them. If you don't like it, you can Star it.