Avatar of Alw1n
Alw1n
 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);
.NET Programming

Avatar of undefined
Last Comment
Alw1n

8/22/2022 - Mon
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Your help has saved me hundreds of hours of internet surfing.
fblack61