Entity Framework : Group by and Join

Question

Entity Framework : Group by and Join

c# entity-framework-6 group-by join

Question

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?

Hultan

View more on Stack Overflow

Jan Palas · Accepted Answer · 2/2/2016 11:05:49 AM

Accepted Answer

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();

Jan Palas

Entity Framework : Group by and Join

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related