Sum of multiple columns in LINQ query

Question

I have this table: Statistics (id int, col1 int, col2 int, col3 int ...) I want to get the sum of col1 values, col2 values, col3 values, etc. 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); ... 

Thus, it works, but it executes N queries in the database. I would like to complete only one. The only way 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 generates a complex query. this is normal?

UPDATE is 2 execution plans:

Answer 1

In my tests, it generates a fairly simple request, and the resulting execution plan is the same as for a request without 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:

Answer 2

It all depends on your business. You are absolutely right that the generated query is not the best, but are there enough databases for you to fill in the difference? As parameters, you can write and call a stored procedure or an unprocessed request:

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