Friday, October 1, 2010

LINQ2Obj vs TSQL: GROUP BY and SUM

I tried to use Dynamic LINQ to create pretty ordinary TSQL query:
SELECT COL1 as B1, COL2 as B2, SUM(CONVERT(Money, COL3)) as B3
FROM a-table
GROUP BY COL1, COL2
HAVING(COL1 < 40)
a-table:
COL1  COL2  COL3
11    "12"  "13"
11    "12"  "23"
31    "32"  "33"
31    "32"  NULL
41    "42"  "43"

Expected result:
B1  B2   B3
11  "12" 36
31  "32" 33

It took me quite a long time to figure out how to create such a query in LINQ using a List of objects as source.
Here is first result of my struggles:
using System.Linq.Dynamic;
. . .

var res =
  list.AsQueryable().Where("COL1<40", null)
    .GroupBy("new(COL1 as B1,COL2 as B2)", "it")
    .Select("new(Key, Sum(COL3==null?0:double.Parse(COL3)) as B3)", null);

The "res" gives two records:
[0]    {Key={B1=11, B2=12}, B3=36}
[1]    {Key={B1=31, B2=32}, B3=33}

Then I need to "flatten" this result to produce what I really needed (but this is another task, not shown here)
[0]    {B1=11, B2=12, B3=36}
[1]    {B1=31, B2=32, B3=33}

Please let me know if anyone has a better solution.

No comments:

Post a Comment