count based on lookup in LINQ

Question

I have a table (or entity) named Cases. There is another table CaseStatus_Lookup and the primary key of this table is a foreign key in the Cases table.

What I want to do is: For every status type I want the number of count of cases. For e.g. if status = in progress , I want to know how many cases are in that status.

one other thing: I also want to filter the Cases based on UserID.

I tried several ways in LINQ but could not get vary far. I was wondering if someone could help.

score 0 · Accepted Answer · edited May 23 '17 at 10:34

try Linq .GroupBy

am assuming your entity structure

suppose your Case Entity is like

public class Case
{
  public int Id{get;set;}
  public int CaseStatusId{get;set;}
  public int UserId{get;set;}


  //navigational fields
  public virtual CaseStatus CaseStatus {get;set;}
}

and suppose your CaseStatus entity is like:

public class CaseStatus
{
   public int Id{get;set;}
   public string Name{get;set;}

  //navigational fields..
  public virtual ICollection<Case> Cases{get;set;}

}

then you can do this:

using (myDbContext db = new myDbContext())
{
   var query = db.Cases.GroupBy(case => case.CaseStatus.Name)
              .Select(group => 
                    new { 
                          Name = group.Key,
                          Cases= group.OrderBy(x => x.Id),
                          Count= group.Count()
                        }
                     ).ToList();
   //query will give you count of cases grouped by CaseStatus.


}

similarly you can further filter your result based on userId.

Start to explore about Linq .GroupBy

score 0 · Answer 2 · answered Mar 08 '13 at 09:53

You need a function that returns the sum and takes the status as parameter :- something like below.

MyCaseStatusEnum caseStatus; //Pass your required status
int caseCount = myCases
                .Where(r => r.Status == caseStatus) 
                .GroupBy(p => p.Status)
                .Select(q => q.Count()).FirstOrDefault<int>();

count based on lookup in LINQ

2 Answers2