. NET in-depth practical series -- how does EF write filter conditions?,. net -- ef

. NET in-depth practical series -- how does EF write filter conditions?,. net -- ef

Unique access address for this article: http://www.cnblogs.com/yubaolee/p/DynamicLinq.html

For system development, filtering by different fields is a common requirement. In EF, the common practice is:

/// <Summary> /// just a simple example. Only two filtering conditions are used: // </summary> IEnumerable <UserInfo> Search (string username = "", string usertype = "") {var query = _ context. userInfoes. asQueryable (); if (! String. IsNullOrEmpty (username) query = query. Where (u => u. UserName = username); if (! String. IsNullOrEmpty (usertype) query = query. Where (u => u. UserType = usertype); return query. ToList ();}

In this case, if I have a new requirement, for example, to query users whose usernames must contain an indefinite number of keywords. Then we can use the parameter array to perform upgrades similar to the following.

  private IEnumerable<UserInfo> Search(params string[] keys)        {            var query = _context.UserInfoes.AsQueryable();            foreach (var key in keys)            {                query = query.Where(u => u.UserName.Contains(key));            }            return query.ToList();        }

The above code works well. If the requirement becomes: what should we do if the query username contains at least one keyword? It is obvious that the Or operation is used, but how is it most reasonable? Normal queries cannot solve this problem. So Joe Albahari used PredicateBuilder in his blog to easily solve the problem:

   IQueryable<UserInfo> Search(params string[] keys)        {            var predicate = PredicateBuilder.False<UserInfo>();            foreach (string keyword in keys)            {                predicate = predicate.Or(p => p.UserName.Contains(keyword));            }            return _context.UserInfoes.Where(predicate);        }

For the implementation of PredicateBuilder, you can view it in his blog or directly search for and add LINQKit references in nuget. PredicateBuilder can solve the problem of dynamically generating Lambda, And supports And/Or And other mainstream operations. However, it still fails to solve a problem: If the attributes in the query conditions (that is, the fields in the database) are also uncertain, what should we do?

At this moment, Scott stood up. In his blog Dynamic LINQ (Part 1: Using the LINQ Dynamic Query Library), he integrated EF into a join SQL method to achieve this requirement. As follows:

  private IQueryable<UserInfo> Search(string key, string value)        {            return _context.UserInfoes.Where("@0 ='@1'", key, value);        }

In this way, we are not afraid of the need for endless changes. We can call any of them:

Var users = Search ("UserNmae", "yubaolee"); // filter username var users2 = Search ("UserType", "administrator"); // filter user types

You can also use Key-Value and other combinations to form more powerful query functions. However, things in the world are not so beautiful. You will use it in practice to discover that you don't even support like, and use it to discover that you don't even support guids.

Alas! I am here. I have never used it before, and I will encounter such a bird thing. Do it yourself!

Well, the following is the main content of the blog post. If you don't see the following content, thank you! Unfortunately...

 

Analyze our requirements:

Based on the above requirements, we can refer to the implementation method of PredicateBuilder, use the expression tree to generate dynamic lambda, and then pass it to the filter conditions of ef. As follows:

Public class Filter {public string Key {get; set;} // The filtered keyword public string Value {get; set;} // The filtered Value public string Contract {get; set;} // filter constraints such: '<''<=''> = ''like' and so on} public static class DynamicLinq {// <summary> // create parameters in lambda, c => c. c // in xxx = xx </summary> public static ParameterExpression CreateLambdaParam <T> (string name) {return Expression. parameter (typeof (T), name) ;}/// <summary> // create the body section of the linq expression, that is, c => c. c. In xxx = xx. xxx = xx // </summary> public static Expression GenerateBody <T> (this ParameterExpression param, Filter filterObj) {PropertyInfo property = typeof (T ). getProperty (filterObj. key); // assemble the left Expression left = Expression. property (param, property); // assemble Expression right = null on the right; // todo: extend the type of if (property. propertyType = typeof (int) {right = Expression. constant (int. parse (filterObj. value);} else if (property. propertyType = typeof (DateTime) {right = Expression. constant (DateTime. parse (filterObj. value);} else if (property. propertyType = typeof (string) {right = Expression. constant (filterObj. value);} else if (property. propertyType = typeof (decimal) {right = Expression. constant (decimal. parse (filterObj. value);} else if (property. propertyType = typeof (Guid) {right = Expression. constant (Guid. parse (filterObj. value);} else if (property. propertyType = typeof (bool) {right = Expression. constant (filterObj. value. equals ("1");} else {throw new Exception ("the type of the Key cannot be parsed");} // todo: the following compares Expression filter = Expression according to your needs. equal (left, right); switch (filterObj. contract) {case "<=": filter = Expression. lessThanOrEqual (left, right); break; case "<": filter = Expression. lessThan (left, right); break; case ">": filter = Expression. greaterThan (left, right); break; case "> =": filter = Expression. greaterThanOrEqual (left, right); break; case "like": filter = Expression. call (left, typeof (string ). getMethod ("Contains", new [] {typeof (string)}), Expression. constant (filterObj. value); break;} return filter;} // <summary> // create a complete lambda, that is, c => c. xxx = xx // </summary> public static LambdaExpression GenerateLambda (this ParameterExpression param, Expression body) {return Expression. lambda (body, param) ;}/// <summary> /// create a complete lambda, to be compatible with the where statement in EF /// </summary> public static Expression <Func <T, bool> GenerateTypeLambda <T> (this ParameterExpression param, Expression body) {return (Expression <Func <T, bool>) (param. generatelamso (body);} public static Expression AndAlso (this Expression expression, Expression expressionRight) {return Expression. andAlso (expression, expressionRight);} public static Expression Or (this Expression expression, Expression expressionRight) {return Expression. or (expression, expressionRight);} public static Expression And (this Expression expression, Expression expressionRight) {return Expression. and (expression, expressionRight );}}

Let's take a look at our client call:

// Simulate the Filter object var filters = new Filter [] {new Filter {Key = "UserName", Value = "yubaolee", Contract = "like "}, new Filter {Key = "UserType", Value = "administrator", Contract = "=" }}; var param = DynamicLinq. createLambdaParam <UserInfo> ("c"); Expression body = Expression. constant (true); // The default value is true foreach (var filter in filters) {body = body. andAlso (param. generateBody <UserInfo> (filter); // You can freely combine them as needed} var lambda = param. generatetypelamers <UserInfo> (body); // The final lambda var users = _ context. userInfoes. where (lambda); // obtain the final result Console. read ();

At this time, we can combine them freely, but the amount of client code seems to be a lot. We will optimize the encapsulation:

Public static class DynamicExtention {public static IQueryable <T> Where <T> (this IQueryable <T> query, Filter [] filters) {var param = DynamicLinq. createLambdaParam <T> ("c"); Expression body = Expression. constant (true); // The default value is true foreach (var filter in filters) {body = body. andAlso (param. generateBody <T> (filter); // You can freely combine them as needed} var lambda = param. generateTypeLambda <T> (body); // The final lambda return query. where (lambda );}}

Finally, let's look at our client call:

// Simulate the Filter object var filters = new Filter [] {new Filter {Key = "UserName", Value = "yubaolee", Contract = "like "}, new Filter {Key = "UserType", Value = "administrator", Contract = "=" }}; var users = _ context. userInfoes. where (filters); // obtain the final result Console. read ();

The code is so clean and tidy. Because the expanded Where statement is based on generics, you can use it directly regardless of the DbSet of your EF set. If you further deepen the Filter function and extend it to a tree structure, you can implement a combined query. Even join table queries are not supported.