两棵表达式树 (Expression Tree)

一、概述

表达式树( Expression Tree )以树形数据结构表示代码,其中每一个节点都是一种表达式。
本文通过 API 创建表达式树,以满足某些不方便根据 Lambda 表达式创建表达式树的情况。

二、Contains 和 Or

照片( Photo )有标签 (Tags) 属性,是以半角逗号分隔的。比如一张照片有”人物,风景,节日”标签。这里姑且不论这样设计是否合理。
查询带有 “人物” 标签的数据:

1
2
3
4
// SELECT * FROM [Photos] WHERE Tags LIKE '%人物%'
var query = from p in Photos
where p.Tags.Contains("人物")
select p;

稍微增加点复杂度,只查询本年度的照片:

1
2
3
4
5
// SELECT * FROM [Photos] WHERE CreationTime >= '2019-01-01' AND Tags LIKE '%人物%'
var creationTime = new DateTime(DateTime.Now.Year, 1, 1);
var query = from p in Photos
where p.CreationTime >= creationTime && p.Tags.Contains("人物")
select p;

查询本年度带有 “人物” 并且 带有 “风景” 标签的数据:

1
2
3
4
5
// SELECT * FROM [Photos] WHERE CreationTime >= '2019-01-01' AND (Tags LIKE '%人物%' AND Tags LIKE '%风景%')
var creationTime = new DateTime(DateTime.Now.Year, 1, 1);
var query = from p in Photos
where p.CreationTime >= creationTime && (p.Tags.Contains("人物") && p.Tags.Contains("风景"))
select p;

查询本年度带有 “人物” 或者 带有 “风景” 标签的数据:

1
2
3
4
5
// SELECT * FROM [Photos] WHERE CreationTime >= '2019-01-01' AND (Tags LIKE '%人物%' OR Tags LIKE '%风景%')
var creationTime = new DateTime(DateTime.Now.Year, 1, 1);
var query = from p in Photos
where p.CreationTime >= creationTime && (p.Tags.Contains("人物") || p.Tags.Contains("风景"))
select p;

如果搜索条件是动态的,要查询本年度带有 “人物” 并且 带有 “风景” 标签的数据:

1
2
3
4
5
6
7
// SELECT * FROM [Photos] WHERE  CreationTime >= '2019-01-01' AND (Tags LIKE '%人物%' AND Tags LIKE '%风景%')
var creationTime = new DateTime(DateTime.Now.Year, 1, 1);
var searchTags = new [] {"人物", "风景"};
IQueryable<Photo> query = Photos.Where(m => m.CreationTime >= creationTime);
for(var tag in searchTags) {
query = query.Where(m => m.Tags.Contains("人物"))
}

如果搜索条件是动态的,要查询本年度带有 “人物” 或者 带有 “风景” 标签的数据,又如何构造查询呢?因为多个 whereAnd 查询,所以不能直接使用上例所示的方式构造查询。
首先,写一个扩展方法 (Extension method):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
public static IQueryable<TEntity> WhereOrStringContains<TEntity, String>
(
this IQueryable<TEntity> query,
Expression<Func<TEntity, String>> selector,
IEnumerable<String> values
)
{
if (selector == null)
{
throw new ArgumentNullException(nameof(selector));
}
if (values == null)
{
throw new ArgumentNullException(nameof(values));
}

if (!values.Any()) return query;

ParameterExpression p = selector.Parameters.Single();
var containsExpressions = values.Select(value => (Expression)Expression.Call(selector.Body, typeof(String).GetMethod("Contains", new[] { typeof(String) }), Expression.Constant(value)));
Expression body = containsExpressions.Aggregate((accumulate, containsExpression) => Expression.Or(accumulate, containsExpression));

return query.Where(Expression.Lambda<Func<TEntity, bool>>(body, p));
}

使用示例:

1
2
3
4
// SELECT * FROM [Photos] WHERE CreationTime >= '2019-01-01' AND (Tags LIKE '%人物%' OR Tags LIKE '%风景%')
var creationTime = new DateTime(DateTime.Now.Year, 1, 1);
var searchTags = new [] {"人物", "风景"};
var query = Photos.Where(m => m.CreationTime >= creationTime).WhereOrStringContains(m => m.Tags, searchTags);

三、Collection、Any 和 Equal

现在将照片和标签分离,使用一个中间结构形成多对多的关系。
查询带有 “人物” 标签的数据:

1
2
3
4
5
6
7
8
9
/*
SELECT * FROM [Photos] AS P
WHERE EXISTS (
SELECT 1
FROM [PhotoTags] AS PT
WHERE PT.TagId = 1 AND P.PhotoId = PT.PhotoId)
*/
var tagId = 1;
var query = Photos.Where(m => m.PhotoTags.Any(n => n.TagId = tagId));

查询带有 “人物” 并且 带有 “风景” 标签的数据:

1
2
var query = Photos.Where(m => m.PhotoTags.Any(n => n.TagId = 1));
query = query = Photos.Where(m => m.PhotoTags.Any(n => n.TagId = 2));

查询带有 “人物” 或者 带有 “风景” 标签的数据:

1
var query = Photos.Where(m => m.PhotoTags.Any(n => n.TagId = 1 || n.TagId = 2));

如果搜索条件是动态的,要查询带有 “人物” 并且 带有 “风景” 标签的数据:

1
2
3
4
5
var tagIds = new [] {1, 2};
IQueryable<Photo> query = Photos;
for(var tagId in tagIds) {
query = query.Where(m => m.PhotoTags.Any(n => n.TagId = tagId));
}

如果搜索条件是动态的,要查询带有 “人物” 或者 带有 “风景” 标签的数据,又如何构造查询呢?因为多个 whereAnd 查询,所以不能直接使用上例所示的方式构造查询。
首先,写一个扩展方法 (Extension method):

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
public static IQueryable<TEntity> WhereOrCollectionAnyEqual<TEntity, TValue, TMemberValue>
(
this IQueryable<TEntity> query,
Expression<Func<TEntity, IEnumerable<TValue>>> selector,
Expression<Func<TValue, TMemberValue>> memberSelector,
IEnumerable<TMemberValue> values
)
{
if (selector == null)
{
throw new ArgumentNullException(nameof(selector));
}
if (values == null)
{
throw new ArgumentNullException(nameof(values));
}

if (!values.Any()) return query;

ParameterExpression selectorParameter = selector.Parameters.Single();
ParameterExpression memberParameter = memberSelector.Parameters.Single();
var methodInfo = GetEnumerableMethod("Any", 2).MakeGenericMethod(typeof(TValue));
var anyExpressions = values.Select(value =>
(Expression)Expression.Call(null,
methodInfo,
selector.Body,
Expression.Lambda<Func<TValue, bool>>(Expression.Equal(memberSelector.Body,
Expression.Constant(value, typeof(TMemberValue))),
memberParameter
)
)
);
Expression body = anyExpressions.Aggregate((accumulate, any) => Expression.Or(accumulate, any));

return query.Where(Expression.Lambda<Func<TEntity, bool>>(body, selectorParameter));
}

使用示例:

1
2
var tagIds = new [] {1, 2};
var query = Photos.WhereOrCollectionAnyEqual(m => m.PhotoTags, m => m.TagId, tagIds);

参考资料

https://docs.microsoft.com/zh-cn/dotnet/csharp/programming-guide/concepts/expression-trees/