EFCore 动态拼接查询条件(表达式树方式)
扩展方法名：Filter
支持参数：实体类、JObject
扩展代码：
//白色风车 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); } } }
用法：
var list=db.tableA.Filter(obj).ToList();
