Dynamic LINQ aggregates on IQueryable as a single query

c# dynamic-linq linq

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?

1
2
5/23/2017 11:55:15 AM

Accepted Answer

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:

2
5/23/2017 11:55:16 AM

Popular Answer

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


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow