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

Question

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?

Answer 1

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]

Answer 2

You can do the same query using Lambda expression as follows:

  var orderTotals = db.Orders
                      .GroupBy( i => 1)
                      .Select( g => new
                      {
                           cnt = g.Count(), 
                           ScolB = g.Sum(item => item.ColB), 
                           ScolC = g.Sum(item => item.ColC) 
                      });

Answer 3

Also, see here: https://github.com/dotnet/efcore/issues/27117

I needed a count if items including when they did not exist (count=0), but only adding 'group ~ by 1' acts like an inner join and excludes those.
Adding .DefaultWhenEmpty()] includes the zero counts

from orderType in db.OrderTypes
from aggregate in (
    from ord in (
        from ord in db.Orders
        where ord.OrderType = orderType.OrderType
        select ord
    ).DefaultIfEmpty()
    group ord by 1 into og
    select new {
        SumCol1 = og.Sum(item => item.Col1),
        SumCol2 = og.Sum(item => item.Col2),
        Count = og.Count(item => item != null)
        CountBool3 = og.Count(item => item != null && item.Bool3)
    }
    )
select new {
    orderType.OrderType,
    aggregate.SumCol1,
    aggregate.SumCol2,
    aggregate.Count,
    aggregate.CountBool3,
}