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