动态LINQ在IQueryable上聚合为单个查询 [英] Dynamic LINQ aggregates on IQueryable as a single query

问题描述

我正在构建一个适用于通用IQueryable数据源的数据网格库.在底部，选定的列将具有汇总:总和，平均值，计数等.

I'm building a datagrid library that works on generic IQueryable data sources. At the bottom selected columns will have aggregates: sum, average, count etc.

我可以使用本文我不想为数据源单独运行它们，因为这会导致对数据库的多个查询，我宁愿创建单个表达式树，并将其作为单个查询执行.

I don't want to run them individually for a datasource, as this would cause multiple queries on the database, I would rather create a single expression tree an execute this as a single query.

在静态LINQ中，您将执行所有.Sum，.Average和.Count方法，并返回带有值的新匿名类型.我不需要匿名类型(除非这是唯一的方法):聚合的列表或数组都可以.

In static LINQ you'd do all the .Sum, .Average and .Count methods and return a new anonymous type with the values. I don't need an anonymous type (unless this is the only way): a list or array of the aggregates would be fine.

我从另一篇文章中假设，我将需要以某种方式将一系列MethodCallExpression对象串在一起.有人可以帮忙吗?

I assume from the other article I would need to string together a series of MethodCallExpression objects somehow. Can anyone help?

推荐答案

我找到了另一种方法，该方法使用动态LINQ库，避免了构建复杂的表达式树.

I found an alternative approach which uses the Dynamic LINQ library and avoids having to construct convoluted expression trees.

该解决方案在下面的单元测试中，适用于任何有兴趣的人.我有一个名为TestQueryableDataset的随机数据集.此IQueryable数据源的通用类型具有Total属性(十进制)，Discount属性(可空十进制)和ID属性(int).

The solution is in the unit test below for anyone who is interested. I have a random dataset called TestQueryableDataset. The generic type of this IQueryable datasource has a Total property (decimal), a Discount property (nullable decimal) and an ID property (int).

单元测试首先使用静态LINQ查询获得预期结果.

The unit test gets the expected results first, using static LINQ queries.

然后构造一个选择语句，该语句使用groupby变量"it"来计算总和，平均值和计数.属性名称通过字符串传递，以证明它是字符串类型的.

It then constructs a select statement that uses the groupby variable 'it' to compute the sum, average and count. The property names are passed in by string to demonstrate this is stringly-typed.

分组方法 .GroupBy(x => 1)是一个虚拟分组，可用于将汇总应用于整个数据集.

The group-by method .GroupBy(x=> 1) is a dummy grouping to enable the aggregates to apply to the whole dataset.

请注意，这将返回具有属性t0，t1和t2的单个动态结果.但是，groupby/select操作仍返回一个IQueryable，但结果为单个.我们必须使用t.Cast().First();转换为对象数组，然后得到第一个结果.

Note that this returns a single dynamic result with properties t0, t1 and t2. However, the groupby/select operation still returns an IQueryable but with a single result. We have to use the t.Cast().First(); to convert to an array of object, then get the first result.

然后我们可以使用反射将每个结果(t0，t1，t2)的属性作为实际值，并断言它们与我们之前获得的静态结果相匹配.

We can then use reflection to get the properties of each result (t0, t1, t2) as the actual values and assert that they match the static result we got earlier.

    [TestMethod()]
    [TestProperty("Anvil.DataSets", "QueryableExtensions")]
    public void DynamicAggregate_test()
    {
        var source = new Anvil.Test.DataSets.TestQueryableDataset();

        var data = source.GetData();

        var expectedTotal = (from d in data select d.Total).Sum();
        var expectedDiscount = (from d in data select d.Discount).Average();
        var expectedCount = (from d in data select d.ID).Count();

        const string prop0 = "Total";
        const string prop1 = "Discount";
        const string prop2 = "ID";

        string sumExpr = string.Format("new ( Sum(it.{0}) as t0, Average(it.{1}) as t1 , Count() as t2)", prop0,prop1, prop2);
        var t = data.GroupBy(x => 1).Select(sumExpr);

        var firstItem = t.Cast<object>().First();

        var ttype = firstItem.GetType();
        var p0 = ttype.GetProperty("t0");
        var p1 = ttype.GetProperty("t1");
        var p2 = ttype.GetProperty("t2");

        decimal actualTotal = (decimal)(p0.GetValue(firstItem));
        decimal actualDiscount = (decimal)(p1.GetValue(firstItem));
        int actualCount = (int)(p2.GetValue(firstItem));

        Assert.AreEqual(expectedTotal, actualTotal);
        Assert.AreEqual(expectedDiscount, actualDiscount);
        Assert.AreEqual(expectedCount, actualCount);
    }

另请参阅:

• 动态Linq GroupBy
• System.LINQ.Dynamic :选择("new(...)")到List< T>中. (或< T>的任何其他可枚举的集合)
• http://developergems.blogspot .co.uk/2012/02/group-by-with-dynamic-linq.html

• Dynamic Linq GroupBy
• System.LINQ.Dynamic: Select(" new (...)") into a List<T> (or any other enumerable collection of <T>)
• http://developergems.blogspot.co.uk/2012/02/group-by-with-dynamic-linq.html

这篇关于动态LINQ在IQueryable上聚合为单个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！