Dynamic LINQ aggregates on IQueryable as a single query

Question

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 can compute the sum/average/count individually using the code from this article How to do a Sum using Dynamic LINQ

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.

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.

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

Can you please give your existing linq query and tell us what columns you are trying to group and take the results ? — Thanigainathan, Feb 07 '14 at 18:06
if you create aggregate from what you bind on grid, why dont you create aggregate from fetched data in memory ? — Amir Sherafatian, Feb 07 '14 at 18:43
@Thanigainathan there isn't an existing query - this is all generic code for a IQueryable source. The properties are referenced by a column name. See how the other article works to understand what i mean. — Quango, Feb 07 '14 at 20:14
@am1r_5h the grid will be paged, I will only be showing a subset of the data. The totals should be for the whole dataset. If the datasource is thousands of rows it would be very inefficient to load all this into memory. LINQ will use a provider's aggregation (EF, LINQ to SQL do this) so only one row is returned from the server. — Quango, Feb 07 '14 at 20:16

score 2 · Accepted Answer · edited May 23 '17 at 11:55

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

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).

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

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.

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

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.

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);
    }

See also:

score 2 · Answer 2 · edited May 23 '17 at 10:30

You don't need anonymous type. You just need a type with the 3 properties Sum, Count and Average. Sum and Average type aren't known at design time. So, use Object type for these 2 properties. Count is always an int.

public class Aggregation
{
    public Aggregation(object sum, object average, int count)
    {
        Sum = sum;
        Average = average;
        Count = count;
    }
    public object Sum { get; private set; }
    public object Average { get; private set; }
    public int Count { get; private set; }
}

Like the Sum extension method described in the article How to do a Sum using Dynamic LINQ, you can write an Aggregate extension method which compute an Aggregation class instance from a IQueryable collection and a property name. The real difficulty is about determining the Average overload method which match the property type. Overload can't be determined from the return type but from the return type of the lambda expression used as second argument.

For example, if the property type is an int, code has to select the public static double Average<TSource>( this IQueryable<TSource> source, Expression<Func<TSource, int>> selector ) overload.

public static Aggregation Aggregate(this IQueryable source, string member)
{
    if (source == null)
        throw new ArgumentNullException("source");
    if (member == null)
        throw new ArgumentNullException("member");

    // Properties
    PropertyInfo property = source.ElementType.GetProperty(member);
    ParameterExpression parameter = Expression.Parameter(source.ElementType, "s");
    Expression selector = Expression.Lambda(Expression.MakeMemberAccess(parameter, property), parameter);
    // We've tried to find an expression of the type Expression<Func<TSource, TAcc>>,
    // which is expressed as ( (TSource s) => s.Price );

    // Methods
    MethodInfo sumMethod = typeof(Queryable).GetMethods().First(
        m => m.Name == "Sum"
            && m.ReturnType == property.PropertyType // should match the type of the property
            && m.IsGenericMethod);
    MethodInfo averageMethod = typeof(Queryable).GetMethods().First(
        m => m.Name == "Average"
            && m.IsGenericMethod
            && m.GetParameters()[1]
                .ParameterType
                    .GetGenericArguments()[0]
                        .GetGenericArguments()[1] == property.PropertyType);
    MethodInfo countMethod = typeof(Queryable).GetMethods().First(
        m => m.Name == "Count"
            && m.IsGenericMethod);

    return new Aggregation(
        source.Provider.Execute(
            Expression.Call(
                null,
                sumMethod.MakeGenericMethod(new[] { source.ElementType }),
                new[] { source.Expression, Expression.Quote(selector) })),
        source.Provider.Execute(
            Expression.Call(
                null,
                averageMethod.MakeGenericMethod(new[] { source.ElementType }),
                new[] { source.Expression, Expression.Quote(selector) })),
        (int)source.Provider.Execute(
            Expression.Call(
                null,
                countMethod.MakeGenericMethod(new[] { source.ElementType }),
                new[] { source.Expression })));
}

Thanks for the alternative solution. I'm sticking with Dynamic LINQ for now as it's pretty easy to use and won't make multiple database calls.Your method will work but it uses Provider.Execute() on each item so you can only get them individually. — Quango, Feb 27 '14 at 16:41
Yes it's true! I was inspired a little too easily from [How to do a Sum using Dynamic LINQ](http://stackoverflow.com/questions/17490080/how-to-do-a-sum-using-dynamic-linq). In fact, the call to the constructor of the Aggregation class should be encapsulated in the query. — nlips, Mar 17 '14 at 21:15

score 0 · Answer 3 · answered Mar 31 '15 at 18:59

here is my solution for sum, average and min, max .. this is what i have used in one of the projects.

public static object AggregateFunc(this IQueryable source, string function, string member)
{
        if (source == null) throw new ArgumentNullException("source");
        if (member == null) throw new ArgumentNullException("member");

        // Properties
        PropertyInfo property = source.ElementType.GetProperty(member);
        ParameterExpression parameter = Expression.Parameter(source.ElementType, "s");

        // We've tried to find an expression of the type Expression<Func<TSource, TAcc>>,
        // which is expressed as ( (TSource s) => s.Price );

        Type propertyType = property.PropertyType;
        Type convertPropType = property.PropertyType;
        if (function == "Sum")//convert int to bigint
        {
            if (propertyType == typeof(Int32))
                convertPropType = typeof(Int64);
            else if (propertyType == typeof(Int32?))
                convertPropType = typeof(Int64?);
        }
        Expression selector = Expression.Lambda(Expression.Convert(Expression.MakeMemberAccess(parameter, property), convertPropType), parameter);
        //var methods = typeof(Queryable).GetMethods().Where(x => x.Name == function);
        // Method
        MethodInfo aggregateMethod = typeof(Queryable).GetMethods().SingleOrDefault(
            m => m.Name == function
                && m.IsGenericMethod
                && m.GetParameters().Length == 2 && m.GetParameters()[1].ParameterType.GenericTypeArguments[0].GenericTypeArguments[1] == convertPropType);// very hacky but works :)

        MethodCallExpression callExpr;
        // Sum, Average
        if (aggregateMethod != null)
        {
            callExpr = Expression.Call(
                    null,
                    aggregateMethod.MakeGenericMethod(new[] { source.ElementType }),
                    new[] { source.Expression, Expression.Quote(selector) });
            return source.Provider.Execute(callExpr);
        }
        // Min, Max
        else
        {
            aggregateMethod = typeof(Queryable).GetMethods().SingleOrDefault(
                m => m.Name == function
                    && m.GetGenericArguments().Length == 2
                    && m.IsGenericMethod);
            if (aggregateMethod != null)
            {
                callExpr = Expression.Call(
                    null,
                    aggregateMethod.MakeGenericMethod(new[] { source.ElementType, propertyType }),
                    new[] { source.Expression, Expression.Quote(selector) });

                return source.Provider.Execute(callExpr);
            }                
        }
        return null;
    }

Dynamic LINQ aggregates on IQueryable as a single query

3 Answers3

Linked