Exception thrown Getting items from group using fluent api

Question

This is the query I'm trying to accomplish:

var contacts = await dbContext.Contacts
                .GroupBy(o => o.UserId)
                .Select(group => new
                {
                    UserId = group.Key,
                    Contacts = group.ToList()
                }).ToListAsync();

This is the Contact entity:

[Table("Contacts")]
public class WAContact
{
    public int Id { get; set; }
    public string Phone { get; set; }
    public string Name { get; set; }
        
    [NotNull]
    public int UserId { get; set; }
    
    public WAUser User { get; set; }
    
}

This code throws this exception:

.ToList()' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().

I have seen examples doing the ToList() without problem to retrieve group items, but, don't know what is hapenning in my code.

P.D. After some more tests I have noticed that I get same error calling First(), Last(), etc. too. But Count() for example work though. Weird!

Answer 1

This query is not translatable to the SQL. I’ve written small answer for such mistakes and your query is at the top of the list: LINQ to Database: how to group entities properly and GroupBy limitations