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

许媛媛 · 发表于 2023-1-2 18:18:48

扩展方法名：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);
}
}
}

复制代码

View Code用法：
var list=db.tableA.Filter(obj).ToList();

出处：[url=https://www.cnblogs.com/zszh/p/17012510.html]https://www.cnblogs.com/zszh/p/17012510.html[/url]
来源:https://www.cnblogs.com/zszh/archive/2022/12/29/17012510.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】我们会及时删除侵权内容，谢谢合作！

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

本帖子中包含更多资源