Return multiple aggregate columns in LINQ

Question

I would like to translate the following SQL into LINQ:

SELECT
    (Select count(BidID)) as TotalBidNum,
    (Select sum(Amount)) as TotalBidVal
FROM Bids

I've tried this:

from b in _dataContext.Bids
select new { TotalBidVal = b.Sum(p => p.Amount), TotalBidNum = b.Count(p => p.BidId) }

but get an error "Bids does not contain a definition for "Sum" and no extension method "Sum" accepting a first argument of type "Bids" could be found.

How can I do this in LINQ?

Thanks

CONCLUDING:

The final answer was:

var ctx = _dataContext.Bids;

var itemsBid = (from b in _dataContext.Bids
               select new { TotalBidVal = ctx.Sum(p => p.Amount), TotalBidNum = ctx.Count() }).First();

Answer 1

You could do it using the Aggregate Clause.

Aggregate t In _dataContext.Bids
Into TotalBidNum = Count(BidID),
     TotalBidVal = Sum(Amount)

If you're using Fx4+ or an extension dll for Fx2, you could also benfit from parallelism by using

Aggregate t In _dataContext.Bids.AsParallel

Answer 2

here's an alternative to scartag's solution:

(from b in _dataContext.Bids.Take(1)
select new 
{
    TotalBidVal = _dataContext.Bids.Sum(p => p.Amount), 
    TotalBidNum = _dataContext.Bids.Count()
}).Single();

Although there's no real reason you can't just say:

var result = new 
{
    TotalBidVal = _dataContext.Bids.Sum(p => p.Amount), 
    TotalBidNum = _dataContext.Bids.Count()
};

It hits the database twice, but its very readable

Answer 3

You could try this out. The variable b is an entity (for every iteration) while ctx is an entityset which has the extension methods you need.

var ctx = _dataContext.Bids;

var result = ctx.Select(x => new {TotalBidVal = ctx.Sum(p => p.Amount),TotalBidNum = ctx.Count(p => p.BidId)}).First();

Answer 4

You can write this query using GroupBy. The Lambda expression is as follows:

    var itemsBid = db.Bids
                     .GroupBy( i => 1)
                     .Select( g => new
                     {
                          TotalBidVal = g.Sum(item => item.Amount), 
                          TotalBidNum = g.Count(item => item.BidId)
                     });