CODEWITHSUNDEEP
.netlinqlinq-to-sql
P B
P B

Reputation: 670

LINQ Query with two COUNTs based on different parameters in Where statement

Is there a way to have a single LINQ Query that contains two different counts based on different count criteria? For example, lets say I want to Query "Inboxes" to get a count of the total number of messages present and another count of all the messages that are unread for each Inbox.

So here is the result that I am looking for...
NAME          UNREAD         TOTAL
gbush          10                   20
bobama       30                  100

Can this be done in one query or do I need two queries?

Here is what I have to count the number of Unread items, but where/how do I get the Total items?

var results = from message in context.inbox
              where message.Status = "UNREAD"
              group message by message.Name into g
              select new { Name = g.Key, Unread = g.Count() };

Upvotes: 2

Views: 116

Answers (1)

sloth
sloth

Reputation: 101072

You can use the Count overload that accepts a predicate:

var results = from message in context.inbox
              group message by message.Name into g
              select new 
              { 
                  Name = g.Key, 
                  Unread = g.Count(m => m.Status == "UNREAD"),
                  Total = g.Count()
              };

Upvotes: 3

Related Questions