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

N Rocking · Accepted Answer · 2013-08-26 19:29:54Z

26

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

answered Aug 26, 2013 at 19:29

N Rocking

3,0852 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Thomas.Benz Over a year ago

This is simple but does the trick. By the way, I'd like to know the purpose of operator ".GroupBy( i => 1)". Why do we need that?

Migwell Over a year ago

How do we know that the aggregate functions (Sum, Count) are calculated in one iteration? Could it not be iterating the group twice for this? (which is something I want to avoid)

Migwell Over a year ago

@Thomas.Benz that expression groups the iterable by a constant, so that all elements of the iterable end up in a single group. Normally with GroupBy you provide a function so that the items are put into multiple different groups

Enigmativity Over a year ago

This is still using two iterations of the group g. It's just avoiding multiple iterations of the source items.

Dai · Accepted Answer · 2020-02-01 05:42:55Z

6

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

edited Feb 1, 2020 at 5:42

Dai

158k31 gold badges314 silver badges438 bronze badges

answered Sep 1, 2011 at 0:02

scartag

17.7k3 gold badges52 silver badges53 bronze badges

5 Comments

BKahuna Over a year ago

That did solve the error (although it seems like an unnecessary step - why is it needed?). There was a problem with my Count(p=> p.BidID) - an error that p.BidID wasn't boolean. I replaced it with Count() and now the query returns the correct sum and count but not as a single row. The same row over and over - I presume as many times as there are rows in the Bids table. How can I return a single row like in my original SQL query?

scartag Over a year ago

@BKahuna your query says "from b in _dataContext.Bids" thats an iteration. and you won't get one result even though u are trying to compute just the sum and count. You can end the lambda expression by calling First(). which will give u only one result like i've done.

scartag Over a year ago

@BKahuna please scroll my answer to the end and see the last call i make. i call the First() method which will ensure that i get only one row back (the sql generated is Top(1) )

Dai Over a year ago

When I do this I get a very inefficient CROSS JOIN SQL query. I need to use the GroupBy( x => 1 ) trick to get an efficient query.

Enigmativity Over a year ago

This is ridiculous as an answer.

saus · Accepted Answer · 2011-09-01 01:03:58Z

0

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

edited Sep 1, 2011 at 1:03

answered Sep 1, 2011 at 0:48

saus

2,19417 silver badges15 bronze badges

Comments

Bill Tür stands with Ukraine · Accepted Answer · 2023-03-30 14:31:21Z

0

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

edited Mar 30, 2023 at 14:31

Bill Tür stands with Ukraine

3,54730 gold badges42 silver badges52 bronze badges

answered Aug 30, 2016 at 15:00

bkqc

1,0319 silver badges35 bronze badges

Collectives™ on Stack Overflow

Return multiple aggregate columns in LINQ

4 Answers 4

4 Comments

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related