How to do a Sum using Dynamic LINQ

Question

I have the following which works brilliantly with the dynamic linq library:

string where = "Price < 5";
string orderby = "BookID ASC";
IQueryable<T> MyDataQueryable = _DataRawBase.AsQueryable<T>();
MyDataQueryable = MyDataQueryable.Where(where).OrderBy(orderby);

Now I want to query the MyDataQueryable to do a SUM (and perhaps Average) of certain fields.

How would I go about this?

Something like:

double mysum = MyDataQueryable.Sum("Price");

would be nice...

Caramiriel · Accepted Answer · 2018-07-13T11:21:06.740

Since everything is stringly-typed, you might want to try:

var myDataQueryable = _DataRawBase.AsQueryable<T>()
    .Sum("Price");

With the following extension method:

public static object Sum(this IQueryable source, string member)
{
    if (source == null) throw new ArgumentNullException(nameof(source));
    if (member == null) throw new ArgumentNullException(nameof(member));

    // The most common variant of Queryable.Sum() expects a lambda.
    // Since we just have a string to a property, we need to create a
    // lambda from the string in order to pass it to the sum method.

    // Lets create a ((TSource s) => s.Price ). First up, the parameter "s":
    ParameterExpression parameter = Expression.Parameter(source.ElementType, "s");

    // Followed by accessing the Price property of "s" (s.Price):
    PropertyInfo property = source.ElementType.GetProperty(member);
    MemberExpression getter = Expression.MakeMemberAccess(parameter, property);

    // And finally, we create a lambda from that. First specifying on what
    // to execute when the lambda is called, and finally the parameters of the lambda.
    Expression selector = Expression.Lambda(getter, parameter);

    // There are a lot of Queryable.Sum() overloads with different
    // return types  (double, int, decimal, double?, int?, etc...).
    // We're going to find one that matches the type of our property.
    MethodInfo sumMethod = typeof(Queryable).GetMethods().First(
        m => m.Name == "Sum"
             && m.ReturnType == property.PropertyType
             && m.IsGenericMethod);

    // Now that we have the correct method, we need to know how to call the method.
    // Note that the Queryable.Sum<TSource>(source, selector) has a generic type,
    // which we haven't resolved yet. Good thing is that we can use copy the one from
    // our initial source expression.
    var genericSumMethod = sumMethod.MakeGenericMethod(new[] { source.ElementType });

    // TSource, source and selector are now all resolved. We now know how to call
    // the sum-method. We're not going to call it here, we just express how we're going
    // call it.
    var callExpression = Expression.Call(
        null,
        genericSumMethod,
        new[] {source.Expression, Expression.Quote(selector)});

    // Pass it down to the query provider. This can be a simple LinqToObject-datasource,
    // but also a more complex datasource (such as LinqToSql). Anyway, it knows what to
    // do.
    return source.Provider.Execute(callExpression);
}

Thanks - I saw that article - but is there any other (more succinct) way of getting the sum? — Marcel, Jul 05 '13 at 13:55
Possibly by writing your own extension method, but not by default. — Caramiriel, Jul 05 '13 at 14:03
ok - however, the statement still doesn't seem to work... This is what I typed as a test: var aq = MyDataQueryable.Select("new (Sum(Price) as Total)"); then the error is: No applicable method 'Sum' exists in type 'MyAlbum' (at index 5) — Marcel, Jul 05 '13 at 14:11
Whehee - success!! - I changed it to var test = _DataRawBase.AsQueryable().Sum("Price"); (otherwise problems with casting) but the var contains my sum !! Thanks very much! Going to study this example later tonight. — Marcel, Jul 05 '13 at 15:11
Added some brief information about how it works. Hope it helps, and if you have further questions, please ask. Enjoy — Caramiriel, Jul 08 '13 at 06:16
If you have the Dynamic LINQ library it's possible to write group and aggreate functions using .groupby and .select expressions. See my own question and answer at http://stackoverflow.com/questions/21634630/dynamic-linq-aggregates-on-iqueryable-as-a-single-query — Quango, Feb 08 '14 at 16:52
hi I was trying to add your method to this [lib](https://github.com/castle-it/dynamic-linq-query-builder/issues/25) , I was able to add it too the [QuerBuilder](https://github.com/castle-it/dynamic-linq-query-builder/blob/master/Castle.DynamicLinqQueryBuilder/QueryBuilder.cs) class - how would I invoke it? — Transformer, Nov 08 '18 at 02:36
@transformer I suggest you make a new question for that, if you have something concrete to ask. — Caramiriel, Nov 08 '18 at 07:56

score 1 · Answer 2 · answered Mar 26 '15 at 11:04

1

This code worked for me:

float? fSum = qryQueryableData.Select("Price").Cast<float?>().Sum();

Where "Price" is a column of type "float?"

Explanations available here.

answered Mar 26 '15 at 11:04

Kro

144
1
5

score 0 · Answer 3 · answered May 28 '15 at 20:25

I needed to expand Caramiriel's answer to handle fields as well:

    public static object Sum(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);
        FieldInfo field = source.ElementType.GetField(member);

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

        // Method
        MethodInfo sumMethod = typeof(Queryable).GetMethods().First(
            m => m.Name == "Sum"
                && (property != null || field != null)
                && (property == null || m.ReturnType == property.PropertyType) // should match the type of the property
                && (field == null || m.ReturnType == field.FieldType) // should match the type of the property
                && m.IsGenericMethod);

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

score -1 · Answer 4 · answered Jul 05 '13 at 13:43

-1

Try using a lambda expression:

double mysum = MyDataQueryable.Sum(mdq => mdq.Price);

answered Jul 05 '13 at 13:43

ploni

140
2
15

Thanks - but the idea here is that I do not know what types I am dealing with hence the dynamic linq library. Therefore I am unable to use standard lambda... – Marcel Jul 05 '13 at 13:45

How to do a Sum using Dynamic LINQ

4 Answers4

Linked