Return multiple aggregate columns in LINQ

Question

I would like to translate the following SQL to LINQ:

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

I tried this:

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

but get the error message "Bets do not contain a definition for" Amount "and cannot be found the extension method" Amount ", which takes the first argument of the type" Bids ".

How to do it in LINQ?

thanks

FINAL:

Final answer:

 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 can try this. The variable b is an entity (for each iteration), while ctx is an entity that has the necessary extension methods.

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

Answer 2

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

Answer 3

here is an alternative to scartag solution:

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

Although there is no real reason, you cannot just say:

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

It gets into the database twice, but its very readable

Answer 4

You can do this using the aggregate offer .

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

If you use Fx4 + or the dll extension for Fx2, you can also benefit from parallelism with

 Aggregate t In _dataContext.Bids.AsParallel