背景
之前看过nodejs，sequelize的orm过滤很丰富，写起来很方便
具体文档地址
过滤条件示例
const { Op } = require("sequelize");
Post.findAll({
where: {
[Op.and]: [{ a: 5 }, { b: 6 }], // (a = 5) AND (b = 6)
[Op.or]: [{ a: 5 }, { b: 6 }], // (a = 5) OR (b = 6)
someAttribute: {
// 基本
[Op.eq]: 3, // = 3
[Op.ne]: 20, // != 20
[Op.is]: null, // IS NULL
[Op.not]: true, // IS NOT TRUE
[Op.or]: [5, 6], // (someAttribute = 5) OR (someAttribute = 6)
// 使用方言特定的列标识符 (以下示例中使用 PG):
[Op.col]: 'user.organization_id', // = "user"."organization_id"
// 数字比较
[Op.gt]: 6, // > 6
[Op.gte]: 6, // >= 6
[Op.lt]: 10, // < 10
[Op.lte]: 10, // <= 10
[Op.between]: [6, 10], // BETWEEN 6 AND 10
[Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15
// 其它操作符
[Op.all]: sequelize.literal('SELECT 1'), // > ALL (SELECT 1)
[Op.in]: [1, 2], // IN [1, 2]
[Op.notIn]: [1, 2], // NOT IN [1, 2]
[Op.like]: '%hat', // LIKE '%hat'
[Op.notLike]: '%hat', // NOT LIKE '%hat'
[Op.startsWith]: 'hat', // LIKE 'hat%'
[Op.endsWith]: 'hat', // LIKE '%hat'
[Op.substring]: 'hat', // LIKE '%hat%'
[Op.iLike]: '%hat', // ILIKE '%hat' (不区分大小写) (仅 PG)
[Op.notILike]: '%hat', // NOT ILIKE '%hat' (仅 PG)
[Op.regexp]: '^[h|a|t]', // REGEXP/~ '^[h|a|t]' (仅 MySQL/PG)
[Op.notRegexp]: '^[h|a|t]', // NOT REGEXP/!~ '^[h|a|t]' (仅 MySQL/PG)
[Op.iRegexp]: '^[h|a|t]', // ~* '^[h|a|t]' (仅 PG)
[Op.notIRegexp]: '^[h|a|t]', // !~* '^[h|a|t]' (仅 PG)
[Op.any]: [2, 3], // ANY ARRAY[2, 3]::INTEGER (仅 PG)
[Op.match]: Sequelize.fn('to_tsquery', 'fat & rat') // 匹配文本搜索字符串 'fat' 和 'rat' (仅 PG)
// 在 Postgres 中, Op.like/Op.iLike/Op.notLike 可以结合 Op.any 使用:
[Op.like]: { [Op.any]: ['cat', 'hat'] } // LIKE ANY ARRAY['cat', 'hat']
// 还有更多的仅限 postgres 的范围运算符,请参见下文
}
}
});
前端可以直接向后台传递这个对象，后台直接执行查询，同时还可以在此基础上增加基础查询，比如增加租户过滤、限制Take数据条数。
开始解析动态表达式
经过搜索过，发现超简单的集成表达式树查询组件，Sy.ExpressionBuilder 使用说明 和我的需求很相似，本来想clone作者的代码改一下，不过对方没有开源，索性只好自己写。
Expression 基本上由以下部分组成
举个例子， x => x.Name.Contains("cnblogs")
形参 x，ParameterExpression类型，通过 var parameter = Expression.Parameter(typeof(T), "x"); 即可创建运算符Contains 后续重点讲左侧参数x.Name ，通过 var propertyExpression = Expression.Property(parameter, parentPropName);右侧参数cnblogs ，常量 Expression.Constant(”cnblogs“)
常规运算符
内置的表达式有如下：
["eq"] = Expression.Equal,
["ne"] = Expression.NotEqual,
["gt"] = Expression.GreaterThan,
["gte"] = Expression.GreaterThanOrEqual,
["lt"] = Expression.LessThan,
["lte"] = Expression.LessThanOrEqual,
如果不在此范围内，就需要自己构建表达式，比如字符串的Contains，找到Method，并手工Call.
public static Expression BuildLikeExpression(Expression left, Expression right)
{
var likeMethod = typeof(string).GetMethod("Contains", new Type[] { typeof(string) })!;
return Expression.Call(left, likeMethod, right);
}
理解了left、right、method后，其实这段代码其实就比较好读了。
需要特殊处理的运算符
泛型方法和null比较，下方示例参数
age: new int[]{ 18,19,20} // age in (18, 19, 20)
school : { null: true} // school is null
泛型方法
对于泛型方法来说，我们已知的Left和Right参数，Method需要手工查找
private static MethodInfo GetContainsMethod(Type elementType)
{
var enumerableType = typeof(Enumerable);
var containsMethods = enumerableType.GetMethods()
.Where(m => m.Name == "Contains" && m.GetParameters().Length == 2)
.Select(m => m.MakeGenericMethod(elementType))
.ToList();
foreach (var method in containsMethods)
{
var parameters = method.GetParameters();
if (parameters[0].ParameterType == typeof(IEnumerable<>).MakeGenericType(elementType) &&
parameters[1].ParameterType == elementType)
{
return method;
}
}
return null;
}
找到后就可以call了。
null 比较运算符
由于参数传递的是true、false，来表示 == null 和 != null
public static Expression BuildNullExpression(Expression left, Expression right)
{
// 检查 right 是否为 ConstantExpression 类型
if (right is ConstantExpression constantExpression)
{
var result = false;
if (constantExpression.Value is bool boolValue)
{
result = boolValue;
}
else if (constantExpression.Value is int intValue)
{
result = intValue == 0 ? false : true;
}
else if (constantExpression.Value is string stringValue)
{
result = !string.IsNullOrEmpty(stringValue) && stringValue != "0";
}
if (result)
{
return Expression.Equal(left, Expression.Constant(null));
}
else
{
return Expression.NotEqual(left, Expression.Constant(null));
}
}
throw new Exception("null 表达式传值不正确， 只能传递true或者false ");
}
完整代码如下：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
namespace ConsoleApp1
{
public class Program
{
public static void Main(string[] args)
{
// 测试数据
var persons = Person.CreateSampleData();
List<Person> filteredData = null;
List<Person> expectData = null;
// 运算符测试
// eq
filteredData = persons.AsQueryable().ApplyQuery(ConvertObjToDictionary(new
{
IntField = persons[0].IntField,
StringField = persons[0].StringField,
DecimalField = persons[0].DecimalField,
GuidField = persons[0].GuidField,
DateTimeField = persons[0].DateTimeField,
BoolField = persons[0].BoolField,
})).ToList();
expectData = persons.Where(t => t.IntField == persons[0].IntField &&
t.StringField == persons[0].StringField &&
t.DecimalField == persons[0].DecimalField &&
t.GuidField == persons[0].GuidField &&
t.DateTimeField == persons[0].DateTimeField &&
t.BoolField == persons[0].BoolField
).ToList();
Assert("eq简写验证", filteredData, expectData);
filteredData = persons.AsQueryable().ApplyQuery(ConvertObjToDictionary(new
{
IntField = new { eq = persons[0].IntField },
StringField = new { eq = persons[0].StringField },
DecimalField = new { eq = persons[0].DecimalField },
GuidField = new { eq = persons[0].GuidField },
DateTimeField = new { eq = persons[0].DateTimeField },
BoolField = new { eq = persons[0].BoolField },
})).ToList();
expectData = persons.Where(t => t.IntField == persons[0].IntField &&
t.StringField == persons[0].StringField &&
t.DecimalField == persons[0].DecimalField &&
t.GuidField == persons[0].GuidField &&
t.DateTimeField == persons[0].DateTimeField &&
t.BoolField == persons[0].BoolField
).ToList();
Assert("eq验证", filteredData, expectData);
filteredData = persons.AsQueryable().ApplyQuery(ConvertObjToDictionary(new
{
IntField = new { ne = persons[0].IntField },
StringField = new { ne = persons[0].StringField },
DecimalField = new { ne = persons[0].DecimalField },
GuidField = new { ne = persons[0].GuidField },
DateTimeField = new { ne = persons[0].DateTimeField },
BoolField = new { ne = persons[0].BoolField },
})).ToList();
expectData = persons.Where(t => t.IntField != persons[0].IntField &&
t.StringField != persons[0].StringField &&
t.DecimalField != persons[0].DecimalField &&
t.GuidField != persons[0].GuidField &&
t.DateTimeField != persons[0].DateTimeField &&
t.BoolField != persons[0].BoolField
).ToList();
Assert("ne验证", filteredData, expectData);
filteredData = persons.AsQueryable().ApplyQuery(ConvertObjToDictionary(new
{
IntField = new { gt = persons[0].IntField },
DecimalField = new { gt = persons[0].DecimalField },
DateTimeField = new { gt = persons[0].DateTimeField },
})).ToList();
expectData = persons.Where(t => t.IntField > persons[0].IntField &&
t.DecimalField > persons[0].DecimalField &&
t.DateTimeField > persons[0].DateTimeField
).ToList();
Assert("gt验证", filteredData, expectData);
filteredData = persons.AsQueryable().ApplyQuery(ConvertObjToDictionary(new
{
IntField = new { gte = persons[0].IntField },
DecimalField = new { gte = persons[0].DecimalField },
DateTimeField = new { gte = persons[0].DateTimeField },
})).ToList();
expectData = persons.Where(t => t.IntField >= persons[0].IntField &&
t.DecimalField >= persons[0].DecimalField &&
t.DateTimeField >= persons[0].DateTimeField
).ToList();
Assert("gte验证", filteredData, expectData);
filteredData = persons.AsQueryable().ApplyQuery(ConvertObjToDictionary(new
{
IntField = new { lt = persons[10].IntField },
DecimalField = new { lt = persons[10].DecimalField },
DateTimeField = new { lt = persons[10].DateTimeField },
})).ToList();
expectData = persons.Where(t => t.IntField < persons[10].IntField &&
t.DecimalField < persons[10].DecimalField &&
t.DateTimeField < persons[10].DateTimeField
).ToList();
Assert("lt验证", filteredData, expectData);
filteredData = persons.AsQueryable().ApplyQuery(ConvertObjToDictionary(new
{
IntField = new { lte = persons[10].IntField },
DecimalField = new { lte = persons[10].DecimalField },
DateTimeField = new { lte = persons[10].DateTimeField },
})).ToList();
expectData = persons.Where(t => t.IntField <= persons[10].IntField &&
t.DecimalField <= persons[10].DecimalField &&
t.DateTimeField <= persons[10].DateTimeField
).ToList();
Assert("lte验证", filteredData, expectData);
filteredData = persons.AsQueryable().ApplyQuery(ConvertObjToDictionary(new
{
IntField = new { @in = persons.Take(3).Select(t => t.IntField).ToList() },
StringField = new { @in = persons.Take(3).Select(t => t.StringField).ToList() },
DecimalField = new { @in = persons.Take(3).Select(t => t.DecimalField).ToList() },
GuidField = new { @in = persons.Take(3).Select(t => t.GuidField).ToList() },
DateTimeField = new { @in = persons.Take(3).Select(t => t.DateTimeField).ToList() },
BoolField = new { @in = persons.Take(3).Select(t => t.BoolField).ToList() },
})).ToList();
expectData = persons.Where(t => persons.Take(3).Select(t => t.IntField).ToList().Contains(t.IntField) &&
persons.Take(3).Select(t => t.StringField).ToList()
.Contains(t.StringField) &&
persons.Take(3).Select(t => t.DecimalField).ToList()
.Contains(t.DecimalField) &&
persons.Take(3).Select(t => t.GuidField).ToList().Contains(t.GuidField) &&
persons.Take(3).Select(t => t.DateTimeField).ToList()
.Contains(t.DateTimeField) &&
persons.Take(3).Select(t => t.BoolField).ToList()
.Contains(t.BoolField)
).ToList();
Assert("in验证", filteredData, expectData);
filteredData = persons.AsQueryable().ApplyQuery(ConvertObjToDictionary(new
{
IntField = new { notin = persons.Take(3).Select(t => t.IntField).ToList() },
StringField = new { notin = persons.Take(3).Select(t => t.StringField).ToList() },
DecimalField = new { notin = persons.Take(3).Select(t => t.DecimalField).ToList() },
GuidField = new { notin = persons.Take(3).Select(t => t.GuidField).ToList() },
DateTimeField = new { notin = persons.Take(3).Select(t => t.DateTimeField).ToList() },
})).ToList();
expectData = persons.Where(it => !persons.Take(3).Select(t => t.IntField).ToList().Contains(it.IntField) &&
!persons.Take(3).Select(t => t.StringField).ToList()
.Contains(it.StringField) &&
!persons.Take(3).Select(t => t.DecimalField).ToList()
.Contains(it.DecimalField) &&
!persons.Take(3).Select(t => t.GuidField).ToList()
.Contains(it.GuidField) &&
!persons.Take(3).Select(t => t.DateTimeField).ToList()
.Contains(it.DateTimeField)
).ToList();
Assert("notin验证", filteredData, expectData);
filteredData = persons.AsQueryable().ApplyQuery(ConvertObjToDictionary(new
{
StringField = new { like = persons[0].StringField },
})).ToList();
expectData = persons.Where(t => t.StringField.Contains(persons[0].StringField)).ToList();
Assert("like验证", filteredData, expectData);
filteredData = persons.AsQueryable().ApplyQuery(ConvertObjToDictionary(new
{
StringField = new { nlike = persons[0].StringField },
})).ToList();
expectData = persons.Where(t => !t.StringField.Contains(persons[0].StringField)).ToList();
Assert("nlike验证", filteredData, expectData);
filteredData = persons.AsQueryable().ApplyQuery(ConvertObjToDictionary(new
{
StringField = new { llike = persons[0].StringField },
})).ToList();
expectData = persons.Where(t => t.StringField.StartsWith(persons[0].StringField)).ToList();
Assert("llike验证", filteredData, expectData);
filteredData = persons.AsQueryable().ApplyQuery(ConvertObjToDictionary(new
{
StringField = new { nllike = persons[0].StringField },
})).ToList();
expectData = persons.Where(t => !t.StringField.StartsWith(persons[0].StringField)).ToList();
Assert("nllike验证", filteredData, expectData);
filteredData = persons.AsQueryable().ApplyQuery(ConvertObjToDictionary(new
{
StringField = new { rlike = persons[0].StringField },
})).ToList();
expectData = persons.Where(t => t.StringField.EndsWith(persons[0].StringField)).ToList();
Assert("rlike验证", filteredData, expectData);
filteredData = persons.AsQueryable().ApplyQuery(ConvertObjToDictionary(new
{
StringField = new { nrlike = persons[0].StringField },
})).ToList();
expectData = persons.Where(t => !t.StringField.EndsWith(persons[0].StringField)).ToList();
Assert("nrlike验证", filteredData, expectData);
filteredData = persons.AsQueryable().ApplyQuery(ConvertObjToDictionary(new
{
NullField = new { @null = true },
})).ToList();
expectData = persons.Where(t => t.NullField == null).ToList();
Assert("null验证：isnull", filteredData, expectData);
filteredData = persons.AsQueryable().ApplyQuery(ConvertObjToDictionary(new
{
NullField = new { @null = false },
})).ToList();
expectData = persons.Where(t => t.NullField != null).ToList();
Assert("null验证：is not null", filteredData, expectData);
// 运算符测试
// eq
filteredData = persons.AsQueryable().ApplyQuery(ConvertObjToDictionary(new
{
IntField = persons[0].IntField,
StringField = persons[0].StringField,
or = new
{
DecimalField = persons[0].DecimalField,
GuidField = persons[0].GuidField,
DateTimeField = persons[0].DateTimeField,
BoolField = persons[0].BoolField,
},
})).ToList();
expectData = persons.Where(t => (t.IntField == persons[0].IntField &&
t.StringField == persons[0].StringField ) &&
(
t.DecimalField == persons[0].DecimalField ||
t.GuidField == persons[0].GuidField ||
t.DateTimeField == persons[0].DateTimeField ||
t.BoolField == persons[0].BoolField
)
).ToList();
Assert("and or 验证", filteredData, expectData);
}
private static Dictionary<string, object> ConvertObjToDictionary(object anonymousObject)
{
if (anonymousObject is null)
{
return null;
}
var dictionary = new Dictionary<string, object>();
var properties = anonymousObject.GetType().GetProperties();
foreach (var property in properties)
{
var propertyName = property.Name;
var propertyValue = property.GetValue(anonymousObject);
// 已知对象直接赋值
if (propertyValue != null && property.PropertyType.IsVisible)
{
dictionary.Add(propertyName, propertyValue);
}
else
{
// 递归处理属性为匿名对象的情况
propertyValue = ConvertObjToDictionary(propertyValue);
dictionary.Add(propertyName, propertyValue);
}
}
return dictionary;
}
private static void Assert(string message, List<Person> result, List<Person> expect)
{
if (result.Count == expect.Count)
{
Console.WriteLine(message + $" 验证成功, 结果为:{expect.Count}");
}
else
{
Console.WriteLine(message + $" 验证失败 , 期望结果: {expect.Count} 实际结果：{result.Count}");
}
}
}
public class Person
{
public int IntField { get; set; }
public string StringField { get; set; }
public decimal DecimalField { get; set; }
public Guid GuidField { get; set; }
public DateTime DateTimeField { get; set; }
public bool BoolField { get; set; }
public Guid? NullField { get; set; }
public static List<Person> CreateSampleData()
{
List<Person> people = new List<Person>();
for (int i = 1; i <= 20; i++)
{
people.Add(new Person()
{
IntField = i,
StringField = "Name" + i,
DecimalField = i,
GuidField = Guid.Parse("00000000-0000-0000-0000-0000000000" + i.ToString().PadLeft(2, '0')),
DateTimeField = new DateTime(2022, 1, 1).AddDays(i),
BoolField = i % 2 == 0 ? true : false,
NullField = i % 2 == 0 ? null : Guid.NewGuid(),
});
}
return people;
}
}
}
Extension.cs
using System;
using System.Collections.Generic;
using System.Dynamic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using ConsoleApp1;
namespace ConsoleApp1
{
public static class DyamicQueryableExtensions
{
// 定义支持的表达式
private static readonly Dictionary<string, Func<Expression, Expression, Expression>> OperatorsAliases =
new Dictionary<string, Func<Expression, Expression, Expression>>
{
["eq"] = Expression.Equal,
["ne"] = Expression.NotEqual,
["gt"] = Expression.GreaterThan,
["gte"] = Expression.GreaterThanOrEqual,
["lt"] = Expression.LessThan,
["lte"] = Expression.LessThanOrEqual,
["in"] = ExpressionHelper.BuildContainsExpression,
["notin"] = ExpressionHelper.BuildNotContainsExpression,
["like"] = ExpressionHelper.BuildLikeExpression,
["nlike"] = ExpressionHelper.BuildNotLikeExpression,
["llike"] = ExpressionHelper.BuildStartsWithExpression,
["nllike"] = ExpressionHelper.BuildNotStartsWithExpression,
["rlike"] = ExpressionHelper.BuildEndsWithExpression,
["nrlike"] = ExpressionHelper.BuildNotEndsWithExpression,
["null"] = ExpressionHelper.BuildNullExpression,
};
/// <summary>
/// 应用动态查询表达式
/// </summary>
/// <param name="query"></param>
/// <param name="where"></param>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static IQueryable<T> ApplyQuery<T>(this IQueryable<T> query, IDictionary<string, object> where)
{
var parameter = Expression.Parameter(typeof(T), "x");
return ApplyQueryInner(query, where, parameter);
}
private static IQueryable<T> ApplyQueryInner<T>(IQueryable<T> query, IDictionary<string, object> where, ParameterExpression parameter)
{
var expression = ParseExpression(parameter, where, "and", null);
var lambda = Expression.Lambda<Func<T, bool>>(expression, parameter);
debug(lambda);
return query.Where(lambda);
}
private static void debug(object a)
{
Console.WriteLine(a.ToString());
}
/// <summary>
/// 转换对象为动态表达式
/// </summary>
/// <param name="parameter"></param>
/// <param name="condition"></param>
/// <param name="relation">多条件的关系运算符 and ，or </param>
/// <param name="parentPropName">父级属性名称</param>
/// <returns></returns>
/// <exception cref="ArgumentException"></exception>
private static Expression ParseExpression(ParameterExpression parameter, IDictionary<string, object> condition,
string relation, string parentPropName)
{
var expressions = new List<Expression>();
foreach (var entry in condition)
{
var key = entry.Key;
var value = entry.Value;
// 运算符，则转换为表达式
if (OperatorsAliases.TryGetValue(key, out var operatorAlias))
{
if (string.IsNullOrEmpty(parentPropName))
{
// 运算符一定要位于属性下
throw new ArgumentException($"运算符{key}没有定义在任何属性下，请检查");
}
var propertyExpression = Expression.Property(parameter, parentPropName);
var express = operatorAlias.Invoke(propertyExpression, Expression.Constant(value));
expressions.Add(express);
}
// 关系运算符
else if (string.CompareOrdinal("and", key) == 0 || string.CompareOrdinal("or", key) == 0)
{
if (value is IDictionary<string, object> valueDic)
{
// 关系运算符，是不使用父级属性的
expressions.Add(ParseExpression(parameter, valueDic, key, null));
}
else
{
throw new Exception($"关系运算符下必须是对象，请检查参数：{key}");
}
}
else
{
// 非运算符说明是常规属性
expressions.Add(ParsePropertyExpression(parameter, key, value));
}
}
if (expressions.Count == 1)
{
return expressions[0];
}
else if (expressions.Count > 1)
{
// 多条件就拼接关系运算符
if (string.CompareOrdinal("and", relation) == 0)
{
return expressions.Aggregate(Expression.AndAlso);
}
else
{
return expressions.Aggregate(Expression.OrElse);
}
}
else
{
return Expression.Constant(true); // 默认返回 true 表达式
}
}
/// <summary>
/// 转换属性表达式，属性可能是List,Array, 或者对象
/// </summary>
/// <param name="parameter"></param>
/// <param name="property"></param>
/// <param name="value"></param>
/// <returns></returns>
private static Expression ParsePropertyExpression(ParameterExpression parameter, string property, object value)
{
var propertyExpression = Expression.Property(parameter, property);
// 如果属性又是嵌套对象
if (value is IDictionary<string, object> dictionary)
{
// 嵌套对象，默认使用and拼接条件
var eqExpression = ParseExpression(parameter, dictionary, "and", property);
return eqExpression;
}
else if (value.GetType().IsArray || value.GetType().IsGenericType)
{
var eqExpression =
ExpressionHelper.BuildContainsExpression(propertyExpression, Expression.Constant(value));
return eqExpression;
}
else
{
var eqExpression = Expression.Equal(propertyExpression, Expression.Constant(value));
return eqExpression;
}
}
}
public static class ExpressionHelper
{
public static Expression BuildContainsExpression(Expression left, Expression right)
{
Type elementType = null;
if (right.Type.IsGenericType)
{
elementType = right.Type.GetGenericArguments().FirstOrDefault();
}
else if (right.Type.IsArray)
{
elementType = right.Type.GetElementType();
}
if (elementType == null)
{
throw new ArgumentException("Invalid list type.", nameof(right));
}
var containsMethod = GetContainsMethod(elementType)!;
return Expression.Call(null, containsMethod, right, left);
}
private static MethodInfo GetContainsMethod(Type elementType)
{
var enumerableType = typeof(Enumerable);
var containsMethods = enumerableType.GetMethods()
.Where(m => m.Name == "Contains" && m.GetParameters().Length == 2)
.Select(m => m.MakeGenericMethod(elementType))
.ToList();
foreach (var method in containsMethods)
{
var parameters = method.GetParameters();
if (parameters[0].ParameterType == typeof(IEnumerable<>).MakeGenericType(elementType) &&
parameters[1].ParameterType == elementType)
{
return method;
}
}
return null;
}
public static Expression BuildNotContainsExpression(Expression left, Expression right)
{
var containsExpression = BuildContainsExpression(left, right);
return Expression.Not(containsExpression);
}
public static Expression BuildLikeExpression(Expression left, Expression right)
{
var likeMethod = typeof(string).GetMethod("Contains", new Type[] { typeof(string) })!;
return Expression.Call(left, likeMethod, right);
}
public static Expression BuildNotLikeExpression(Expression left, Expression right)
{
var likeExpression = BuildLikeExpression(left, right);
return Expression.Not(likeExpression);
}
public static Expression BuildStartsWithExpression(Expression left, Expression right)
{
var startsWithMethod = typeof(string).GetMethod("StartsWith", new[] { typeof(string) })!;
return Expression.Call(left, startsWithMethod, right);
}
public static Expression BuildNotStartsWithExpression(Expression left, Expression right)
{
var startsWithExpression = BuildStartsWithExpression(left, right);
return Expression.Not(startsWithExpression);
}
public static Expression BuildEndsWithExpression(Expression left, Expression right)
{
var endsWithMethod = typeof(string).GetMethod("EndsWith", new[] { typeof(string) })!;
return Expression.Call(left, endsWithMethod, right);
}
public static Expression BuildNotEndsWithExpression(Expression left, Expression right)
{
var endsWithExpression = BuildEndsWithExpression(left, right);
return Expression.Not(endsWithExpression);
}
public static Expression BuildNullExpression(Expression left, Expression right)
{
// 检查 right 是否为 ConstantExpression 类型
if (right is ConstantExpression constantExpression)
{
var result = false;
if (constantExpression.Value is bool boolValue)
{
result = boolValue;
}
else if (constantExpression.Value is int intValue)
{
result = intValue == 0 ? false : true;
}
else if (constantExpression.Value is string stringValue)
{
result = !string.IsNullOrEmpty(stringValue) && stringValue != "0";
}
if (result)
{
return Expression.Equal(left, Expression.Constant(null));
}
else
{
return Expression.NotEqual(left, Expression.Constant(null));
}
}
throw new Exception("null 表达式传值不正确， 只能传递true或者false ");
}
}
}
里面还实现的递归查询，没有做过多的测试，Program代码输出如下：
x => ((((((x.IntField == 1) AndAlso (x.StringField == "Name1")) AndAlso (x.DecimalField == 1)) AndAlso (x.GuidField == 00000000-0000-0000-0000-000000000001)) AndAlso (x.DateTimeField == 2022/1/2 00:00:00)) AndAlso (x.BoolField == False))
eq简写验证 验证成功, 结果为:1
x => ((((((x.IntField == 1) AndAlso (x.StringField == "Name1")) AndAlso (x.DecimalField == 1)) AndAlso (x.GuidField == 00000000-0000-0000-0000-000000000001)) AndAlso (x.DateTimeField == 2022/1/2 00:00:00)) AndAlso (x.BoolField == False))
eq验证 验证成功, 结果为:1
x => ((((((x.IntField != 1) AndAlso (x.StringField != "Name1")) AndAlso (x.DecimalField != 1)) AndAlso (x.GuidField != 00000000-0000-0000-0000-000000000001)) AndAlso (x.DateTimeField != 2022/1/2 00:00:00)) AndAlso (x.BoolField != False))
ne验证 验证成功, 结果为:10
x => (((x.IntField > 1) AndAlso (x.DecimalField > 1)) AndAlso (x.DateTimeField > 2022/1/2 00:00:00))
gt验证 验证成功, 结果为:19
x => (((x.IntField >= 1) AndAlso (x.DecimalField >= 1)) AndAlso (x.DateTimeField >= 2022/1/2 00:00:00))
gte验证 验证成功, 结果为:20
x => (((x.IntField < 11) AndAlso (x.DecimalField < 11)) AndAlso (x.DateTimeField < 2022/1/12 00:00:00))
lt验证 验证成功, 结果为:10
x => (((x.IntField <= 11) AndAlso (x.DecimalField <= 11)) AndAlso (x.DateTimeField <= 2022/1/12 00:00:00))
lte验证 验证成功, 结果为:11
x => (((((value(System.Collections.Generic.List`1[System.Int32]).Contains(x.IntField) AndAlso value(System.Collections.Generic.List`1[System.String]).Contains(x.StringField)) AndAlso value(System.Collections.Generic.List`1[System.Decimal]).Contains(x.DecimalField)) AndAlso value(System.Collections.Generic.List`1[System.Guid]).Contains(x.GuidField)) AndAlso value(System.Collections.Generic.List`1[System.DateTime]).Contains(x.DateTimeField)) AndAlso value(System.Collections.Generic.List`1[System.Boolean]).Contains(x.BoolField))
in验证 验证成功, 结果为:3
x => ((((Not(value(System.Collections.Generic.List`1[System.Int32]).Contains(x.IntField)) AndAlso Not(value(System.Collections.Generic.List`1[System.String]).Contains(x.StringField))) AndAlso Not(value(System.Collections.Generic.List`1[System.Decimal]).Contains(x.DecimalField))) AndAlso Not(value(System.Collections.Generic.List`1[System.Guid]).Contains(x.GuidField))) AndAlso Not(value(System.Collections.Generic.List`1[System.DateTime]).Contains(x.DateTimeField)))
notin验证 验证成功, 结果为:17
x => x.StringField.Contains("Name1")
like验证 验证成功, 结果为:11
x => Not(x.StringField.Contains("Name1"))
nlike验证 验证成功, 结果为:9
x => x.StringField.StartsWith("Name1")
llike验证 验证成功, 结果为:11
x => Not(x.StringField.StartsWith("Name1"))
nllike验证 验证成功, 结果为:9
x => x.StringField.EndsWith("Name1")
rlike验证 验证成功, 结果为:1
x => Not(x.StringField.EndsWith("Name1"))
nrlike验证 验证成功, 结果为:19
x => (x.NullField == null)
null验证：isnull 验证成功, 结果为:10
x => (x.NullField != null)
null验证：is not null 验证成功, 结果为:10
x => (((x.IntField == 1) AndAlso (x.StringField == "Name1")) AndAlso ((((x.DecimalField == 1) OrElse (x.GuidField == 00000000-0000-0000-0000-000000000001)) OrElse (x.DateTimeField == 2022/1/2 00:00:00)) OrElse (x.BoolField == False)))
and or 验证 验证成功, 结果为:1
参考文章
https://www.cnblogs.com/seriawei/p/entity-framework-dynamic-search.html
https://www.cnblogs.com/chenyanbo1024/p/15724055.html
https://www.cnblogs.com/yannis/p/3584818.html
https://www.cnblogs.com/noert/p/15968706.html#5025000