基於領域驅動設計(DDD)超輕量級快速開發架構(二)動態linq查詢的實現方式

基於領域驅動設計(DDD)超輕量級快速開發架構(二)動態linq查詢的實現方式

-之動態查詢,查詢邏輯封裝復用

基於領域驅動設計(DDD)超輕量級快速開發架構詳細介紹請看

https://www.cnblogs.com/neozhu/p/13174234.html

需求

  1. 配合EasyUI datagird filter實現多字段(任意字段)的篩選
  2. 根據業務需求篩選特定的狀態或條件,如:查看結案的訂單,最近30天的訂單,查看屬於我的訂單.等等,這些邏輯是固定也是可以被重用,但又不想每次寫相同的條件,那麼下面我會給我的解決方案.

需求1隻是一個偷懶的實現方式,因為datagrid自帶這個功能,但又不想根據具體的需求來畫查詢條件,如果需求必須要再datagrid上面做一塊查詢條件的輸入那目前只能在前端自己手工添加,在組織後傳入後台,暫時不在這裏討論

需求2可能不太好解釋,看完代碼就自然理解為什麼要這麼做了,這麼做的好處有哪些

具體實現的方式

 

 默認情況下 datagrid 有幾列就可以對這幾列進行篩選,對於日期型的字段會採用between,選擇2個時間之間進行篩選,数字類型會提供大於小於等符號選擇,可以自行嘗試,其原理是datagrid 會根據datagrid 頭部輸入的值生成一個Json字符串發送後台請求數據

JSON:格式

filterRules: [
{field:field,op:op,value:value},
{field:field,op:op,value:value},
] 
  • 通常的做法是一個一個判斷加條件
  1 var filters = JsonConvert.DeserializeObject<IEnumerable<filterRule>>(filterRules); 
  2 foreach (var rule in filters)
  3         {
  4           if (rule.field == "Id" && !string.IsNullOrEmpty(rule.value) && rule.value.IsInt())
  5           {
  6             var val = Convert.ToInt32(rule.value);
  7             switch (rule.op)
  8             {
  9               case "equal":
 10                 this.And(x => x.Id == val);
 11                 break;
 12               case "notequal":
 13                 this.And(x => x.Id != val);
 14                 break;
 15               case "less":
 16                 this.And(x => x.Id < val);
 17                 break;
 18               case "lessorequal":
 19                 this.And(x => x.Id <= val);
 20                 break;
 21               case "greater":
 22                 this.And(x => x.Id > val);
 23                 break;
 24               case "greaterorequal":
 25                 this.And(x => x.Id >= val);
 26                 break;
 27               default:
 28                 this.And(x => x.Id == val);
 29                 break;
 30             }
 31           }
 32           if (rule.field == "Name" && !string.IsNullOrEmpty(rule.value))
 33           {
 34             this.And(x => x.Name.Contains(rule.value));
 35           }
 36           if (rule.field == "Code" && !string.IsNullOrEmpty(rule.value))
 37           {
 38             this.And(x => x.Code.Contains(rule.value));
 39           }
 40 
 41           if (rule.field == "Address" && !string.IsNullOrEmpty(rule.value))
 42           {
 43             this.And(x => x.Address.Contains(rule.value));
 44           }
 45 
 46           if (rule.field == "Contect" && !string.IsNullOrEmpty(rule.value))
 47           {
 48             this.And(x => x.Contect.Contains(rule.value));
 49           }
 50 
 51           if (rule.field == "PhoneNumber" && !string.IsNullOrEmpty(rule.value))
 52           {
 53             this.And(x => x.PhoneNumber.Contains(rule.value));
 54           }
 55 
 56           if (rule.field == "RegisterDate" && !string.IsNullOrEmpty(rule.value))
 57           {
 58             if (rule.op == "between")
 59             {
 60               var datearray = rule.value.Split(new char[] { '-' });
 61               var start = Convert.ToDateTime(datearray[0]);
 62               var end = Convert.ToDateTime(datearray[1]);
 63 
 64               this.And(x => SqlFunctions.DateDiff("d", start, x.RegisterDate) >= 0);
 65               this.And(x => SqlFunctions.DateDiff("d", end, x.RegisterDate) <= 0);
 66             }
 67           }
 68           if (rule.field == "CreatedDate" && !string.IsNullOrEmpty(rule.value))
 69           {
 70             if (rule.op == "between")
 71             {
 72               var datearray = rule.value.Split(new char[] { '-' });
 73               var start = Convert.ToDateTime(datearray[0]);
 74               var end = Convert.ToDateTime(datearray[1]);
 75 
 76               this.And(x => SqlFunctions.DateDiff("d", start, x.CreatedDate) >= 0);
 77               this.And(x => SqlFunctions.DateDiff("d", end, x.CreatedDate) <= 0);
 78             }
 79           }
 80 
 81 
 82           if (rule.field == "CreatedBy" && !string.IsNullOrEmpty(rule.value))
 83           {
 84             this.And(x => x.CreatedBy.Contains(rule.value));
 85           }
 86 
 87          if (rule.field == "LastModifiedDate" && !string.IsNullOrEmpty(rule.value))
 88           {
 89             if (rule.op == "between")
 90             {
 91               var datearray = rule.value.Split(new char[] { '-' });
 92               var start = Convert.ToDateTime(datearray[0]);
 93               var end = Convert.ToDateTime(datearray[1]);
 94 
 95               this.And(x => SqlFunctions.DateDiff("d", start, x.LastModifiedDate) >= 0);
 96               this.And(x => SqlFunctions.DateDiff("d", end, x.LastModifiedDate) <= 0);
 97             }
 98           }
 99 
100           if (rule.field == "LastModifiedBy" && !string.IsNullOrEmpty(rule.value))
101           {
102             this.And(x => x.LastModifiedBy.Contains(rule.value));
103           }
104 
105         }

View Code

  • 新的做法是動態根據field,op,value生成一個linq 表達式,不用再做繁瑣的判斷,這塊代碼也可以被其它項目使用,非常好用
namespace SmartAdmin
{
 
  public static class PredicateBuilder
  {

    public static Expression<Func<T, bool>> FromFilter<T>(string filtergroup) {
      Expression<Func<T, bool>> any = x => true;
      if (!string.IsNullOrEmpty(filtergroup))
      {
        var filters = JsonSerializer.Deserialize<filter[]>(filtergroup);

          foreach (var filter in filters)
          {
            if (Enum.TryParse(filter.op, out OperationExpression op) && !string.IsNullOrEmpty(filter.value))
            {
              var expression = GetCriteriaWhere<T>(filter.field, op, filter.value);
              any = any.And(expression);
            }
          }
      }

      return any;
    }

    #region -- Public methods --
    public static Expression<Func<T, bool>> GetCriteriaWhere<T>(Expression<Func<T, object>> e, OperationExpression selectedOperator, object fieldValue)
    {
      var name = GetOperand<T>(e);
      return GetCriteriaWhere<T>(name, selectedOperator, fieldValue);
    }

    public static Expression<Func<T, bool>> GetCriteriaWhere<T, T2>(Expression<Func<T, object>> e, OperationExpression selectedOperator, object fieldValue)
    {
      var name = GetOperand<T>(e);
      return GetCriteriaWhere<T, T2>(name, selectedOperator, fieldValue);
    }

    public static Expression<Func<T, bool>> GetCriteriaWhere<T>(string fieldName, OperationExpression selectedOperator, object fieldValue)
    {
      var props = TypeDescriptor.GetProperties(typeof(T));
      var prop = GetProperty(props, fieldName, true);
      var parameter = Expression.Parameter(typeof(T));
      var expressionParameter = GetMemberExpression<T>(parameter, fieldName);
      if (prop != null && fieldValue != null)
      {
       
        BinaryExpression body = null;
        switch (selectedOperator)
        {
          case OperationExpression.equal:
            body = Expression.Equal(expressionParameter, Expression.Constant(Convert.ChangeType(fieldValue, Nullable.GetUnderlyingType(prop.PropertyType)?? prop.PropertyType), prop.PropertyType));
            return Expression.Lambda<Func<T, bool>>(body, parameter);
          case OperationExpression.notequal:
            body = Expression.NotEqual(expressionParameter, Expression.Constant(Convert.ChangeType(fieldValue, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType), prop.PropertyType));
            return Expression.Lambda<Func<T, bool>>(body, parameter);
          case OperationExpression.less:
            body = Expression.LessThan(expressionParameter, Expression.Constant(Convert.ChangeType(fieldValue, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType), prop.PropertyType));
            return Expression.Lambda<Func<T, bool>>(body, parameter);
          case OperationExpression.lessorequal:
            body = Expression.LessThanOrEqual(expressionParameter, Expression.Constant(Convert.ChangeType(fieldValue, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType), prop.PropertyType));
            return Expression.Lambda<Func<T, bool>>(body, parameter);
          case OperationExpression.greater:
            body = Expression.GreaterThan(expressionParameter, Expression.Constant(Convert.ChangeType(fieldValue, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType), prop.PropertyType));
            return Expression.Lambda<Func<T, bool>>(body, parameter);
          case OperationExpression.greaterorequal:
            body = Expression.GreaterThanOrEqual(expressionParameter, Expression.Constant(Convert.ChangeType(fieldValue, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType), prop.PropertyType));
            return Expression.Lambda<Func<T, bool>>(body, parameter);
          case OperationExpression.contains:
            var contains = typeof(string).GetMethod("Contains", new[] { typeof(string) });
            var bodyLike = Expression.Call(expressionParameter, contains, Expression.Constant(Convert.ChangeType(fieldValue, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType), prop.PropertyType));
            return Expression.Lambda<Func<T, bool>>(bodyLike, parameter);
          case OperationExpression.endwith:
            var endswith = typeof(string).GetMethod("EndsWith",new[] { typeof(string) });
            var bodyendwith = Expression.Call(expressionParameter, endswith, Expression.Constant(Convert.ChangeType(fieldValue, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType), prop.PropertyType));
            return Expression.Lambda<Func<T, bool>>(bodyendwith, parameter);
          case OperationExpression.beginwith:
            var startswith = typeof(string).GetMethod("StartsWith", new[] { typeof(string) });
            var bodystartswith = Expression.Call(expressionParameter, startswith, Expression.Constant(Convert.ChangeType(fieldValue, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType), prop.PropertyType));
            return Expression.Lambda<Func<T, bool>>(bodystartswith, parameter);
          case OperationExpression.includes:
            return Includes<T>(fieldValue, parameter, expressionParameter, prop.PropertyType);
          case OperationExpression.between:
            return Between<T>(fieldValue, parameter, expressionParameter, prop.PropertyType);
          default:
            throw new Exception("Not implement Operation");
        }
      }
      else
      {
        Expression<Func<T, bool>> filter = x => true;
        return filter;
      }
    }

    public static Expression<Func<T, bool>> GetCriteriaWhere<T, T2>(string fieldName, OperationExpression selectedOperator, object fieldValue)
    {


      var props = TypeDescriptor.GetProperties(typeof(T));
      var prop = GetProperty(props, fieldName, true);

      var parameter = Expression.Parameter(typeof(T));
      var expressionParameter = GetMemberExpression<T>(parameter, fieldName);

      if (prop != null && fieldValue != null)
      {
        switch (selectedOperator)
        {
          case OperationExpression.any:
            return Any<T, T2>(fieldValue, parameter, expressionParameter);

          default:
            throw new Exception("Not implement Operation");
        }
      }
      else
      {
        Expression<Func<T, bool>> filter = x => true;
        return filter;
      }
    }



    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr, Expression<Func<T, bool>> or)
    {
      if (expr == null)
      {
        return or;
      }

      return Expression.Lambda<Func<T, bool>>(Expression.OrElse(new SwapVisitor(expr.Parameters[0], or.Parameters[0]).Visit(expr.Body), or.Body), or.Parameters);
    }

    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr, Expression<Func<T, bool>> and)
    {
      if (expr == null)
      {
        return and;
      }

      return Expression.Lambda<Func<T, bool>>(Expression.AndAlso(new SwapVisitor(expr.Parameters[0], and.Parameters[0]).Visit(expr.Body), and.Body), and.Parameters);
    }

    #endregion
    #region -- Private methods --

    private static string GetOperand<T>(Expression<Func<T, object>> exp)
    {
      if (!( exp.Body is MemberExpression body ))
      {
        var ubody = (UnaryExpression)exp.Body;
        body = ubody.Operand as MemberExpression;
      }

      var operand = body.ToString();

      return operand.Substring(2);

    }

    private static MemberExpression GetMemberExpression<T>(ParameterExpression parameter, string propName)
    {
      if (string.IsNullOrEmpty(propName))
      {
        return null;
      }

      var propertiesName = propName.Split('.');
      if (propertiesName.Count() == 2)
      {
        return Expression.Property(Expression.Property(parameter, propertiesName[0]), propertiesName[1]);
      }

      return Expression.Property(parameter, propName);
    }

    private static Expression<Func<T, bool>> Includes<T>(object fieldValue, ParameterExpression parameterExpression, MemberExpression memberExpression ,Type type)
    {
      var safetype= Nullable.GetUnderlyingType(type) ?? type;

      switch (safetype.Name.ToLower())
      {
        case  "string":
          var strlist = (IEnumerable<string>)fieldValue;
          if (strlist == null || strlist.Count() == 0)
          {
            return x => true;
          }
          var strmethod = typeof(List<string>).GetMethod("Contains", new Type[] { typeof(string) });
          var strcallexp = Expression.Call(Expression.Constant(strlist.ToList()), strmethod, memberExpression);
          return Expression.Lambda<Func<T, bool>>(strcallexp, parameterExpression);
        case "int32":
          var intlist = (IEnumerable<int>)fieldValue;
          if (intlist == null || intlist.Count() == 0)
          {
            return x => true;
          }
          var intmethod = typeof(List<int>).GetMethod("Contains", new Type[] { typeof(int) });
          var intcallexp = Expression.Call(Expression.Constant(intlist.ToList()), intmethod, memberExpression);
          return Expression.Lambda<Func<T, bool>>(intcallexp, parameterExpression);
        case "float":
          var floatlist = (IEnumerable<float>)fieldValue;
          if (floatlist == null || floatlist.Count() == 0)
          {
            return x => true;
          }
          var floatmethod = typeof(List<int>).GetMethod("Contains", new Type[] { typeof(int) });
          var floatcallexp = Expression.Call(Expression.Constant(floatlist.ToList()), floatmethod, memberExpression);
          return Expression.Lambda<Func<T, bool>>(floatcallexp, parameterExpression);
        default:
          return x => true;
      }
      
    }
    private static Expression<Func<T, bool>> Between<T>(object fieldValue, ParameterExpression parameterExpression, MemberExpression memberExpression, Type type)
    {
      
      var safetype = Nullable.GetUnderlyingType(type) ?? type;
      switch (safetype.Name.ToLower())
      {
        case "datetime":
          var datearray = ( (string)fieldValue ).Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries);
          var start = Convert.ToDateTime(datearray[0] + " 00:00:00", CultureInfo.CurrentCulture);
          var end = Convert.ToDateTime(datearray[1] + " 23:59:59", CultureInfo.CurrentCulture);
          var greater = Expression.GreaterThan(memberExpression, Expression.Constant(start, type));
          var less = Expression.LessThan(memberExpression, Expression.Constant(end, type));
          return Expression.Lambda<Func<T, bool>>(greater, parameterExpression)
            .And(Expression.Lambda<Func<T, bool>>(less, parameterExpression));
        case "int":
        case "int32":
          var intarray = ( (string)fieldValue ).Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries);
          var min = Convert.ToInt32(intarray[0] , CultureInfo.CurrentCulture);
          var max = Convert.ToInt32(intarray[1], CultureInfo.CurrentCulture);
          var maxthen = Expression.GreaterThan(memberExpression, Expression.Constant(min, type));
          var minthen = Expression.LessThan(memberExpression, Expression.Constant(max, type));
          return Expression.Lambda<Func<T, bool>>(maxthen, parameterExpression)
            .And(Expression.Lambda<Func<T, bool>>(minthen, parameterExpression));
        case "decimal":
          var decarray = ( (string)fieldValue ).Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries);
          var dmin = Convert.ToDecimal(decarray[0], CultureInfo.CurrentCulture);
          var dmax = Convert.ToDecimal(decarray[1], CultureInfo.CurrentCulture);
          var dmaxthen = Expression.GreaterThan(memberExpression, Expression.Constant(dmin, type));
          var dminthen = Expression.LessThan(memberExpression, Expression.Constant(dmax, type));
          return Expression.Lambda<Func<T, bool>>(dmaxthen, parameterExpression)
            .And(Expression.Lambda<Func<T, bool>>(dminthen, parameterExpression));
        case "float":
          var farray = ((string)fieldValue).Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries);
          var fmin = Convert.ToDecimal(farray[0], CultureInfo.CurrentCulture);
          var fmax = Convert.ToDecimal(farray[1], CultureInfo.CurrentCulture);
          var fmaxthen = Expression.GreaterThan(memberExpression, Expression.Constant(fmin, type));
          var fminthen = Expression.LessThan(memberExpression, Expression.Constant(fmax, type));
          return Expression.Lambda<Func<T, bool>>(fmaxthen, parameterExpression)
            .And(Expression.Lambda<Func<T, bool>>(fminthen, parameterExpression));
        case "string":
          var strarray = ( (string)fieldValue ).Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries);
          var smin = strarray[0];
          var smax = strarray[1];
        
          var strmethod = typeof(string).GetMethod("Contains");
          var mm = Expression.Call(memberExpression, strmethod, Expression.Constant(smin, type));
          var nn = Expression.Call(memberExpression, strmethod, Expression.Constant(smax, type));


          return Expression.Lambda<Func<T, bool>>(mm, parameterExpression)
            .Or(Expression.Lambda<Func<T, bool>>(nn, parameterExpression));
        default:
          return x => true;
      }

    }



    private static Expression<Func<T, bool>> Any<T, T2>(object fieldValue, ParameterExpression parameterExpression, MemberExpression memberExpression)
    {
      var lambda = (Expression<Func<T2, bool>>)fieldValue;
      var anyMethod = typeof(Enumerable).GetMethods(BindingFlags.Static | BindingFlags.Public)
      .First(m => m.Name == "Any" && m.GetParameters().Count() == 2).MakeGenericMethod(typeof(T2));

      var body = Expression.Call(anyMethod, memberExpression, lambda);

      return Expression.Lambda<Func<T, bool>>(body, parameterExpression);
    }

    private static PropertyDescriptor GetProperty(PropertyDescriptorCollection props, string fieldName, bool ignoreCase)
    {
      if (!fieldName.Contains('.'))
      {
        return props.Find(fieldName, ignoreCase);
      }

      var fieldNameProperty = fieldName.Split('.');
      return props.Find(fieldNameProperty[0], ignoreCase).GetChildProperties().Find(fieldNameProperty[1], ignoreCase);

    }
    #endregion
  }

  internal class SwapVisitor : ExpressionVisitor
  {
    private readonly Expression from, to;
    public SwapVisitor(Expression from, Expression to)
    {
      this.from = from;
      this.to = to;
    }
    public override Expression Visit(Expression node) => node == from ? to : base.Visit(node);
  }
  public enum OperationExpression
  {
    equal,
    notequal,
    less,
    lessorequal,
    greater,
    greaterorequal,
    contains,
    beginwith,
    endwith,
    includes,
    between,
    any
  }
}

View Code

 1 public async Task<JsonResult> GetData(int page = 1, int rows = 10, string sort = "Id", string order = "asc", string filterRules = "")
 2     {
 3       try
 4       {
 5         var filters = PredicateBuilder.FromFilter<Company>(filterRules);
 6         var total = await this.companyService
 7                              .Query(filters)
 8                              .AsNoTracking()
 9                              .CountAsync()
10                               ;
11         var pagerows = (await this.companyService
12                              .Query(filters)
13                               .AsNoTracking()
14                            .OrderBy(n => n.OrderBy(sort, order))
15                            .Skip(page - 1).Take(rows)
16                            .SelectAsync())
17                            .Select(n => new
18                            {
19                              Id = n.Id,
20                              Name = n.Name,
21                              Code = n.Code,
22                              Address = n.Address,
23                              Contect = n.Contect,
24                              PhoneNumber = n.PhoneNumber,
25                              RegisterDate = n.RegisterDate.ToString("yyyy-MM-dd HH:mm:ss")
26                            }).ToList();
27         var pagelist = new { total = total, rows = pagerows };
28         return Json(pagelist);
29       }
30       catch(Exception e) {
31         throw e;
32         }
33 
34     }

配合使用的代碼

  • 對於固定查詢邏輯的封裝和復用,當然除了復用還可以明顯的提高代碼的可讀性.
public class OrderSalesQuery : QueryObject<Order>
{
    public decimal Amount { get; set; }
    public string Country { get; set; }
    public DateTime FromDate { get; set; }
    public DateTime ToDate { get; set; }

    public override Expression<Func<Order, bool>> Query()
    {
        return (x => 
            x.OrderDetails.Sum(y => y.UnitPrice) > Amount &&
            x.OrderDate >= FromDate &&
            x.OrderDate <= ToDate &&
            x.ShipCountry == Country);
    }
}

查看訂單的銷售情況,條件 金額,國家,日期

var orderRepository = new Repository<Order>(this);
    
var orders = orderRepository
    .Query(new OrderSalesQuery(){ 
        Amount = 100, 
        Country = "USA",
        FromDate = DateTime.Parse("01/01/1996"), 
        ToDate = DateTime.Parse("12/31/1996" )
    })
    .Select();

調用查詢方法

public class CustomerLogisticsQuery : QueryObject<Customer>
{
    public CustomerLogisticsQuery FromCountry(string country)
    {
        Add(x => x.Country == country);
        return this;
    }

    public CustomerLogisticsQuery LivesInCity(string city)
    {   
        Add(x => x.City == city);
        return this;
    }
}

客戶查詢 根據國家和城市查詢

public class CustomerSalesQuery : QueryObject<Customer>
{
    public CustomerSalesQuery WithPurchasesMoreThan(decimal amount)
    {
        Add(x => x.Orders
            .SelectMany(y => y.OrderDetails)
            .Sum(z => z.UnitPrice * z.Quantity) > amount);

        return this;
    }

    public CustomerSalesQuery WithQuantitiesMoreThan(decimal quantity)
    {
        Add(x => x.Orders
            .SelectMany(y => y.OrderDetails)
            .Sum(z => z.Quantity) > quantity);

        return this;
    }
}

客戶的銷售情況,金額和數量

var customerRepository = new Repository<Customer>(this);

var query1 = new CustomerLogisticsQuery()
    .LivesInCity("London");

var query2 = new CustomerSalesQuery()
    .WithPurchasesMoreThan(100)
    .WithQuantitiesMoreThan(10);

customerRepository
    .Query(query1.And(query2))
    .Select()
    .Dump();

復用上面的定義的查詢方法

以上這些都是改項目提供的方法,非常的好用

 

本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】

網頁設計一頭霧水該從何著手呢? 台北網頁設計公司幫您輕鬆架站!

網頁設計公司推薦不同的風格,搶佔消費者視覺第一線

※Google地圖已可更新顯示潭子電動車充電站設置地點!!

※廣告預算用在刀口上,台北網頁設計公司幫您達到更多曝光效益

※別再煩惱如何寫文案,掌握八大原則!

網頁設計最專業,超強功能平台可客製化