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();
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)
});
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();
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With