Entity Framework multiple counts with a single query

Question

Sorry if this has been asked, but how can I improve the following with a single call to the database?

var statsModel = new
{
     Total = _db.Messages.Count(),
     Approved = _db.Messages.Count(x => x.Approved),
     Rejected = _db.Messages.Count(x => !x.Approved),
};

I mean I could easily return all Messages and filter down form there, but I might have thousands of messages. Returning the entire table would be an intense query, and thats why I just need to return the count. — TYRONEMICHAEL
– TYRONEMICHAEL, Commented Jan 17, 2012 at 12:42

Arion · Accepted Answer · 2012-01-17 13:07:02Z

14

This might help:

var statsModel =(
        from message in _db.Messages
        group message by 1 into g
        select new
        {
            Total = g.Count(),
            Approved =g.Count (x =>x.Approved),
            Rejected =g.Count (x =>!x.Approved)
        }
    ).FirstOrDefault();

edited Jan 17, 2012 at 13:07

answered Jan 17, 2012 at 13:01

Arion

31.3k10 gold badges74 silver badges89 bronze badges

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

Comments

Jahan Zinedine · Accepted Answer · 2016-08-20 05:18:42Z

11

First of all you can compute the Rejected by Total and Accepted like this:

Rejected = Total - Approved

And for further improvement you can compute both of them in one shot;

from m in _db.Messages
let Total =  _db.Messages.Count()
let Accept = _db.Messages.Count(x => x.Approved == true)
select new {Total , Accept})

UPDATE: a simple hack for now : just take the first row

(from m in _db.Messages
let Total =  _db.Messages.Count()
let Accept = _db.Messages.Count(x => x.Approved == true)
select new {Total , Accept}).Take(1);

But I'm looking for a cleaner one

edited Aug 20, 2016 at 5:18

answered Jan 17, 2012 at 12:41

Jahan Zinedine

14.9k5 gold badges49 silver badges74 bronze badges

1 Comment

TYRONEMICHAEL Over a year ago

Last question related to this. It is working correctly, but returning the {Total,Accept} for each message. So what I did was "from m in _db.Messages let Total = _db.Messages.Count() let Accept = _db.Messages.Count(x => x.Approved == true) select new {Total , Accept}).FirstOrDefaul();

Chris Moschini · Accepted Answer · 2019-10-11 16:48:18Z

5

In C# (rather than LINQ query), async syntax:

var statsModel = await _db.Messages
.GroupBy(m => 1, (g, mm) => new
{
    Total = mm.Count(),
    Approved = mm.Count(m => m.Approved),
    Rejected = mm.Count(m => !m.Approved)
})
.SingleAsync();

answered Oct 11, 2019 at 16:48

Chris Moschini

38.2k19 gold badges167 silver badges197 bronze badges

2 Comments

kemsky Over a year ago

It fails if table is empty, so SingleOrDefault and an additonal if is needed.

Chris Moschini Over a year ago

@kemsky That depends on whether an empty table is Exceptional in your Application.

Collectives™ on Stack Overflow

Entity Framework multiple counts with a single query

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related