SUM and COUNT in single LINQ to SQL query

Question

I'm trying to create the following query in LINQ-TO-SQL.

select count(*), sum( o.CostInCents ) from Orders o
where Flag = true;

I came up with the following LINQ query:

var q = db.Orders
    .Where(o => o.Flag )

var result = q
    .GroupBy(o => 1)
    .Select(g => new MyDTO
    {
        NoOfOrders = g.Count(),
        TotalInCents = g.Sum(o => o.CostInCents )
    })
    .SingleOrDefaultAsync();

Is there a better way?

Is .GroupBy(o => 1) even OK?

The other option would be to do two queries, like below.

var q = db.Orders
   .Where(o => o.Flag );

//No groupBy
var result2 = new MyDTO
{
    NoOfCostedOrders = q.Count(),//hit the db
    TotalInCents = q.Sum(o => o.CostInCents )//hit the db 2nd time
};

How should I judge which approach is better?

Thanks in advance!

In terms of performance i suggest keeping the iqueryable as long as you can still filter, http://stackoverflow.com/a/1578977/3956100 other than that im not entirely sure what you want to accomplish with the code. — Niklas, Oct 07 '16 at 07:23
What is your grouping requirement for the Orders, not very clear, which property you want to use — Mrinal Kamboj, Oct 07 '16 at 07:27
It's ok. The trick is called **group by constant** and is the only LINQ way to produce multiple aggregates with single db query. — Ivan Stoev, Oct 07 '16 at 07:32
@IvanStoev Just reviewed, http://stackoverflow.com/questions/1597181/linq-to-sql-how-to-aggregate-without-a-group-by, looks like it is just to group all data as single unit, what's the benefit of doing so, we can't easily get the result without it. — Mrinal Kamboj, Oct 07 '16 at 07:43
@MrinalKamboj Indeed it's just a way to transform a set to a single group. The benefit is to **allow** using multiple aggregate functions inside the LINQ query which has no grouping, hence normally produces a set. — Ivan Stoev, Oct 07 '16 at 08:29

score 4 · Answer 1 · answered Jan 18 '21 at 18:16

This query can be rewritten in sql format as follows

var orderList = db.Orders.Where(o => o.Flag );
var orderSummary = from o in orderList
                   group o by 1 into p
                   select new
                   {
                      Items = p.Count(),
                      Total = p.Sum( x => x.CostInCents)
                   }

score 1 · Answer 2 · answered Jun 09 '20 at 06:09

1

I think what you are searching for is the following slight adjustment:

var q = db.Orders
   .Where(o => o.Flag).Select(o => o.CostInCents).ToList(); // hit the db here once

//No groupBy
var result2 = new MyDTO
{
    NoOfCostedOrders = q.Count(), // don't hit the db
    TotalInCents = q.Sum() // don't hit the db a 2nd time
};

If you have a question to my adjustment feel free to comment.

answered Jun 09 '20 at 06:09

jonzbonz

69
4

1

very clever... I voted +1. However, the problem is that if you have ten or hundred of thousands, it will return that many records which are bad - very bad. But man, you are thinking outside the box. I like it! – Sam Aug 16 '20 at 13:43

SUM and COUNT in single LINQ to SQL query

2 Answers2

Linked