Sum of multiple columns in a LINQ query

Question

I've this table: Statistics(id int, col1 int, col2 int, col3 int ...) I want to get the sum of the col1 values, col2 values, col3 values etc. A simple sql query:

SELECT SUM([Col1]), SUM([Col2])
FROM [dbo].[Statistics]

but in LINQ:

var val1 = db.Statistics.Sum(x => x.Col1);
var val2 = db.Statistics.Sum(x => x.Col2);
var val3 = db.Statistics.Sum(x => x.Col3);
...

In this way works, but this executes N queries on the database. I would like execute only one. The only way that I found is this:

var result = db.Statistics.GroupBy(x => 1).Select(x => new
{
   val1 = x.Sum(k => k.Col1),
   val2 = x.Sum(k => k.Col2),
   val3 = x.Sum(k => k.Col3),
});

that it generate a complex query. is it normal?

UPDATE these are the 2 execution plans:

enter image description here

We can't tell you if it performs well enough for you. You tell us. **Measure it**. If it performs well enough for you, then it performs well enough for you. If it doesn't, you know it doesn't. — Servy, Jan 07 '15 at 15:31
Possible [duplicate](http://stackoverflow.com/questions/2432281/get-sum-of-two-columns-in-one-linq-query) — Max Brodin, Jan 07 '15 at 15:34

Magnus · Answer 1 · 2015-01-07T15:49:21.623

5

In my tests it generates a fairly simple query and the execution plan produced is the same as for a query without the GroupBy

Linq:

var result = tblSystems.GroupBy (s => 1).Select (s => new
{
    val1 = s.Sum (x => x.fkInfoSubCatID),
    val2 = s.Sum (x => x.fkCompanyID),
    val3 = s.Sum (x => x.eventResult)
});

Generated SQL:

-- Region Parameters
DECLARE @p0 Int = 1
-- EndRegion
SELECT SUM([t1].[fkInfoSubCatID]) AS [val1], SUM([t1].[fkCompanyID]) AS [val2], SUM([t1].[eventResult]) AS [val3]
FROM (
    SELECT @p0 AS [value], [t0].[fkInfoSubCatID], [t0].[fkCompanyID], [t0].[eventResult]
    FROM [dbo].[tblSystem] AS [t0]
    ) AS [t1]
GROUP BY [t1].[value]

Execution plan: enter image description here

edited Jan 07 '15 at 15:49

answered Jan 07 '15 at 15:44

Magnus

45,362
8
80
118

is it the same plan for SELECT SUM([Col1]), SUM([Col2]) FROM [dbo].[Statistics] ? – DevT Jan 07 '15 at 15:47
I can not of course test the query using your database. But it should generate the same plan (for such a simple query). – Magnus Jan 07 '15 at 15:50

score 1 · Answer 2 · answered Jan 07 '15 at 15:35

1

Everything depends on your case. You absolutely right, that generated query not the best one, but does you database big enought to fill the difference? As options, you can write and call stored procedure, or raw query:

var result = db.ExecuteQuery<Dto>("SELECT SUM([Col1]) AS Sum1, SUM([Col2]) AS Sum2 FROM [dbo].[Statistics]")

public class Dto
{
    public int Sum1{get;set;}
    public int Sum2{get;set;}
}

answered Jan 07 '15 at 15:35

Uriil

11,948
11
47
68

yes I can, but this means that linq has a limitation for this kind of query and I was not sure about this – DevT Jan 07 '15 at 15:46

Sum of multiple columns in a LINQ query

2 Answers2