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:

Solution 1

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:

Solution 2

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;}
}

Comments

[Servy]

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.

[DevT]

yes I can, but this means that linq has a limitation for this kind of query and I was not sure about this