Linq动态拼接Expression表达式(可多表字段判别)

本文是在这篇博文：https://www.cnblogs.com/liyouming/p/9402113.html 基础上进行的修改补充：扩展了多表的连接查询的动态拼接。

1、添加客户端和服务器端的通用模型，对于本地App也可以使用。

A、查询条件类型化

    /// <summary>
    /// 查询条件
    /// </summary>
    public class LinqSelectCondition
    {
        public LinqSelectCondition()
        {
            ParentFields = new List<string>();
        }
        /// <summary>
        /// 查询字段名称
        /// </summary>
        public string Field { get; set; }
        /// <summary>
        /// 值
        /// </summary>
        public string Value { get; set; }
        /// <summary>
        /// 值类型
        /// </summary>
        public string Type { get; set; }
        /// <summary>
        /// 查询操作类型
        /// </summary>
        public LinqSelectOperator Operator { get; set; }        
        /// <summary>
        /// 父表字段集合，按先后次序，层次依次升高
        /// </summary>
        public List<string> ParentFields { get; set; }
    }//

其中用到的操作比较类型：

public enum LinqSelectOperator
    {
        Contains,
        Equal,
        Greater,
        GreaterEqual,
        Less,
        LessEqual,
        NotEqual,
        InWithEqual,  //对于多个值执行等于比较
        InWithContains,//对于多个值执行包含比较
        Between,
  }

上述用到的ParentFields是一个列表，如果是单表查询，可以不用考虑，如果需要借用父表或者父表的父表等查询字段，则依次往里面添加父键的字段名。

B、排序类

public class LinqOrderCondition
    {
        public LinqOrderCondition()
        {
            ParentFields = new List<string>();
        }
        /// <summary>
        /// 查询字段名称
        /// </summary>
        public string Field { get; set; }
        /// <summary>
        /// 排序类型
        /// </summary>
        public LinqOrderType OrderType { get; set; }
        /// <summary>
        /// 父表字段集合，按先后次序，层次依次升高
        /// </summary>
        public List<string> ParentFields { get; set; }
 }
public enum LinqOrderType
    {
        ASC,
        DESC,
 }

C、页过滤类

public class PaginationModel
    {
        /// <summary>
        /// 当前页起始号，从1开的
        /// </summary>
        public int CurrentPageIndex { get; set; }
        /// <summary>
        /// 每页的尺寸
        /// </summary>

        public int PageSize { get; set; }
  }

D、打包用于客户端和服务器的公共模型

public class LinqConditionModel
    {
        public LinqConditionModel()
        {
            SelectConditions = new List<LinqSelectCondition>();
            OrderConditions = new List<LinqOrderCondition>();
        }
        /// <summary>
        /// 选择条件集合
        /// </summary>
        public List<LinqSelectCondition> SelectConditions { get; set; }
        /// <summary>
        /// 查询条件集合
        /// </summary>
        public List<LinqOrderCondition> OrderConditions { get; set; }
        /// <summary>
        /// 页查询信息
        /// </summary>
        public PaginationModel PageInfo { get; set; }
  }

LinqConditionModel主要是用于服务器和客户端的数据交互，如果是本地App，可以简化上面的一些定义。

对于EF Core的父子表映射关系的定义有多种模式，上述的模式主要对应在子表中定义对父表的映射，父表不做任何处理。

譬如一个父表：

public class Manufacture
{
    public long ID { get; set; }
    public string Name { get; set; }
}

子表的定义如下(ManufactureID映射到父表的ID)：

public class Part
{
    public long ID { get; set; }
    public string Name { get; set; }
    public long ManufactureID { get; set; }
    [ForeignKey("ManufactureID ")]
    public virtual Manufacture OfManufacture { get; set; }   
}

2、表达式拼接

    public class LinqExpressionParser<T>
    {
        public Expression<Func<T, bool>> ParserConditions(IEnumerable<LinqSelectCondition> conditions)
        {
            //将条件转化成表达式的Body
            var parameter = Expression.Parameter(typeof(T));
            var query = ParseExpressionBody(conditions, parameter);
            return Expression.Lambda<Func<T, bool>>(query, parameter);
        }
        private Expression ParseExpressionBody(IEnumerable<LinqSelectCondition> conditions, ParameterExpression parameter)
        {
            if (conditions == null || conditions.Count() == 0)
            {
                return Expression.Constant(true, typeof(bool));
            }
            else if (conditions.Count() == 1)
            {
                return ParseCondition(conditions.First(), parameter);
            }
            else
            {
                Expression left = ParseCondition(conditions.First(), parameter);
                Expression right = ParseExpressionBody(conditions.Skip(1), parameter);
                return Expression.AndAlso(left, right); //此位置可执行其他逻辑操作
            }
        }
        //对查询条件进行处理
        private Expression ParseCondition(LinqSelectCondition condition,ParameterExpression parameter)
        {
            ParameterExpression p = parameter;
            Expression key;
            //对值进行转换处理
            object convertValue = condition.Value;
            if (condition.Type != null 
                && condition.Operator!=LinqSelectOperator.InWithContains 
                && condition.Operator != LinqSelectOperator.InWithEqual)
            {
                if (condition.Type.ToUpper() == "DATETIME")
                {
                    convertValue = System.Convert.ToDateTime(condition.Value);
                }
                else if (condition.Type.ToUpper() == "INT")
                {
                    convertValue = System.Convert.ToInt32(condition.Value);
                }
                else if (condition.Type.ToUpper() == "LONG")
                {
                    convertValue = System.Convert.ToInt64(condition.Value);
                }
                else if (condition.Type.ToUpper() == "DOUBLE")
                {
                    convertValue = System.Convert.ToDouble(condition.Value);
                }
                else if (condition.Type.ToUpper() == "BOOL")
                {
                    convertValue = System.Convert.ToBoolean(condition.Value);
                }
            }
            Expression value = Expression.Constant(convertValue);
            //参数化字段
            if(condition.ParentFields!=null&& condition.ParentFields.Count > 0)
            {
                key = p;
                foreach (var parent in condition.ParentFields)
                {
                    key = Expression.Property(key, parent);
                }
                key = Expression.Property(key, condition.Field);
            }
            else
            {
                key = Expression.Property(p, condition.Field);
            }
            switch (condition.Operator)
            {
                case LinqSelectOperator.Contains:
                    return Expression.Call(key, typeof(string).GetMethod("Contains", new Type[] { typeof(string) }), value);
                case LinqSelectOperator.Equal:
                    return Expression.Equal(key, Expression.Convert(value, key.Type));
                case LinqSelectOperator.Greater:
                    return Expression.GreaterThan(key, Expression.Convert(value, key.Type));
                case LinqSelectOperator.GreaterEqual:
                    return Expression.GreaterThanOrEqual(key, Expression.Convert(value, key.Type));
                case LinqSelectOperator.Less:
                    return Expression.LessThan(key, Expression.Convert(value, key.Type));
                case LinqSelectOperator.LessEqual:
                    return Expression.LessThanOrEqual(key, Expression.Convert(value, key.Type));
                case LinqSelectOperator.NotEqual:
                    return Expression.NotEqual(key, Expression.Convert(value, key.Type));
                case LinqSelectOperator.InWithEqual:
                    return ParaseIn(key, condition,true);
                case LinqSelectOperator.InWithContains:
                    return ParaseIn(key, condition, false);
                case LinqSelectOperator.Between:
                    return ParaseBetween(key, condition);
                default:
                    throw new NotImplementedException("不支持此操作");
            }
        }
        //对查询“Between"的处理
        private Expression ParaseBetween(Expression key, LinqSelectCondition conditions)
        {
            //ParameterExpression p = parameter;
            //Expression key = Expression.Property(p, conditions.Field);
            var valueArr = conditions.Value.Split(',');
            if (valueArr.Length != 2)
            {
                throw new NotImplementedException("ParaseBetween参数错误");
            }
            try
            {
                int.Parse(valueArr[0]);
                int.Parse(valueArr[1]);
            }
            catch
            {
                throw new NotImplementedException("ParaseBetween参数只能为数字");
            }
            Expression expression = Expression.Constant(true, typeof(bool));
            //开始位置
            Expression startvalue = Expression.Constant(int.Parse(valueArr[0]));
            Expression start = Expression.GreaterThanOrEqual(key, Expression.Convert(startvalue, key.Type));

            Expression endvalue = Expression.Constant(int.Parse(valueArr[1]));
            Expression end = Expression.GreaterThanOrEqual(key, Expression.Convert(endvalue, key.Type));
            return Expression.AndAlso(start, end);
        }
        //对查询“in"的处理
        private Expression ParaseIn(Expression key, LinqSelectCondition conditions,bool isEqual)
        {
            var valueArr = conditions.Value.Split(',');
            Expression expression = Expression.Constant(false, typeof(bool));
            foreach (var itemVal in valueArr)
            {
                object conValue = itemVal;
                Type keyType = key.Type;
                if (conditions.Type?.ToUpper() == "INT")
                {
                    conValue = System.Convert.ToInt32(itemVal);
                    keyType = typeof(int);
                }
                else if (conditions.Type?.ToUpper() == "LONG")
                {
                    conValue = System.Convert.ToInt64(itemVal);
                    keyType = typeof(long);
                }
                Expression value = Expression.Constant(conValue);
                Expression right;
                if(isEqual)
                   right = Expression.Equal(key, Expression.Convert(value, keyType));
                else 
                   right = Expression.Call(key, typeof(string).GetMethod("Contains", new Type[] { typeof(string) }), value);
                expression = Expression.Or(expression, right);
            }
            return expression;
        }
        
    }//

上面的方法ParseExpressionBody()最后的一段可以根据逻辑执行其他的条件或等拼接方式，但因为用得少，和原文一样也只有条件与，如果有谁想扩展多种拼接方式，可以修改此处。

3、Linq表达式扩展

    public static class LinqExpressionExtended
    {
        //扩展查询
        public static IQueryable<T> QueryConditions<T>(this IQueryable<T> query, IEnumerable<LinqSelectCondition> conditions)
        {
            var parser = new LinqExpressionParser<T>();
            var filter = parser.ParserConditions(conditions);
            return query.Where(filter);
        }
        //扩展多条件排序
        public static IQueryable<T> OrderConditions<T>(this IQueryable<T> query, IEnumerable<LinqOrderCondition> orderConditions)
        {
            foreach (var orderinfo in orderConditions)
            {
                var parameter = Expression.Parameter(typeof(T));
                Expression propertySelector= parameter;
                if (orderinfo.ParentFields != null && orderinfo.ParentFields.Count > 0)
                {
                    foreach (var parent in orderinfo.ParentFields)
                    {
                        propertySelector = Expression.Property(propertySelector, parent);
                    }
                    propertySelector = Expression.Property(propertySelector, orderinfo.Field);
                }
                else
                {
                    propertySelector = Expression.Property(propertySelector, orderinfo.Field);
                }
                if (propertySelector.Type == typeof(long)) //int64不能直接转换为object
                {
                    var orderby = Expression.Lambda<Func<T, long>>(propertySelector, parameter);
                    //var funcType = typeof(Func<,>).MakeGenericType(typeof(T), propertyInfo.PropertyType);
                    //var orderby = Expression.Lambda(funcType,propertySelector, parameter);
                    if (orderinfo.OrderType == LinqOrderType.DESC)
                        query = query.OrderByDescending(orderby);
                    else
                        query = query.OrderBy(orderby);
                }
                else if (propertySelector.Type == typeof(int)) //int64不能直接转换为object
                {
                    var orderby = Expression.Lambda<Func<T, int>>(propertySelector, parameter);
                    //var funcType = typeof(Func<,>).MakeGenericType(typeof(T), propertyInfo.PropertyType);
                    //var orderby = Expression.Lambda(funcType,propertySelector, parameter);
                    if (orderinfo.OrderType == LinqOrderType.DESC)
                        query = query.OrderByDescending(orderby);
                    else
                        query = query.OrderBy(orderby);
                }
                else if (propertySelector.Type == typeof(DateTime))
                {
                    var orderby = Expression.Lambda<Func<T, DateTime>>(propertySelector, parameter);
                    if (orderinfo.OrderType == LinqOrderType.DESC)
                        query = query.OrderByDescending(orderby);
                    else
                        query = query.OrderBy(orderby);
                }
                else if (propertySelector.Type == typeof(TimeSpan))
                {
                    var orderby = Expression.Lambda<Func<T, TimeSpan>>(propertySelector, parameter);
                    if (orderinfo.OrderType == LinqOrderType.DESC)
                        query = query.OrderByDescending(orderby);
                    else
                        query = query.OrderBy(orderby);
                }
                else
                {
                    var orderby = Expression.Lambda<Func<T, object>>(propertySelector, parameter);
                    if (orderinfo.OrderType == LinqOrderType.DESC)
                        query = query.OrderByDescending(orderby);
                    else
                        query = query.OrderBy(orderby);
                }

            }
            return query;
        }
        //扩展分页
        public static IQueryable<T> Pager<T>(this IQueryable<T> query, int? pageindex, int? pagesize)
        {
            if (pageindex == null || pagesize == null) return query;
            return query.Skip(((int)pageindex - 1) * (int)pagesize).Take((int)pagesize);
        }
        //扩展页统计
        public async static Task<int> PageCountAsync<T>(this IQueryable<T> query, int pagesize)
        {
            return (int)Math.Ceiling((double)(await query.CountAsync() / pagesize)); 
        }
       
    }//

注意上述在对OrderConditions进行扩展时，string类型可以直接对应object,而像long之类的类型需要进行类型转换，或者如上述的判别后直接将泛型类型固定，否则运行会报错，对于“Pager”扩展，使用了可空的"int"，目的是传递时如果未对PaginationModel赋值，即使调用了Pager，也不会进行分页查询，这样简化了查询判断。

4、使用

表内字段的条件判别如下：

        LinqConditionModel conditions = new LinqConditionModel();
            if (!string.IsNullOrEmpty(SearchName))
            {
                conditions.SelectConditions.Add(new LinqSelectCondition()
                {
                    Field = "Name",
                    Operator = LinqSelectOperator.Contains,
                    Value = SearchName,
                });
          }

如果要引用父表的字段，如下：

      if (!string.IsNullOrEmpty(SearchManufacture))
            {
                var con = new LinqSelectCondition()
                {
                    Field = "Name",
                    Operator = LinqSelectOperator.InWithContains,
                    Value = SearchManufacture,
                };
                con.ParentFields.Add("OfManufacture");
                conditions.SelectConditions.Add(con);
         }

        如果还要引用父表的父表，则继续添加字段到con.ParentFields，此时的Field属性对应最终的父表内的相应字段名称。比较符InWithContains可以让用户输入属性SearchManufacture的值类似为”sie,sch”,则会搜索厂家中Name字段中包含sie或者sch的数据，对于字段为[string]类型，可以不指定查询字段类型 [Type] ，但对于其他类型，则需要指定。

以上内容可以适用于多表取条件，查询单表数据的方式，如果要获取相关联的父表数据，简单的使用Include()即可搞定。