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!

Answer 1

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

Answer 2

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.