Entity-framework – Get sum of two columns in one LINQ query without grouping

entity-frameworklinqlinq-to-sql

I want to sum up three different fields of a table in one query. I want a linq equivalent of this T-SQL:

select sum(fld1), SUM(fld2),  SUM(fld3)   from MyTable where classID = 5

All examples I have found either use a group by or call the query multiple times, once for each field that needs to be summed up.

I have something like this now. But I don't want to add a group by clause to it as it is not required in its T-SQL form.

from a in MyTable
where a.classID == 5
group a by a.classID into g
select new 
{
Sumfld1 =  g.Sum(x => x.fld1 ), 
Sumfld2 = g.Sum(x => x.fld2),
Sumfld3 = g.Sum(x => x.fld3)
}

Any suggestions? Thanks for your time.

Best Answer

As it turns out, in linq-to-sql you can get very close by doing:

from a in MyTable
where a.classID == 5
group a by 0 into g
select new 
{
    Sumfld1 = g.Sum(x => x.fld1), 
    Sumfld2 = g.Sum(x => x.fld2),
    Sumfld3 = g.Sum(x => x.fld3)
}

(note the group by 0)

It does not translate into a sql GROUP BY, but a SELECT of multiple SUMs from a subquery. In terms of execution plan it may even be the same.

Entity Framework still creates a GROUP BY, albeit by 0.

Related Topic