How to select multiple counts grouped by date in EF

Question

I have a table that looks roughly like this:

Date        | Category  | Name
01/02/2014  |    A      | Foo
01/02/2014  |    B      | Bar
02/02/2014  |    A      | Baz
02/02/2014  |    A      | Bla

I am trying to build a query that produces something like this:

Date        | CategoryACount | CategoryBCount
01/02/2014  |       1        |      1
02/02/2014  |       2        |      0

I currently I have stored procedure that loops through the dates and create a temporary table by querying the data one by one, and we are planning to move all application logic out of stored procedure.

How do I produce this query in EF?

Answer 1

If your table looks like this

public class Table
{
   public DateTime Date { get; set; }
   public string  Category { get; set; }
   public string Name { get; set; }
}

You could use a qry like this:

db.Table.GroupBy(c => c.Date)
        .Select(c => new 
                 {
                  Date = c.Key,
                  CatA = c.Where(q => q.Category == "A").Count(),
                  CatB = c.Where(q => q.Category == "B").Count(),
                  })

To test it - use LinqPad and run this:

var lst = new List<Table>();

lst.Add(new Table(){Date = new DateTime(2014,2,1),Category = "A"});
lst.Add(new Table(){Date = new DateTime(2014,2,1),Category = "B"});
lst.Add(new Table(){Date = new DateTime(2014,2,2),Category = "A"});
lst.Add(new Table(){Date = new DateTime(2014,2,2),Category = "A"});


lst.GroupBy(c => c.Date)
   .Select(c => new {
         Date = c.Key,
         CatA = c.Where(q => q.Category == "A").Count(),
         CatB = c.Where(q => q.Category == "B").Count(),
          })
   .Dump();