EF Core - How to group by a nullable column through multiple joins

Question

Given the following models in EF Core 2.2:

public class A
{
    public int Id { get; set; }
    public ICollection<B> Bs { get; set; }
}

public class B
{
    public int Id { get; set; }
    public int AId { get; set; }
    public ICollection<C> Cs { get; set; }
}

public class C
{
    public int Id { get; set; }
    public int BId { get; set; }
    public long? DId { get; set; }
}

public class D
{
    public long Id { get; set; }
}

I would like to perform this query:

from a in context.Set<A>()
from b in a.Bs
from c in b.Cs
where c.DId.HasValue
group c by c.DId.Value into g
select new
{
    g.Key,
    Count = g.Count() - 1
}

But when I try to execute this, I get:

The LINQ expression 'GroupBy(Convert([b.Cs].DId, Int64), [b.Cs])' could not be translated and will be evaluated locally.

I've referenced these:

• LINQ GroupBy translation
• GroupBy

But I can't seem to compose the query correctly. Is this possible on EF Core 2.2? Or is there a better way to get the result I'm looking for?

Answer 1

There is nothing wrong with the way you compose the query. It's just one of the EF Core 2.2 query translation bugs/defects, because group by c.DId works, bit the resulting key type is long?. Also in EF Core 3.1 the query translates properly.

The workaround for 2.2 is to use intermediate anonymous type for grouping key where you perform the nullable to non nullable "conversion". Something like this:

group c by new { Value = c.DId.Value } into g
select new
{
    Key = g.Key.Value,
    Count = g.Count() - 1
}