EFCore 动态拼接查询条件(表达式树方式)

  • EFCore 动态拼接查询条件(表达式树方式)已关闭评论
  • 31 次浏览
  • A+
所属分类:.NET技术
摘要

扩展方法名:Filter支持参数:实体类、JObject扩展代码:用法:var list=db.tableA.Filter(obj).ToList();

扩展方法名:Filter

支持参数:实体类、JObject

扩展代码:

EFCore 动态拼接查询条件(表达式树方式)EFCore 动态拼接查询条件(表达式树方式)

//白色风车   public static class EntityFrameworkCoreExtensions     {         private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection connection, params object[] parameters)         {             var conn = facade.GetDbConnection();             connection = conn;             if (conn.State != ConnectionState.Open)             {                 conn.Open();             }             var cmd = conn.CreateCommand();             cmd.CommandText = sql;             cmd.Parameters.AddRange(parameters);             return cmd;         }          public static DataTable SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters)         {             var command = CreateCommand(facade, sql, out DbConnection conn, parameters);              var reader = command.ExecuteReader();             var dt = new DataTable();             dt.Load(reader);             reader.Close();             conn.Close();             return dt;         }          public static List<T> SqlQuery<T>(this DatabaseFacade facade, string sql, params object[] parameters) where T : class, new()         {             var dt = SqlQuery(facade, sql, parameters);             return dt.ToList<T>();         }          public static List<T> ToList<T>(this DataTable dt) where T : class, new()         {             var propertyInfos = typeof(T).GetProperties();             var list = new List<T>();             foreach (DataRow row in dt.Rows)             {                 var t = new T();                 foreach (PropertyInfo p in propertyInfos)                 {                     if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)                         p.SetValue(t, row[p.Name], null);                 }                 list.Add(t);             }             return list;         }          //public static List<T> DTToList<T>(this DataTable dt)         //{         //    var dataColumn = dt.Columns.Cast<DataColumn>().Select(c => c.ColumnName).ToList();          //    var properties = typeof(T).GetProperties();         //    string columnName = string.Empty;          //    return dt.AsEnumerable().Select(row =>         //    {         //        var t = Activator.CreateInstance<T>();         //        foreach (var p in properties)         //        {         //            columnName = p.Name;         //            if (dataColumn.Contains(columnName))         //            {         //                if (!p.CanWrite)         //                    continue;          //                object value = row[columnName];         //                Type type = p.PropertyType;          //                if (value != DBNull.Value)         //                {         //                    p.SetValue(t, Convert.ChangeType(value, type), null);         //                }         //            }         //        }         //        return t;         //    }).ToList();         //}         public static DataTable ToDataTable<T>(this List<T> items)         {             DataTable dataTable = new DataTable();              PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);             foreach (PropertyInfo prop in Props)             {                 dataTable.Columns.Add(prop.Name, prop.PropertyType);             }              foreach (T obj in items)             {                 var values = new object[Props.Length];                 for (int i = 0; i < Props.Length; i++)                 {                     values[i] = Props[i].GetValue(obj, null);                 }                 dataTable.Rows.Add(values);             }              return dataTable;         }          /// <summary>         /// WhereIf扩展         /// </summary>         /// <typeparam name="T"></typeparam>         /// <param name="query"></param>         /// <param name="condition"></param>         /// <param name="predicate"></param>         /// <returns></returns>         public static IQueryable<T> WhereIf<T>(this IQueryable<T> query, bool condition, Expression<Func<T, bool>> predicate)         {             return condition ? query.Where(predicate) : query;         }         /// <summary>         /// WhereIf扩展         /// </summary>         /// <typeparam name="T"></typeparam>         /// <param name="query"></param>         /// <param name="condition"></param>         /// <param name="predicate"></param>         /// <returns></returns>         public static IQueryable<T> WhereIf<T>(this IQueryable<T> query, bool condition, Expression<Func<T, int, bool>> predicate)         {             return condition ? query.Where(predicate) : query;         }         /// <summary>         /// WhereIf扩展         /// </summary>         /// <typeparam name="T"></typeparam>         /// <param name="query"></param>         /// <param name="condition"></param>         /// <param name="predicate"></param>         /// <returns></returns>         public static IEnumerable<T> WhereIf<T>(this IEnumerable<T> query, bool condition, Func<T, bool> predicate)         {             return condition ? query.Where(predicate) : query;         }         /// <summary>         /// 条件筛选过滤  (字段的名称、类型一致)         /// </summary>         /// <typeparam name="T">源数据</typeparam>         /// <typeparam name="R">筛选参数实体</typeparam>         /// <param name="query">属性标识[JsonIgnore] 设置该属性不作筛选字段</param>         /// <param name="condition">属性标识[JsonIgnore] 设置该属性不作筛选字段</param>         /// <returns></returns>         public static IQueryable<T> Filter<T, R>(this IQueryable<T> query, R condition) where R : new()         {             //参考 https://www.cnblogs.com/ma-nong01/p/14323430.html https://www.cnblogs.com/doudouzi/p/11897731.html              var dbsel = typeof(T).GetProperties().Where(p => p.CanWrite && !p.CustomAttributes.Any(x => x.AttributeType == typeof(JsonIgnoreAttribute) || x.AttributeType == typeof(NotMappedAttribute))).ToList();             var con = condition.GetType().GetProperties().Where(p => p.CanWrite && !p.CustomAttributes.Any(x => x.AttributeType == typeof(JsonIgnoreAttribute) || x.AttributeType == typeof(NotMappedAttribute))).ToList();              List<MethodCallExpression> mcList = new List<MethodCallExpression>();             List<BinaryExpression> mcList2 = new List<BinaryExpression>();             ParameterExpression parameterExpression = Expression.Parameter(typeof(T), "x");              List<Expression> listexp = new List<Expression>();              foreach (var item in dbsel)             {                 foreach (var p in con)                 {                     var name = p.Name;                     if (name.ToLower() == item.Name.ToLower())                     {                         var type = p.PropertyType;                         var val = p.GetValue(condition, null);                          if (val != null)                         {                             //字符串不为空                             if (!(type.Name == "String" && string.IsNullOrEmpty(val.ToString())))                             {                                 //传入的是数组                                 if (type.Name == "List`1")                                 {                                     //泛型里的类型与筛选值的类型一致                                     if (type.GetGenericArguments()?.FirstOrDefault() == item.PropertyType && ((ICollection)val).Count > 0)                                     {                                         //参考 https://www.likecs.com/ask-4358604.html#sc=2800                                          var methodInfo = type.GetMethod("Contains", new Type[] { item.PropertyType });                                          var list = Expression.Constant(val);                                          //var param = Expression.Parameter(typeof(T), "j");                                         var value = Expression.Property(parameterExpression, item);                                          var body = Expression.Call(list, methodInfo, value);                                          listexp.Add(body);                                     }                                 }                                 //类型一致                                 else if (item.PropertyType == type)                                 {                                     MemberExpression proerty = Expression.Property(parameterExpression, item);                                     ConstantExpression constantExpression = Expression.Constant(val, item.PropertyType);                                     if (item.PropertyType.Name == "String")                                     {                                         listexp.Add(Expression.Call(proerty, typeof(string).GetMethod("Contains", new Type[] { item.PropertyType }), new Expression[] { constantExpression }));                                     }                                     //else if (item.PropertyType.Name == "Boolean")                                     //{                                     //    listexp.Add(Expression.Call(proerty, typeof(bool).GetMethod("Equals", new Type[] { typeof(bool) }), new Expression[] { constantExpression }));                                     //}                                     //else if (item.PropertyType.Name == "Int32" /*&& !val.ToString().Equals("0")*/)                                     //{                                     //    listexp.Add(Expression.Call(proerty, typeof(int).GetMethod("Equals", new Type[] { typeof(int) }), new Expression[] { constantExpression }));                                     //}                                     else if (item.PropertyType.Name == "DateTime")                                     {                                         if (DateTime.TryParse(val?.ToString(), out DateTime parsedDate))                                         {                                             var constant = Expression.Constant(parsedDate.Date);                                             var property = Expression.Property(proerty, "Date");                                             var exp1 = Expression.Equal(property, constant);                                             listexp.Add(exp1);                                         }                                     }                                     //else if (item.PropertyType.Name == "Decimal")                                     //{                                     //    listexp.Add(Expression.Call(proerty, typeof(decimal).GetMethod("Equals", new Type[] { typeof(decimal) }), new Expression[] { constantExpression }));                                     //}                                     //else if (item.PropertyType.Name != "Int32" && item.PropertyType.Name != "ICollection`1" && item.PropertyType.Name != "Nullable`1")                                     //{                                     //    listexp.Add(Expression.Call(proerty, typeof(string).GetMethod("Contains", new Type[] { item.PropertyType }), new Expression[] { constantExpression }));                                     //}                                     else if (item.PropertyType.Name == "ICollection`1")                                     {                                      }                                     else if (item.PropertyType.Name == "Nullable`1"/* && !val.ToString().Equals("0")*/)                                     {                                         if (type.GetGenericArguments()?.FirstOrDefault() == typeof(DateTime))                                         {                                             if (DateTime.TryParse(val?.ToString(), out DateTime parsedDate))                                             {                                                 var constant = Expression.Constant(parsedDate.Date);                                                  var property = Expression.Property(proerty, "Value");                                                 property = Expression.Property(property, "Date");                                                 var exp1 = Expression.Equal(property, constant);                                                 listexp.Add(exp1);                                             }                                         }                                         else                                         {                                             listexp.Add(Expression.Equal(proerty, constantExpression));                                         }                                     }                                     else                                     {                                         listexp.Add(Expression.Equal(proerty, constantExpression));                                     }                                 }                                 break;                             }                         }                     }                 }             }              Expression<Func<T, bool>> exp = Expression.Lambda<Func<T, bool>>(MethodCall(listexp), new ParameterExpression[] { parameterExpression });              return exp != null ? query.Where(exp) : query;         }         /// <summary>         /// 条件筛选过滤         /// </summary>         /// <typeparam name="T">源数据</typeparam>         /// <param name="query">属性标识[JsonIgnore] 设置该属性不作筛选字段</param>         /// <param name="Jobj">JObject 筛选参数</param>         /// <returns></returns>         public static IQueryable<T> Filter<T>(this IQueryable<T> query, JObject Jobj) where T : new()         {             var condition = Jobj.ToObject<T>();             return query.Filter(condition);         }         /// <summary>         /// 递归拼接 条件         /// </summary>         /// <param name="conditions"></param>         /// <returns></returns>         private static Expression MethodCall(IEnumerable<Expression> conditions)         {             if (conditions == null || conditions.Count() == 0)             {                 return Expression.Constant(true, typeof(bool));             }             else if (conditions.Count() == 1)             {                 return conditions.First();             }             else             {                 Expression left = MethodCall(conditions.Take(1).ToList());                 Expression right = MethodCall(conditions.Skip(1).ToList());                 return Expression.AndAlso(left, right);             }         }     }

View Code

用法:

var list=db.tableA.Filter(obj).ToList();