linq-to-sql

Linq to SQL: how to aggregate without a group by?


I am searching for the Linq-to-SQL equivalent to this query:

SELECT
  [cnt]=COUNT(*),
  [colB]=SUM(colB),
  [colC]=SUM(colC),
  [colD]=SUM(colD)
FROM myTable

This is an aggregate without a group by. I can't seem to find any way to do this, short of issuing four separate queries (one Count and three Sum). Any ideas?


Solution

  • This is what I found seems like you still have to do a group by...can just use constant:

    var orderTotals =
        from ord in dc.Orders
        group ord by 1 into og
        select new
        {
            prop1 = og.Sum(item=> item.Col1),
            prop2 = og.Sum(item => item.Col2),
            prop3 = og.Count(item => item.Col3)
        };
    

    This produces the following SQL, which is not optimal, but works:

    SELECT SUM([Col1]) as [prop1], SUM([Col2]) as [prop2], COUNT(*) AS [prop3]
    FROM (
        SELECT 1 AS [value], [t0].[Col1], [t0].[Col2], [t0].[Col3]
        FROM [table] AS [t0]
        ) AS [t1]
    GROUP BY [t1].[value]