I need to get some statistics from the database, but I can't get it to work as I want. I have two tables, Task and Status, and I need to get the number of Tasks per Status, for example, if I have the statuses New and Closed, and each Task has exactly one Status, I want a list like this:
New 35
Closed 47
This is what I have so far:
var statistics = database.Task
.GroupBy(t => t.StatusId)
.Select(g => new { Status = g.Key, Count = g.Count() }).ToList();
This returns the StatusId and the Task count, so all I need to do is to replace the StatusId with Status.Name. Basically I need to combine the above with a join the table Status and then get the Status.Name. But here I fail, can anyone help me?
Try this:
var statistics = database.Task
.GroupBy(t => new { Id = t.StatusId, Name = t.Status.Name })
.Select(g => new { StatusId = g.Key.Id, StatusName = g.Key.Name, Count = g.Count() })
.ToList();