使用表达式树构造LINQ GroupBy查询
本文关键字:LINQ GroupBy 查询 表达式 | 更新日期: 2023-09-27 18:03:04
这个问题我已经纠结了一个星期了,还是没有找到解决办法。
我有一个POCO如下:
public class Journal {
public int Id { get; set; }
public string AuthorName { get; set; }
public string Category { get; set; }
public DateTime CreatedAt { get; set; }
}
我想知道在一个特定的日期范围内(按月或年分组),按AuthorName或Category计算的期刊数量。
在我发送查询对象到JSON序列化器然后生成JSON数据如下(只是使用JSON来演示我想要获得的数据,如何序列化对象到JSON不是我的问题)
data: {
'201301': {
'Alex': 10,
'James': 20
},
'201302': {
'Alex': 1,
'Jessica': 9
}
}
或
data: {
'2012': {
'C#': 230
'VB.NET': 120,
'LINQ': 97
},
'2013': {
'C#': 115
'VB.NET': 29,
'LINQ': 36
}
}
我所知道的是用"方法方式"写一个LINQ查询,如:
IQueryable<Journal> query = db.GroupBy(x=> new
{
Year = key.CreatedAt.Year,
Month = key.CreatedAt.Month
}, prj => prj.AuthorName)
.Select(data => new {
Key = data.Key.Year * 100 + data.Key.Month, // very ugly code, I know
Details = data.GroupBy(y => y).Select(z => new { z.Key, Count = z.Count() })
});
按月或年、AuthorName或Category分组的条件将通过两个字符串类型方法参数传递。我不知道的是如何在GroupBy()方法中使用"Magic String"参数。经过一些谷歌搜索,似乎我不能通过传递像"AuthorName"这样的神奇字符串来分组数据。我应该做的是构建一个表达式树并将其传递给GroupBy()方法。
哦,这看起来像一个有趣的问题:)
所以首先,让我们设置我们的人造源,因为我没有你的DB:
// SETUP: fake up a data source
var folks = new[]{"Alex", "James", "Jessica"};
var cats = new[]{"C#", "VB.NET", "LINQ"};
var r = new Random();
var entryCount = 100;
var entries =
from i in Enumerable.Range(0, entryCount)
let id = r.Next(0, 999999)
let person = folks[r.Next(0, folks.Length)]
let category = cats[r.Next(0, cats.Length)]
let date = DateTime.Now.AddDays(r.Next(0, 100) - 50)
select new Journal() {
Id = id,
AuthorName = person,
Category = category,
CreatedAt = date };
好了,现在我们有一组数据要处理,让我们看看我们想要什么…我们想要一些"形状"像:
public Expression<Func<Journal, ????>> GetThingToGroupByWith(
string[] someMagicStringNames,
????)
它的功能大致与(在伪代码中):
GroupBy(x => new { x.magicStringNames })
让我们一次一块地剖析它。首先,我们究竟如何动态地做到这一点?
x => new { ... }
编译器通常为我们做了神奇的事情——它所做的是定义一个新的Type
,我们也可以这样做:
var sourceType = typeof(Journal);
// define a dynamic type (read: anonymous type) for our needs
var dynAsm = AppDomain
.CurrentDomain
.DefineDynamicAssembly(
new AssemblyName(Guid.NewGuid().ToString()),
AssemblyBuilderAccess.Run);
var dynMod = dynAsm
.DefineDynamicModule(Guid.NewGuid().ToString());
var typeBuilder = dynMod
.DefineType(Guid.NewGuid().ToString());
var properties = groupByNames
.Select(name => sourceType.GetProperty(name))
.Cast<MemberInfo>();
var fields = groupByNames
.Select(name => sourceType.GetField(name))
.Cast<MemberInfo>();
var propFields = properties
.Concat(fields)
.Where(pf => pf != null);
foreach (var propField in propFields)
{
typeBuilder.DefineField(
propField.Name,
propField.MemberType == MemberTypes.Field
? (propField as FieldInfo).FieldType
: (propField as PropertyInfo).PropertyType,
FieldAttributes.Public);
}
var dynamicType = typeBuilder.CreateType();
因此,我们在这里所做的是定义一个自定义的一次性类型,对于我们传入的每个名称都有一个字段,该字段与源类型上的(Property或field)类型相同。好了!现在我们如何给LINQ它想要的?
首先,让我们为要返回的函数设置一个"输入":
// Create and return an expression that maps T => dynamic type
var sourceItem = Expression.Parameter(sourceType, "item");
我们知道我们需要"new up"一个新的动态类型…
Expression.New(dynamicType.GetConstructor(Type.EmptyTypes))
我们需要用参数输入的值来初始化它。
Expression.MemberInit(
Expression.New(dynamicType.GetConstructor(Type.EmptyTypes)),
bindings),
但是我们到底要用什么来代替bindings
呢?嗯…好吧,我们想要绑定到源类型中相应的属性/字段,但将它们重新映射到我们的dynamicType
字段…
var bindings = dynamicType
.GetFields()
.Select(p =>
Expression.Bind(
p,
Expression.PropertyOrField(
sourceItem,
p.Name)))
.OfType<MemberBinding>()
.ToArray();
力量……看起来很糟糕,但是我们仍然没有完成—所以我们需要为我们通过表达式树创建的Func
声明一个返回类型……如果有疑问,请使用object
!
Expression.Convert( expr, typeof(object))
最后,我们将它通过Lambda
绑定到我们的"输入参数",使整个堆栈:
// Create and return an expression that maps T => dynamic type
var sourceItem = Expression.Parameter(sourceType, "item");
var bindings = dynamicType
.GetFields()
.Select(p => Expression.Bind(p, Expression.PropertyOrField(sourceItem, p.Name)))
.OfType<MemberBinding>()
.ToArray();
var fetcher = Expression.Lambda<Func<T, object>>(
Expression.Convert(
Expression.MemberInit(
Expression.New(dynamicType.GetConstructor(Type.EmptyTypes)),
bindings),
typeof(object)),
sourceItem);
为了方便使用,让我们把整个混乱打包成一个扩展方法,所以现在我们有:
public static class Ext
{
// Science Fact: the "Grouper" (as in the Fish) is classified as:
// Perciformes Serranidae Epinephelinae
public static Expression<Func<T, object>> Epinephelinae<T>(
this IEnumerable<T> source,
string [] groupByNames)
{
var sourceType = typeof(T);
// define a dynamic type (read: anonymous type) for our needs
var dynAsm = AppDomain
.CurrentDomain
.DefineDynamicAssembly(
new AssemblyName(Guid.NewGuid().ToString()),
AssemblyBuilderAccess.Run);
var dynMod = dynAsm
.DefineDynamicModule(Guid.NewGuid().ToString());
var typeBuilder = dynMod
.DefineType(Guid.NewGuid().ToString());
var properties = groupByNames
.Select(name => sourceType.GetProperty(name))
.Cast<MemberInfo>();
var fields = groupByNames
.Select(name => sourceType.GetField(name))
.Cast<MemberInfo>();
var propFields = properties
.Concat(fields)
.Where(pf => pf != null);
foreach (var propField in propFields)
{
typeBuilder.DefineField(
propField.Name,
propField.MemberType == MemberTypes.Field
? (propField as FieldInfo).FieldType
: (propField as PropertyInfo).PropertyType,
FieldAttributes.Public);
}
var dynamicType = typeBuilder.CreateType();
// Create and return an expression that maps T => dynamic type
var sourceItem = Expression.Parameter(sourceType, "item");
var bindings = dynamicType
.GetFields()
.Select(p => Expression.Bind(
p,
Expression.PropertyOrField(sourceItem, p.Name)))
.OfType<MemberBinding>()
.ToArray();
var fetcher = Expression.Lambda<Func<T, object>>(
Expression.Convert(
Expression.MemberInit(
Expression.New(dynamicType.GetConstructor(Type.EmptyTypes)),
bindings),
typeof(object)),
sourceItem);
return fetcher;
}
}
现在,使用它:
// What you had originally (hand-tooled query)
var db = entries.AsQueryable();
var query = db.GroupBy(x => new
{
Year = x.CreatedAt.Year,
Month = x.CreatedAt.Month
}, prj => prj.AuthorName)
.Select(data => new {
Key = data.Key.Year * 100 + data.Key.Month, // very ugly code, I know
Details = data.GroupBy(y => y).Select(z => new { z.Key, Count = z.Count() })
});
var func = db.Epinephelinae(new[]{"CreatedAt", "AuthorName"});
var dquery = db.GroupBy(func, prj => prj.AuthorName);
这个解决方案缺乏"嵌套语句"的灵活性,比如"CreatedDate"。