Entity Framework Group by based on a Nullable Primary Key column

Question

My entity for storing address looks like below and referring to multiple tables as foreign key like country continent etc

public class ContactAddress
{
    [Key]
    public int Id { get; set; }

    [ForeignKey(nameof(Contact))]
    public int ContactId { get; set; }

    public virtual  Contact Contact { get; set; } 
    
    [ForeignKey(nameof(Continent))]
    public int? ContinentId { get; set; }

    public virtual  Continent Continent { get; set; }

    [ForeignKey(nameof(Country))]
    public int? CountryId { get; set; }

    public virtual  Country Country { get; set; }
}

Trying to fetch the count of address values country-wise and continent-wise and map the result to a List<GeoStatsModel> which is like

public class GeoStatsModel
{
    public int Id { get; set; } // Country Id
    public string Name { get; set; }  //Country Name
    public int Count { get; set; }
}           

Managed to generate the query like below

 List<GeoStatsModel> response = new List<GeoStatsModel>();

 response = context.ContactAddresses
                   .Where(a => a.IsPrimary == true && contactIds.Contains(a.ContactId))
                   .GroupBy(a => a.CountryId) 
                   .Select(g => new GeoStatsModel 
                                    {
                                        Id = (int)g.Key, 
                                        Name = (g.First().Country!=null)? g.First().Country.Name: "",
                                        Count = g.Count() 
                                    }).ToList(); 

At the time of execution, I get the error:

The LINQ expression 'GroupByShaperExpression: KeySelector: (Nullable)c.CountryId, ElementSelector:EntityShaperExpression: EntityType: ContactAddress ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember IsNullable: False

.Select(s => s.Country) .First()' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

How can we correctly return the name from foreign key table and group the results?

Answer 1

GroupBy support is limited in EF core. It basically supports statements that are supported by SQL, in EF core 6 a bit more.

In SQL it's not possible to group by CountryId and have the country's name in the result set too (at least not in all RDBMS's). To do it in SQL, the statement should join with Country and then group by CountryId and Country.Name. That's what EF also supports:

var response = context.ContactAddresses
    .Where(a => a.IsPrimary && contactIds.Contains(a.ContactId))
    .GroupBy(a => new { a.CountryId, a.Country.Name }) 
    .Select(g => new GeoStatsModel 
    {
        Id = g.Key.CountryId ?? 0, 
        Name = g.Key.Name ?? string.Empty,
        Count = g.Count() 
    }).ToList();