通过Linq表达式实现SqlSugar动态查询

本文中内容基于SqlSugar的ISugarQueryable接口类型进行的扩展，可以根据个人需要进行调整，比如IQueryable等

nuget安装SqlSugar包，不是本文重点的步骤省略。

常量定义

结合常见的查询操作，我们首先定义一些常量

逻辑运算，常用的有并且，或者，异或。分别取并集，交集和差集

public class FilterLogic
{
    /// <summary>
    /// 并且
    /// </summary>
    public const string AND = "and";
    /// <summary>
    /// 或者
    /// </summary>
    public const string OR = "or";
    /// <summary>
    /// 异或
    /// </summary>
    public const string XOR = "xor";
}

接着是判断条件

public class FilterOperator
{
    /// <summary>
    /// 等于，=
    /// </summary>
    public const string EQ = "eq";
    /// <summary>
    /// 不等于，!=
    /// </summary>
    public const string NEQ = "neq";
    /// <summary>
    /// 小于，<
    /// </summary>
    public const string LT = "lt";
    /// <summary>
    /// 小于等于，<=
    /// </summary>
    public const string LTE = "lte";
    /// <summary>
    /// 大于，>
    /// </summary>
    public const string GT = "gt";
    /// <summary>
    /// 大于等于，>=
    /// </summary>
    public const string GTE = "gte";
    /// <summary>
    /// 开始包含
    /// </summary>
    public const string STARTSWITH = "StartsWith";
    /// <summary>
    /// 末尾包含
    /// </summary>
    public const string ENDSWITH = "EndsWith";
    /// <summary>
    /// 包含
    /// </summary>
    public const string CONTAINS = "Contains";
}

抽象查询操作

指定要从哪些字段中，根据关键字模糊查询

public class Search
{
    ///字段名称集合
    public List<string> Fields { get; set; }
    //关键字
    public string? Keyword { get; set; }    
}

通过逻辑运算符和判断条件，针对不同字段组成动态查询条件

public class Filter
{
    public string? Logic { get; set; }

    public IEnumerable<Filter>? Filters { get; set; }

    public string? Field { get; set; }

    public string? Operator { get; set; }

    public object? Value { get; set; }
}

最终过滤条件基类

public abstract class BaseFilter
{
    /// <summary>
    /// 关键字查询
    /// </summary>
    public Search? AdvancedSearch { get; set; }


    /// <summary>
    /// 关键字,当不指定任何字段名称时
    /// </summary>
    public string? Keyword { get; set; }

    /// <summary>
    /// 
    /// </summary>
    public Filter? AdvancedFilter { get; set; }
}

动态表达式

新建类文件SqlSugarExtension.cs，实现基于ISugarQueryable的扩展方法

模糊查询

实现单一字段的模糊查询操作,首先需要通过ToString方法将字段转换为string类型，接着再根据传入的FilterOperator实现string类型对应的StartsWith,EndsWith或Contains方法

private static Expression AddSearchPropertyByKeyword<T>(
      Expression propertyExpr,
        string keyword,
        string operatorSearch = FilterOperator.CONTAINS)
{
    if (propertyExpr is not MemberExpression memberExpr || memberExpr.Member is not PropertyInfo property)
    {
        throw new ArgumentException("propertyExpr must be a property expression.", nameof(propertyExpr));
    }
    
    //关键字
    ConstantExpression constant = Expression.Constant(keyword);

    //string类型的StartsWith,EndsWith或Contains方法
    MethodInfo method = typeof(string).GetMethod(operatorSearch, new Type[] { typeof(string) });

    //call toString转换为string类型
    Expression selectorExpr =
           property.PropertyType == typeof(string)
               ? propertyExpr
               : Expression.Condition(
                   Expression.Equal(Expression.Convert(propertyExpr, typeof(object)), Expression.Constant(null, typeof(object))),
                   Expression.Constant(null, typeof(string)),
                   Expression.Call(propertyExpr, "ToString", null, null));

    return Expression.Call(selectorExpr, method, constant);
}

将属性名称转换为MemberExpression，并对Class.Property(例如Student.Name)格式的名称处理

private static MemberExpression GetPropertyExpression(
        string propertyName,
        ParameterExpression parameter)
{
    Expression propertyExpression = parameter;
    foreach (string member in propertyName.Split('.'))
    {
        propertyExpression = Expression.PropertyOrField(propertyExpression, member);
    }

    return (MemberExpression)propertyExpression;
}

通过Expression.Or连接多个字段的模糊查询表达式，实现关键字的模糊查询扩展方法

public static ISugarQueryable<T> SearchByKeyword<T>(this ISugarQueryable<T> queryable,string keyword)
{
    //不指定模糊查询的字段名称
    return queryable.AdvancedSearch(new Search { Keyword = keyword });
}

public static ISugarQueryable<T> AdvancedSearch<T>(this ISugarQueryable<T> queryable,Search search)
{
    if (!string.IsNullOrWhiteSpace(search?.Keyword))
    {
        var paramExpr = Expression.Parameter(typeof(T));

        Expression right = Expression.Constant(false);

        if (search.Fields?.Any() is true)
        {
            foreach (string field in search.Fields)
            {
                MemberExpression propertyExpr = GetPropertyExpression(field, paramExpr);

                var left = AddSearchPropertyByKeyword<T>(propertyExpr, search.Keyword);

                right = Expression.Or(left, right);
            }
        }
        else
        {
            //未指定模糊查询字段名称时，获取类型的部分属性
            var properties = typeof(T).GetProperties()
                    .Where(prop => (Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType) is { } propertyType
                        && !propertyType.IsEnum
                        && Type.GetTypeCode(propertyType) != TypeCode.Object);

            foreach (var property in properties)
            {
                var propertyExpr = Expression.Property(paramExpr, property);

                var left = AddSearchPropertyByKeyword<T>(propertyExpr, search.Keyword);

                right = Expression.Or(left, right);
            }
        }

        var lambda = Expression.Lambda<Func<T, bool>>(right, paramExpr);

        return queryable.Where(lambda);
    }

    return queryable;
}

按条件筛选过滤

通过传入的FilterLogic创建逻辑运算的表达式

private static Expression CombineFilter(
       string filterOperator,
       Expression bExpresionBase,
       Expression bExpresion)
{
    return filterOperator switch
    {
        FilterLogic.AND => Expression.And(bExpresionBase, bExpresion),
        FilterLogic.OR => Expression.Or(bExpresionBase, bExpresion),
        FilterLogic.XOR => Expression.ExclusiveOr(bExpresionBase, bExpresion),
        _ => throw new ArgumentException("FilterLogic is not valid.", nameof(FilterLogic)),
    };
}

根据属性类型，获取属性值表达式

private static ConstantExpression GeValuetExpression(
      string field,
      object? value,
      Type propertyType)
{
    if (value == null) return Expression.Constant(null, propertyType);

    if (propertyType.IsEnum)
    {
        string? stringEnum = GetStringFromJsonElement(value);

        if (!Enum.TryParse(propertyType, stringEnum, true, out object? valueparsed)) throw new ArgumentException(string.Format("Value {0} is not valid for {1}", value, field));

        return Expression.Constant(valueparsed, propertyType);
    }
    if(propertyType == typeof(long))
    {
        string? stringLong = GetStringFromJsonElement(value);

        if (!long.TryParse(stringLong, out long valueparsed)) throw new ArgumentException(string.Format("Value {0} is not valid for {1}", value, field));

        return Expression.Constant(valueparsed, propertyType);
    }

    if (propertyType == typeof(Guid))
    {
        string? stringGuid = GetStringFromJsonElement(value);

        if (!Guid.TryParse(stringGuid, out Guid valueparsed)) throw new ArgumentException(string.Format("Value {0} is not valid for {1}", value, field));

        return Expression.Constant(valueparsed, propertyType);
    }

    if (propertyType == typeof(string))
    {
        string? text = GetStringFromJsonElement(value);

        return Expression.Constant(text, propertyType);
    }

    if (propertyType == typeof(DateTime) || propertyType == typeof(DateTime?))
    {
        string? text = GetStringFromJsonElement(value);
        return Expression.Constant(ChangeType(text, propertyType), propertyType);
    }

    return Expression.Constant(ChangeType(((JsonElement)value).GetRawText(), propertyType), propertyType);
}

private static dynamic? ChangeType(object value, Type conversion)
{
    var t = conversion;

    if (t.IsGenericType && t.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
    {
        if (value == null)
        {
            return null;
        }

        t = Nullable.GetUnderlyingType(t);
    }

    return Convert.ChangeType(value, t!);
}

private static string GetStringFromJsonElement(object value)
{
    if (value is JsonElement) return ((JsonElement)value).GetString()!;
    if (value is string) return (string)value;
    return value?.ToString();
}

创建过滤条件的表达式

private static Filter GetValidFilter(Filter filter)
{
    if (string.IsNullOrEmpty(filter.Field)) throw new ArgumentException("The field attribute is required when declaring a filter");
    if (string.IsNullOrEmpty(filter.Operator)) throw new ArgumentException("The Operator attribute is required when declaring a filter");
    return filter;
}

private static Expression CreateFilterExpression(
    string logic,
    IEnumerable<Filter> filters,
    ParameterExpression parameter)
{
    Expression filterExpression = default!;

    foreach (var filter in filters)
    {
        Expression bExpresionFilter;

        if (!string.IsNullOrEmpty(filter.Logic))
        {
            if (filter.Filters is null) throw new ArgumentException("The Filters attribute is required when declaring a logic");
            bExpresionFilter = CreateFilterExpression(filter.Logic, filter.Filters, parameter);
        }
        else
        {
            var filterValid = GetValidFilter(filter);
            bExpresionFilter = CreateFilterExpression(filterValid.Field!, filterValid.Operator!, filterValid.Value, parameter);
        }

        filterExpression = filterExpression is null ? bExpresionFilter : CombineFilter(logic, filterExpression, bExpresionFilter);
    }

    return filterExpression;
}

private static Expression CreateFilterExpression(
    string field,
    string filterOperator,
    object? value,
    ParameterExpression parameter)
{
    var propertyExpresion = GetPropertyExpression(field, parameter);
    var valueExpresion = GeValuetExpression(field, value, propertyExpresion.Type);
    return CreateFilterExpression(propertyExpresion, valueExpresion, filterOperator);
}

private static Expression CreateFilterExpression(
    MemberExpression memberExpression,
    ConstantExpression constantExpression,
    string filterOperator)
{
    return filterOperator switch
    {
        FilterOperator.EQ => Expression.Equal(memberExpression, constantExpression),
        FilterOperator.NEQ => Expression.NotEqual(memberExpression, constantExpression),
        FilterOperator.LT => Expression.LessThan(memberExpression, constantExpression),
        FilterOperator.LTE => Expression.LessThanOrEqual(memberExpression, constantExpression),
        FilterOperator.GT => Expression.GreaterThan(memberExpression, constantExpression),
        FilterOperator.GTE => Expression.GreaterThanOrEqual(memberExpression, constantExpression),
        FilterOperator.CONTAINS => Expression.Call(memberExpression, FilterOperator.CONTAINS, null, constantExpression),
        FilterOperator.STARTSWITH => Expression.Call(memberExpression, FilterOperator.STARTSWITH, null, constantExpression),
        FilterOperator.ENDSWITH => Expression.Call(memberExpression, FilterOperator.ENDSWITH, null, constantExpression),
        _ => throw new ArgumentException("Filter Operator is not valid."),
    };
}

实现按条件过滤筛选

public static ISugarQueryable<T> AdvancedFilter<T>(this ISugarQueryable<T> queryable,Filter filter)
{
    if(filter is not null)
    {
        var parameter = Expression.Parameter(typeof(T));

        Expression binaryExpresioFilter;

        if (!string.IsNullOrEmpty(filter.Logic))
        {
            if (filter.Filters is null) throw new ArgumentException("The Filters attribute is required when declaring a logic");
            binaryExpresioFilter = CreateFilterExpression(filter.Logic, filter.Filters, parameter);
        }
        else
        {
            var filterValid = GetValidFilter(filter);
            binaryExpresioFilter = CreateFilterExpression(filterValid.Field!, filterValid.Operator!, filterValid.Value, parameter);
        }

        var lambda = Expression.Lambda<Func<T, bool>>(binaryExpresioFilter, parameter);

        return queryable.Where(lambda);
    }
    return queryable;
}

封装

BaseFilter组合查询封装

public static ISugarQueryable<T> SearchBy<T>(this ISugarQueryable<T> queryable,BaseFilter filter)
{
    return queryable.SearchByKeyword(filter.Keyword)
            .AdvancedSearch(filter.AdvancedSearch)
            .AdvancedFilter(filter.AdvancedFilter);   
}

使用时，业务类可以继承自BaseFilter,Demo如下

public class StudentSearch:BaseFilter { }

public class StudentService
{
    private readonly SqlSugarRepository<Student> _db;

    public StudentService(SqlSugarRepository<Student> db)=>_db = db;

    public Task<List<Student>> Search(StudentSearch search)
    {
        //return _db.AsQueryable().SearchByKeyword<Student>("wang");

        //return _db.AsQueryable().AdvancedSearch<Student>(search.AdvancedSearch);

        //return _db.AsQueryable().AdvancedFilter<Student>(search.AdvancedFilter);

        return _db.AsQueryable().SearchBy<Student>(search);
    }
}