asked on

Linq->EF lambda syntax for sum & group by

Hi,
I am struggling with the EF lambda syntax for the following 2 traditional SQL's:

1. select sum(FIELD1), sum(FIELD2) from MYTABLE where FIELD3 = 3
2. select FIELD1, sum(FIELD2), sum(FIELD3) from MYTABLE group by FIELD1

I kinda get the basics of the syntax below but how do I get this all into one data set like the SQL's above would produce?:
var x = DBContext.MYTABLE.Where(a => a.FIELD3 == 3);
var d1 = x.Sum(p1 => p1.FIELD1);
var d2 = x.Sum(p2 => p2.FIELD2);

kaufmed

Try:

var x = from a in DBContext.MYTABLE
        where a.FIELD3 == 3
        group a by a.FIELD3 into g
        select new
        {
            d1 = g.Sum(p1 => p1.FIELD1),
            d2 = g.Sum(p2 => p2.FIELD2)
        };

Open in new window

Alw1n

ASKER

Thanks kuafmed, that is close but it's not quite the same:

I can't see how to get the sums without using group by in this:
1. select sum(FIELD1), sum(FIELD2) from MYTABLE where FIELD3 = 3

Your example works fine for this sql except that I can't see how to return
FIELD1 along with the 'sums'
2. select FIELD1, sum(FIELD2), sum(FIELD3) from MYTABLE group by FIELD1

ASKER CERTIFIED SOLUTION

kaufmed

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alw1n

ASKER

Great, thanks. I didn't think of using a group like 'group a by a into g' because you can SUM in raw SQL without using a group as long as you only SELECT the SUMMED fields

Linq-&gt;EF lambda syntax for sum &amp; group by

Linq->EF lambda syntax for sum & group by