40

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?

Portman
  • 31,785
  • 25
  • 82
  • 101

2 Answers2

47

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]
Portman
  • 31,785
  • 25
  • 82
  • 101
CSharpAtl
  • 7,374
  • 8
  • 39
  • 53
  • How would this be translated to VB.Net? An error occurs on `group ord by 1 into og`. It doesn't like the 1. –  May 15 '22 at 14:14
  • 1
    @kaiyaq I translated it from C# to VB https://converter.telerik.com/ and got Dim orderTotals = db.Orders.GroupBy(Function(i) 1).[Select](Function(g) New With {Key .cnt = g.Count(), Key .ScolB = g.Sum(Function(item) item.ColB), Key .ScolC = g.Sum(Function(item) item.ColC) }) which worked for me. – Grant Johnson May 24 '22 at 18:04
23

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) 
                      });
N Rocking
  • 2,947
  • 2
  • 21
  • 24
  • How do I use orderTotals cleanly in code? Do I have to do a ToList() on the result and then check the list for a content of at least 1 and then use the first element in the list? Something like : var list = orderTotals.ToList(); if(list.Count == 0) { return; } var totalColB = list[0].ScolB; Is there a way I can change the query to return an object of the first result? Or the complete result? – Grant Johnson May 24 '22 at 18:28