Using IQueryable.GroupBy to count related database entities Given a DbSet&lt;Client&gt; from an Entity Framework 6 DbContext, I need to count the number of related Tickets of each type: Open, Responded, and Resolved.  

The below code works, and results in only one query (according to DbContext.Database.Log), as desired. However, the fact that the call to GroupBy results in an IGrouping&lt;Client, ICollection&lt;Ticket&gt;&gt;, which is also an IEnumerable&lt;ICollection&lt;Ticket&gt;&gt;, requires me to call FirstOrDefault(), even though there is always only one ICollection&lt;Ticket&gt;.  

I've never used GroupBy before, so I'm willing to bet I'm just misusing it.  

public IEnumerable&lt;DashboardItemViewModel&gt; GetItems(IQueryable&lt;Client&gt; clients)
{
    return clients
        .Where(cli =&gt; cli.IsActive)
        .GroupBy(cli =&gt; cli, cli =&gt; cli.Tickets)
        .Select(grp =&gt; new DashboardItemViewModel
        {
            Id = grp.Key.Id,
            Name = grp.Key.Name,
            Open = grp.FirstOrDefault().Count(t =&gt; t.Status == StatusType.Open &amp;&amp; !t.IsArchived),
            Responded = grp.FirstOrDefault().Count(t =&gt; t.Status == StatusType.Responded &amp;&amp; !t.IsArchived),
            Resolved = grp.FirstOrDefault().Count(t =&gt; t.Status == StatusType.Resolved &amp;&amp; !t.IsArchived),
        });
}

public enum StatusType
{
    Open,
    Responded,
    Resolved,
};

public class DashboardItemViewModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Open { get; set; }
    public int Responded { get; set; }
    public int Resolved { get; set; }
}

public class Client
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsActive { get; set; }
    public virtual ICollection&lt;Ticket&gt; Tickets { get; set; }
}

public class Ticket
{
    public int Id { get; set; }
    public StatusType Status { get; set; }
    public bool IsArchived { get; set; }
    public virtual Client Client { get; set; }
}
You don't need the grouping at the Client level. No grouping at all, actually. Don't worry, this is a common mistake when people want grouping/aggregation in child collections. Per client, the Tickets can be counted: 

return clients
    .Where(cli =&gt; cli.IsActive)
    .Select(cli =&gt; new DashboardItemViewModel
    {
        Id = cli.Id,
        Name = cli.Name,
        Open = cli.Tickets.Count(t =&gt; t.Status == StatusType.Open &amp;&amp; !t.IsArchived),
        Responded = cli.Tickets.Count(t =&gt; t.Status == StatusType.Responded &amp;&amp; !t.IsArchived),
        Resolved = cli.Tickets.Count(t =&gt; t.Status == StatusType.Resolved &amp;&amp; !t.IsArchived),
    });


Or in query syntax, so we can benefit from the let statement:

return from cli in clients
    where cli.IsActive
    let activeTickets = cli.Tickets.Where(t =&gt; !t.IsArchived)
    select new DashboardItemViewModel
    {
        Id = cli.Id,
        Name = cli.Name,
        Open = activeTickets.Count(t =&gt; t.Status == StatusType.Open),
        Responded = activeTickets.Count(t =&gt; t.Status == StatusType.Responded),
        Resolved = activeTickets.Count(t =&gt; t.Status == StatusType.Resolved)
    };