C# Dynamic LINQ: Select with Sum on dictionary indexer

Question

I'm using dynamic LINQ to create a groupby and select on the fly. My items are key/value collections (dictionaries) so they contain no properties (it's a design requirement and can't be changed). I was able to solve the groupby part in another question, but it doesn't seem to work in the select method.

My code below:

    private void GetValuesGroupedBy(List<Dictionary<string, object>> list, List<string> groupbyNames, List<string> summableNames)
    {
        // build the groupby string
        StringBuilder groupBySB = new StringBuilder();
        groupBySB.Append("new ( ");
        bool useComma = false;
        foreach (var name in groupbyNames)
        {
            if (useComma)
                groupBySB.Append(", ");
            else
                useComma = true;

            groupBySB.Append("it[\"");
            groupBySB.Append(name);
            groupBySB.Append("\"]");
            groupBySB.Append(" as ");
            groupBySB.Append(name);
        }
        groupBySB.Append(" )");

        // and now the select string
        StringBuilder selectSB = new StringBuilder();
        selectSB.Append("new ( ");
        useComma = false;
        foreach (var name in groupbyNames)
        {
            if (useComma)
                selectSB.Append(", ");
            else
                useComma = true;

            selectSB.Append("Key.")
                //.Append(name)
                //.Append(" as ")
                .Append(name);
        }
        foreach (var name in summableNames)
        {
            if (useComma)
                selectSB.Append(", ");
            else
                useComma = true;

            selectSB.Append("Sum(")
                .Append("it[\"")
                .Append(name)
                .Append("\"]")
                .Append(") as ")
                .Append(name);
        }
        selectSB.Append(" )");

        var groupby = list.GroupBy(groupBySB.ToString(), "it");
        var select = groupby.Select(selectSB.ToString());
    }

The Key part of the select string is ok but the Sum part doesn't work. Assuming the key I want is called value, I've tried:

"Sum(value)" : ParseException: Expression expected
"Sum(\"value\")" : ParseException: Expression expected
"Sum(it[\"value\"])" : ParseException : No applicable aggregate method 'Sum' exists
"Sum(it[value])" : ParseException : No property or field 'value' exists in type 'Dictionary'
"Sum([\"value\"])" : ParseException: Expression expected

But all have failed. Any ideas?

Thanks! Sean

IMO, the further down the rabbit hole you get with dynamic LINQ, the more you should consider mastering the articulation of expression trees yourself. — Kirk Woll, May 30 '12 at 13:55

score 3 · Answer 1 · answered Jun 05 '13 at 21:49

I faced this issue myself; the problem is that Sum() is seeing the column as type object (which can be applied to Convert(), or Max() even, but not Sum()), and thus is failing; note that it says there is no applicable aggregate function.

The solution is to use an inline conversion to integer. Dynamic LINQ supports this conversion, and can be done as follows in your example:

selectSB.Append("Sum(")
    .Append("Convert.ToInt32(") // Add this...
    .Append("it[\"")
    .Append(name)
    .Append("\"]")
    .Append(")") // ... and this
    .Append(") as ")
    .Append(name);

If your columns aren't int type, I believe ToInt64 (bigint) works, as well as ToDouble() and ToDecimal().

Adam Mills · Answer 2 · 2012-06-29T14:04:09.900

0

You have the correct syntax (my test fields are "One" and "Two")

new ( Key.One, Sum(it["Two"]) as Two )

However there is no linq method Sum(object), you need to tell it what it is Summing (int, float, decimal etc)

The easiest way is to change the dictionary definition to Dictionary<'string, int> assuming you are summing integers.

edited Jun 29 '12 at 14:04

answered Jun 29 '12 at 13:12

Adam Mills

7,719
3
31
47

C# Dynamic LINQ: Select with Sum on dictionary indexer

2 Answers2