使用表达式树构造LINQ GroupBy查询 [英] Construct a LINQ GroupBy query using expression trees
问题描述
我已经在这个问题上坚持了一个星期，没有找到解决办法.
I have stuck on this problem for a week and no solution found.
我的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计数的期刊数量.
I want to know during a specific date span ( grouped by months or years ) the amount of journals count by a AuthorName or a Category.
将查询的对象发送到JSON序列化器之后，生成如下所示的JSON数据(仅使用JSON演示我想要获取的数据，如何将对象序列化为JSON并不是我的问题)
After I send the queryed object to JSON serializer then generated JSON data like below ( just using JSON to demonstrate the data I want to get, how to serializer a object to a JSON is not my problem )
data: {
'201301': {
'Alex': 10,
'James': 20
},
'201302': {
'Alex': 1,
'Jessica': 9
}
}
OR
data: {
'2012': {
'C#': 230
'VB.NET': 120,
'LINQ': 97
},
'2013': {
'C#': 115
'VB.NET': 29,
'LINQ': 36
}
}
我所知道的是以方法"编写LINQ查询，例如:
What I know is to write a LINQ query in "method way" like:
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()方法中使用魔术字符串"参数.经过一番谷歌搜索之后，似乎我无法通过传递诸如"AuthorName"之类的魔术字符串来对数据进行分组.我应该做的是构建一个表达式树，并将其传递给GroupBy()方法.
The conditions that grouped by months or years, AuthorName or Category will be passed by two string type method parameters. What I don't know is how to use "Magic String" parameters in a GroupBy() method . After some googling, it seems that I cannot group data by passing a magic string like "AuthorName". What I should to do is build a expression tree and pass it to the GroupBy() method.
任何解决方案或建议都值得赞赏.
Any solution or suggestion is appreciate.
推荐答案
哦，这看起来像是一个 fun 问题:)
Ooh, this looks like a fun problem :)
所以，首先，让我们建立我们的人造源，因为我没有数据库的方便:
So first, let's set up our faux-source, since I don't have your DB handy:
// 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 };
好，所以现在我们有一组数据可以使用，让我们看一下我们想要的...我们想要带有形状"的东西，例如:
Ok, so now we've got a set of data to work with, let's look at what we want...we want something with a "shape" like:
public Expression<Func<Journal, ????>> GetThingToGroupByWith(
string[] someMagicStringNames,
????)
其功能与(使用伪代码)大致相同:
That has roughly the same functionality as (in pseudo code):
GroupBy(x => new { x.magicStringNames })
让我们一次将其解剖.首先，我们如何动态地做到这一点?
Let's dissect it one piece at a time. First, how the heck do we do this dynamically?
x => new { ... }
编译器通常为我们做魔术-它所做的是定义一个新的Type，我们可以做同样的事情:
The compiler does the magic for us normally - what it does is define a new Type, and we can do the same:
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();
因此，我们在这里定义的是一个自定义的，一次性的类型，对于传入的每个名称都具有一个字段，该类型与源类型上的(属性或字段)相同.很好！
So what we've done here is define a custom, throwaway type that has one field for each name we pass in, which is the same type as the (either Property or Field) on the source type. Nice!
现在我们如何给LINQ想要什么?
Now how do we give LINQ what it wants?
首先，让我们为返回的函数设置一个输入":
First, let's set up an "input" for the func we'll return:
// Create and return an expression that maps T => dynamic type
var sourceItem = Expression.Parameter(sourceType, "item");
我们知道我们需要更新"一种新的动态类型...
We know we'll need to "new up" one of our new dynamic types...
Expression.New(dynamicType.GetConstructor(Type.EmptyTypes))
我们将需要使用该参数中的值来对其进行初始化...
And we'll need to initialize it with the values coming in from that parameter...
Expression.MemberInit(
Expression.New(dynamicType.GetConstructor(Type.EmptyTypes)),
bindings),
但是我们要对bindings使用什么呢?嗯...嗯，我们想要绑定到源类型中相应属性/字段的东西，但是将它们重新映射到我们的dynamicType字段...
But what the heck are we going to use for bindings? Hmm...well, we want something that binds to the corresponding properties/fields in the source type, but remaps them to our dynamicType fields...
var bindings = dynamicType
.GetFields()
.Select(p =>
Expression.Bind(
p,
Expression.PropertyOrField(
sourceItem,
p.Name)))
.OfType<MemberBinding>()
.ToArray();
看起来很讨厌，但还没有完成-因此我们需要为通过表达式树创建的Func声明返回类型...如果有疑问，请使用object！
Oof...nasty looking, but we're still not done - so we need to declare a return type for the Func we're creating via Expression trees...when in doubt, use object!
Expression.Convert( expr, typeof(object))
最后，我们将通过Lambda将其绑定到我们的输入参数"，从而形成整个堆栈:
And finally, we'll bind this to our "input parameter" via Lambda, making the whole stack:
// 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);
为了易于使用，让我们将整个混乱包装为扩展方法，所以现在我们有了:
For ease of use, let's wrap the whole mess up as an extension method, so now we've got:
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.Month")的灵活性，但是如果您有一点想像力，您可以将其扩展为可与任何自由格式查询一起使用.
This solution lacks the flexibility of "nested statements", like "CreatedDate.Month", but with a bit of imagination, you could possibly extend this idea to work with any freeform query.
这篇关于使用表达式树构造LINQ GroupBy查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
