C# Dynamic LINQ: Select with Sum on dictionary indexer

Question

C# Dynamic LINQ: Select with Sum on dictionary indexer

c# dynamic-linq linq select

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

sean.net

View more on Stack Overflow

Eric · Accepted Answer · 6/5/2013 9:49:41 PM

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

C# Dynamic LINQ: Select with Sum on dictionary indexer

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator