前言：
EFCore是.net目前使用量最广，社区活跃度最高的一款数据库ORM框架，是我们.net 程序员开发的利器。在我们日常编程中，不可避免就是可选参数查询，efcore的IQueryable 内部传参的Lamba Expression 往往不能够根据条件动态拼接，或手写SQL做法通常是通过StringBuilder判断条件拼接SQL，我们既不想写SQL，又想动态拼接查询条件，那可以使用今天的主角 Expression.
原理：
1.注解方式在请求参数属性上标注condition，如Equal, NotEqual, Include, Exclude等
2. 反射拿到请求参数，以及数据库实体，找到Mapping的column和request param name ，然后根据condition 组装Expression.
以前写法如下图所示：我们where接受一个expression,常见写法是指定数据库实体的某一列的值等于我请求参数的值，但是会遇到一些问题，太多的if -else 去判断是否为空。
代码实现：
1.定义Attribute以及Condition Enum.
[AttributeUsage(AttributeTargets.Property)]
public class ExpressionAlias : Attribute
{
public string EntityName { get; init; }
public ConditionType Condition { get; init; }
public bool IsIgnore { get; init; }
public ExpressionAlias(string entityName, bool isIgnore =false, ConditionType condition = ConditionType.Equal)
{
EntityName = entityName;
IsIgnore = isIgnore;
Condition = condition;
}
}
public enum ConditionType
{
/// <summary>
/// ==
/// </summary>
Equal,
/// !=
NotEqual,
/// <summary>
/// like, in
/// </summary>
Include,
/// <summary>
/// not like, not in
/// </summary>
Exclude
}
2. 构建表达式：
public static class BuildParameterHelper
{
/// <summary>
/// 构建请求为表达式，默认设置名字相同
/// 1.对应的property name 设置Expression
/// 2.返回这个expression 给efcore 查询
/// </summary>
/// <param name="t"></param>
/// <typeparam name="T"></typeparam>
/// <typeparam name="V"></typeparam>
/// <returns></returns>
public static Expression<Func<V, bool>> BuildExpression<T, V>(ref T t)
{
Expression<Func<V, bool>> expression = ExpressionExtension.True<V>();
//request实体
var propertyInfos = typeof(T).GetProperties();
//数据库实体
var entityDic = typeof(V).GetProperties().ToDictionary(p => p.Name.ToUpper(), p => p);
if (!propertyInfos.Any())
return expression;
foreach (var propertyInfo in propertyInfos)
{
var value = propertyInfo.GetValue(t);
var eA = (ExpressionAlias)Attribute.GetCustomAttribute(propertyInfo, typeof(ExpressionAlias)) ??
new ExpressionAlias(propertyInfo.Name);
//判断是否为可空类型,
if (!propertyInfo.PropertyType.IsNullableType())
{
//不是可空类型
//if忽略就自动略过
if (eA is { IsIgnore: true })
continue;
//设置别名
if (entityDic.TryGetValue(eA.EntityName.ToUpper(), out var vPropertyInfo))
{
switch (eA.Condition)
{
case ConditionType.Equal:
expression = expression.And(CreateEqualExpression<V>(vPropertyInfo.Name, value));
break;
case ConditionType.NotEqual:
expression = expression.And(CreateNotEqualExpression<V>(vPropertyInfo.Name, value));
break;
case ConditionType.Include:
if (IsArrayOrCollection(propertyInfo))
{
//是数组才Include,
if (propertyInfo.PropertyType == typeof(string))
{
//string单独处理
expression = expression.And(CreateLikeExpression<V>(vPropertyInfo.Name, value));
continue;
}
//处理数组 In
var elementType = propertyInfo.PropertyType.GetElementType();
var type = GetElementType<V>(elementType, vPropertyInfo.Name, value);
if (type is not null)
expression = expression.And(type);
}
break;
case ConditionType.Exclude:
if (IsArrayOrCollection(propertyInfo))
{
//是数组才Include,
if (propertyInfo.PropertyType == typeof(string))
{
//string单独处理
var likeExpression = CreateLikeExpression<V>(vPropertyInfo.Name, value);
expression =
expression.And(Expression.Lambda<Func<V, bool>>(
Expression.Not(likeExpression.Body), likeExpression.Parameters));
continue;
}
//处理数组 not In
var elementType = propertyInfo.PropertyType.GetElementType();
var type = GetElementType<V>(elementType, vPropertyInfo.Name, value);
if (type is not null)
{
//反转Expression.
var notIn = Expression.Not(type.Body);
expression =
expression.And(Expression.Lambda<Func<V, bool>>(notIn, type.Parameters));
}
}
break;
default:
continue;
}
}
}
else
{
//如果是可空类型且只值为空则跳过
if (value is null)
continue;
//可控类型不为空也可以直接设置lambda表达式
//if忽略就自动略过
if (eA is { IsIgnore: true })
continue;
//设置别名
if (entityDic.TryGetValue(eA.EntityName.ToUpper(), out var vPropertyInfo))
{
switch (eA.Condition)
{
case ConditionType.Equal:
expression = expression.And(CreateEqualExpression<V>(vPropertyInfo.Name, value));
break;
case ConditionType.NotEqual:
expression = expression.And(CreateNotEqualExpression<V>(vPropertyInfo.Name, value));
break;
case ConditionType.Include:
if (IsArrayOrCollection(propertyInfo))
{
//是数组才Include,
if (propertyInfo.PropertyType == typeof(string))
{
//string单独处理
expression = expression.And(CreateLikeExpression<V>(vPropertyInfo.Name, value));
continue;
}
//处理数组 In
var elementType =
Nullable.GetUnderlyingType(propertyInfo.PropertyType.GetElementType() ??
typeof(String));
var type = GetElementType<V>(elementType, vPropertyInfo.Name, value);
if (type is not null)
expression = expression.And(type);
}
break;
case ConditionType.Exclude:
if (IsArrayOrCollection(propertyInfo))
{
//是数组才Include,
if (propertyInfo.PropertyType == typeof(string))
{
//string单独处理
var likeExpression = CreateLikeExpression<V>(vPropertyInfo.Name, value);
expression =
expression.And(Expression.Lambda<Func<V, bool>>(
Expression.Not(likeExpression.Body), likeExpression.Parameters));
continue;
}
//处理数组 not In
var elementType =
Nullable.GetUnderlyingType(propertyInfo.PropertyType.GetElementType() ??
typeof(String));
var type = GetElementType<V>(elementType, vPropertyInfo.Name, value);
if (type is not null)
{
//反转Expression.
var notIn = Expression.Not(type.Body);
expression =
expression.And(Expression.Lambda<Func<V, bool>>(notIn, type.Parameters));
}
}
break;
default:
continue;
}
}
}
}
//给其设置Is_deleted==false 的默认查询条件
if (entityDic.TryGetValue("ISDELETED", out var isDel))
expression = expression.And(CreateEqualExpression<V>(isDel.Name, false));
return expression;
}
private static Expression<Func<V, bool>> GetElementType<V>(Type? elementType, string propertyName, object? value)
{
Expression<Func<V, bool>> inExpression = null;
switch (Type.GetTypeCode(elementType))
{
case TypeCode.String:
// 如果元素类型是字符串,使用 string.Contains 方法
var changeString = ((IEnumerable)value).Cast<string>();
inExpression = CreateInExpression<V, string>(propertyName, changeString);
break;
case TypeCode.Int32:
// 如果元素类型是整数,使用 IEnumerable.Contains 方法
var changeInt = ((IEnumerable)value).Cast<Int32>();
inExpression = CreateInExpression<V, Int32>(propertyName, changeInt);
break;
}
return inExpression;
}
private static Expression<Func<V, bool>> CreateEqualExpression<V>(string columnName, object value)
{
ParameterExpression parameter = Expression.Parameter(typeof(V), "e");
MemberExpression memberExpression = Expression.PropertyOrField(parameter, columnName);
ConstantExpression constantExpression = Expression.Constant(value);
BinaryExpression binaryExpression = Expression.Equal(memberExpression, constantExpression);
return Expression.Lambda<Func<V, bool>>(binaryExpression, parameter);
}
private static Expression<Func<V, bool>> CreateNotEqualExpression<V>(string columnName, object value)
{
ParameterExpression parameter = Expression.Parameter(typeof(V), "e");
MemberExpression memberExpression = Expression.PropertyOrField(parameter, columnName);
ConstantExpression constantExpression = Expression.Constant(value);
BinaryExpression binaryExpression = Expression.NotEqual(memberExpression, constantExpression);
return Expression.Lambda<Func<V, bool>>(binaryExpression, parameter);
}
private static Expression<Func<V, bool>> CreateLikeExpression<V>(string columnName, object searchText)
{
ParameterExpression parameter = Expression.Parameter(typeof(V), "l");
MemberExpression memberExpression = Expression.PropertyOrField(parameter, columnName);
ConstantExpression constantExpression = Expression.Constant(searchText);
MethodCallExpression likeExpression = Expression.Call(
memberExpression,
typeof(string).GetMethod("Contains", [typeof(string)]),
constantExpression
);
return Expression.Lambda<Func<V, bool>>(likeExpression, parameter);
}
private static Expression<Func<T, bool>> CreateInExpression<T, TValue>(string propertyName,
IEnumerable<TValue>? values)
{
// 获取参数表达式
var parameter = Expression.Parameter(typeof(T), "I");
// 获取属性表达式
var property = Expression.Property(parameter, propertyName);
// 创建常量表达式
var constant = Expression.Constant(values, typeof(IEnumerable<TValue>));
// 创建 Contains 方法调用表达式
var containsMethod = typeof(Enumerable).GetMethods()
.First(m => m.Name == "Contains" && m.GetParameters().Length == 2)
.MakeGenericMethod(typeof(TValue));
var containsExpression = Expression.Call(containsMethod, constant, property);
// 创建 lambda 表达式
return Expression.Lambda<Func<T, bool>>(containsExpression, parameter);
}
private static bool IsNullableType(this Type type)
{
return !type.IsValueType || (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>));
}
private static bool IsArrayOrCollection(PropertyInfo propertyInfo)
{
Type propertyType = propertyInfo.PropertyType;
// 检查是否为数组类型 检查是否实现了 IEnumerable 接口
if (propertyType.IsArray || typeof(IEnumerable).IsAssignableFrom(propertyType))
return true;
// 检查是否为泛型集合类型
if (propertyType.IsGenericType)
{
Type genericTypeDefinition = propertyType.GetGenericTypeDefinition();
if (genericTypeDefinition == typeof(List<>) ||
genericTypeDefinition == typeof(HashSet<>) ||
genericTypeDefinition == typeof(ICollection<>) ||
genericTypeDefinition == typeof(IList<>) ||
genericTypeDefinition == typeof(ISet<>))
return true;
}
return false;
}
}3.添加表达式Extension:
public static class ExpressionExtension
{
/// <summary>
/// 初始化一个逻辑值为true的表达式
/// </summary>
/// <typeparam name="TEntity">实体类型</typeparam>
/// <returns>新的表达式</returns>
public static Expression<Func<TEntity, bool>> True<TEntity>()
{
return t => true;
}
/// <summary>
/// 初始化一个逻辑值为false的表达式
/// </summary>
/// <typeparam name="TEntity">实体类型</typeparam>
/// <returns>新的表达式</returns>
public static Expression<Func<TEntity, bool>> False<TEntity>()
{
return t => false;
}
//生成逻辑与表达式
public static Expression<Func<TEntity, bool>> And<TEntity>(this Expression<Func<TEntity, bool>> first,
Expression<Func<TEntity, bool>> second)
{
ParameterExpression parameter = Expression.Parameter(typeof(TEntity), "t");
ParameterRebinder reBinder = new ParameterRebinder(parameter);
Expression left = reBinder.RebindParameter(first.Body);
Expression right = reBinder.RebindParameter(second.Body);
Expression body = Expression.AndAlso(left, right);
Expression<Func<TEntity, bool>> expression = Expression.Lambda<Func<TEntity, bool>>(body, parameter);
return expression;
}
/// <summary>
/// 生成逻辑或表达式
/// </summary>
/// <typeparam name="TEntity">实体类型</typeparam>
/// <param name="first">第一个表达式</param>
/// <param name="second">第二个表达式</param>
/// <returns>新的表达式</returns>
public static Expression<Func<TEntity, bool>> Or<TEntity>(Expression<Func<TEntity, bool>> first,
Expression<Func<TEntity, bool>> second)
{
ParameterExpression parameter = Expression.Parameter(typeof(TEntity), "t");
ParameterRebinder reBinder = new ParameterRebinder(parameter);
Expression left = reBinder.RebindParameter(first.Body);
Expression right = reBinder.RebindParameter(second.Body);
Expression body = Expression.OrElse(left, right);
Expression<Func<TEntity, bool>> expression = Expression.Lambda<Func<TEntity, bool>>(body, parameter);
return expression;
}
}public class ParameterRebinder: ExpressionVisitor
{
/// <summary>
/// 类型参数
/// </summary>
private ParameterExpression parameter;
/// <summary>
/// 构造函数
/// </summary>
/// <param name="parameter">类型参数</param>
public ParameterRebinder(ParameterExpression parameter)
{
this.parameter = parameter;
}
/// <summary>
/// 替换类型参数
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
public Expression RebindParameter(Expression expression)
{
return Visit(expression);
}
/// <summary>
/// 重写VisitParameter
/// </summary>
/// <param name="node"></param>
/// <returns></returns>
protected override Expression VisitParameter(ParameterExpression node)
{
return this.parameter;
}
}实例：
1.参数写法
2.Manager写法：
以上就可以拿到结果了，如果涉及到比较负责的条件查询，比如参数某个字段需要查询单表，可以将其标注为Ignore 为true，然后复用这个Expression即可。
以上就是简单的分享.
扫一扫
160
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
