上一篇中说到了 Expression 的一些概念性东西，其实也是为了这一篇做知识准备。为了实现 EFCore 的多条件、连表查询，简化查询代码编写，也就有了这篇文章。
在一些管理后台中，对数据进行多条件查询是一件很普遍的事情，比如在用户列表需要实现可以对 "用户名"、"手机号"、"账户是否冻结" 等等一系列的条件查询，常见的处理方式就是通过一系列 if...else... 来对条件进行拼接。这会导致查询接口实现起来堆叠了一堆看起来有用但实际很繁琐的代码。所以根据前后端请求报文协商，我们就可以按照一定的格式来动态创建表达式树。
创建 QueryEntity 类
QueryEntity 是前端向 API 传递的参数列表，通过这个类，服务端可以知道前端需要查询哪个字段，使用什么方法(Equals、Contains)过滤。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
/// <summary>/// 查询实体/// </summary>public class QueryEntity{ /// <summary> /// 字段名称 /// </summary> public string Key { get; set; } /// <summary> /// 值 /// </summary> public string Value { get; set; } /// <summary> /// 操作方法，对应OperatorEnum枚举类 /// </summary> public string Operator { get; set; } /// <summary> /// 逻辑运算符，只支持AND、OR /// </summary> public string LogicalOperator { get; set; }} |
创建 OperatorEnum 类
OperatorEnum 这是一个操作方法的枚举类，规定了 API 允许的查询方法，比如 Equals、Contains 等等。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
/// <summary>/// 操作方法枚举/// </summary>public enum OperatorEnum{ /// <summary> /// 等于 /// </summary> Equals, /// <summary> /// 不等于 /// </summary> NotEqual, /// <summary> /// 包含 /// </summary> Contains, /// <summary> /// 由什么开始 /// </summary> StartsWith, /// <summary> /// 由什么结束 /// </summary> EndsWith, /// <summary> /// 大于 /// </summary> Greater, /// <summary> /// 大于等于 /// </summary> GreaterEqual, /// <summary> /// 小于 /// </summary> Less, /// <summary> /// 小于等于 /// </summary> LessEqual,} |
创建 ExpressionExtension 类
ExpressionExtension 类实现了表达式树的动态创建，将前端传入的多条件查询转换成表达式，用于 EFCore 的查询。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
|
/// <summary>/// 表达式扩展/// </summary>/// <typeparam name="T">泛型</typeparam>public static class ExpressionExtension<T> where T : class, new(){ /// <summary> /// 表达式动态拼接 /// </summary> public static Expression<Func<T, bool>> ExpressionSplice(List<QueryEntity> entities) { if (entities.Count < 1) { return ex => true; } var expression_first = CreateExpressionDelegate(entities[0]); foreach (var entity in entities.Skip(1)) { var expression = CreateExpressionDelegate(entity); InvocationExpression invocation = Expression.Invoke(expression_first, expression.Parameters.Cast<Expression>()); BinaryExpression binary; // 逻辑运算符判断 if (entity.LogicalOperator.ToUpper().Equals("OR")) { binary = Expression.Or(expression.Body, invocation); } else { binary = Expression.And(expression.Body, invocation); } expression_first = Expression.Lambda<Func<T, bool>>(binary, expression.Parameters); } return expression_first; } /// <summary> /// 创建 Expression<TDelegate> /// </summary> private static Expression<Func<T, bool>> CreateExpressionDelegate(QueryEntity entity) { ParameterExpression param = Expression.Parameter(typeof(T)); Expression key = param; var entityKey = entity.Key.Trim(); // 包含'.'，说明是父表的字段 if (entityKey.Contains('.')) { var tableNameAndField = entityKey.Split('.'); key = Expression.Property(key, tableNameAndField[0].ToString()); key = Expression.Property(key, tableNameAndField[1].ToString()); } else { key = Expression.Property(key, entityKey); } Expression value = Expression.Constant(ParseType(entity)); Expression body = CreateExpression(key, value, entity.Operator); var lambda = Expression.Lambda<Func<T, bool>>(body, param); return lambda; } /// <summary> /// 属性类型转换 /// </summary> /// <param name="entity">查询实体</param> /// <returns></returns> private static object ParseType(QueryEntity entity) { try { PropertyInfo property; // 包含'.'，说明是子类的字段 if (entity.Key.Contains('.')) { var tableNameAndField = entity.Key.Split('.'); property = typeof(T).GetProperty(tableNameAndField[0], BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance); property = property.PropertyType.GetProperty(tableNameAndField[1], BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance); } else { property = typeof(T).GetProperty(entity.Key, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance); } return Convert.ChangeType(entity.Value, property.PropertyType); } catch (Exception) { throw new ArgumentException("字段类型转换失败：字段名错误或值类型不正确"); } } /// <summary> /// 创建 Expression /// </summary> private static Expression CreateExpression(Expression left, Expression value, string entityOperator) { if (!Enum.TryParse(entityOperator, true, out OperatorEnum operatorEnum)) { throw new ArgumentException("操作方法不存在,请检查operator的值"); } return operatorEnum switch { OperatorEnum.Equals => Expression.Equal(left, Expression.Convert(value, left.Type)), OperatorEnum.NotEqual => Expression.NotEqual(left, Expression.Convert(value, left.Type)), OperatorEnum.Contains => Expression.Call(left, typeof(string).GetMethod("Contains", new Type[] { typeof(string) }), value), OperatorEnum.StartsWith => Expression.Call(left, typeof(string).GetMethod("StartsWith", new Type[] { typeof(string) }), value), OperatorEnum.EndsWith => Expression.Call(left, typeof(string).GetMethod("EndsWith", new Type[] { typeof(string) }), value), OperatorEnum.Greater => Expression.GreaterThan(left, Expression.Convert(value, left.Type)), OperatorEnum.GreaterEqual => Expression.GreaterThanOrEqual(left, Expression.Convert(value, left.Type)), OperatorEnum.Less => Expression.LessThan(left, Expression.Convert(value, left.Type)), OperatorEnum.LessEqual => Expression.LessThanOrEqual(left, Expression.Convert(value, left.Type)), _ => Expression.Equal(left, Expression.Convert(value, left.Type)), }; }} |
使用示例
例如有以下两个实体类，Address 是 User 的子类
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
public class User{ public int Id { get; set; } public string Name { get; set; } = string.Empty; public int Age { get; set; } public DateTime CreateTime { get; set; } public Address Address { get; set; }}public class Address{ public string Province { get; set; } public string City { get; set; }} |
单条件查询
查询用户表中名称(name) 包含 "chen" ：
|
1
2
3
4
5
6
7
8
9
10
11
|
List<QueryEntity> list = new List<QueryEntity>{ new QueryEntity { Key = "name", Value = "chen", Operator = "Contains" }};var expression = ExpressionExtension<User>.ExpressionSplice(list);// expression = Param_0 => Param_0.Name.Contains("chen") |
查询用户表中年龄(age) 大于等于 18：
|
1
2
3
4
5
6
7
8
9
10
11
|
List<QueryEntity> list = new List<QueryEntity>{ new QueryEntity { Key = "age", Value = "18", Operator = "GreaterEqual" }};var expression = ExpressionExtension<User>.ExpressionSplice(list);// expression = Param_0 => Param_0.Name.GreaterThanOrEqual(18) |
多条件查询
查询用户表中名称(name) 包含 "chen" 并且年龄(age) 大于等于 18：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
List<QueryEntity> list = new List<QueryEntity>{ new QueryEntity { Key = "name", Value = "chen", Operator = "Contains" }, new QueryEntity { Key = "age", Value = "18", Operator = "GreaterEqual", // 注意：这里得填入 "AND",代表两个条件是并且的关系，如果需要查询名称包含 "chen" 或者 年龄大于等于18，则填入 "OR" "logicalOperator": "AND" }};var expression = ExpressionExtension<User>.ExpressionSplice(list);// expression = Param_0 => ((Param_0.Status >= Convert(1, Int32)) And Invoke(Param_1 => Param_1.OpenId.Contains("9JJdFTVt6oimCgdbW61sk"), Param_0)) |
多表查询
查询用户表中名称(name) 包含 "chen" 并且 地址(address)在广东省：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
List<QueryEntity> list = new List<QueryEntity>{ new QueryEntity { Key = "name", Value = "chen", Operator = "Contains" }, new QueryEntity { Key = "address.Province", Value = "广东省", Operator = "Equals", // 注意：这里得填入 "AND",代表两个条件是并且的关系，如果需要查询名称包含 "chen" 或者 年龄大于等于18，则填入 "OR" "logicalOperator": "AND" }};var expression = ExpressionExtension<BookingRecord>.ExpressionSplice(list);// expression = {Param_0 => ((Param_0.Address.Province == Convert("广东省", String)) And Invoke(Param_1 => Param_1.Name.Contains("chen"), Param_0))} |
到此这篇关于C#表达式树Expression动态创建表达式的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
原文链接：https://www.cnblogs.com/chenyanbo1024/p/15724055.html
