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

Answer 2

This is issue . Contacts = group.ToList()

why not change code like

var grouprs= await dbContext.Contacts.Select(c=>c)
                .GroupBy(o => o.UserId);
           

Answer 3

You are getting this exception because EF couldn't translate the LINQ to equivalent SQL.

Change your query to this

// Load the Contact from the DB
var contacts = await dbContext.Contacts.ToListAsync();
// Perform the group by in memory
var userContacts = contacts
.GroupBy(o => o.UserId)
.Select(group => new
{
    UserId = group.Key,
    Contacts = group.Select(contact => new 
    {
      contact.Name,
      contact.Phone,
      contact.Id
    }).ToList()
}).ToList();

Now EF will be able to translate the LINQ to proper SQL.

Answer 4

You can achieve the query you want in multiple ways, depending on the outcome you desire:

A) Return all WAContact entities

Because every entity must have a UserId, there is no need to actually query the WAUsers table:

var userIdsWithContactsWithoutJoin = context.Contacts
    .AsEnumerable()
    .GroupBy(c => c.UserId)
    .ToList();

The code just performs a SELECT, then switches to client-evaluation to group the returned data in memory:

SELECT `c`.`Id`, `c`.`Name`, `c`.`Phone`, `c`.`UserId`
FROM `Contacts` AS `c`

B) Return all WAUser Ids only with related WAContact entities (full)

var userIdsWithContacts = context.Users
    .SelectMany(
        u => context.Contacts
            .Where(c => u.Id == c.UserId),
        (u, c) => new
        {
            c.UserId,
            Contact = c
        })
    .AsEnumerable()
    .GroupBy(j => j.UserId, j => j.Contact)
    .ToList();

The code first performs an INNER JOIN, then switches to client-evaluation to group the returned data in memory:

SELECT `c`.`UserId`, `c`.`Id`, `c`.`Name`, `c`.`Phone`
FROM `Users` AS `u`
INNER JOIN `Contacts` AS `c` ON `u`.`Id` = `c`.`UserId`

C) Return all WAUser entities (full) with or without related WAContact entities (full)

var usersWithOrWithoutContacts = context.Users
    .SelectMany(
        u => context.Contacts
            .Where(c => u.Id == c.UserId)
            .DefaultIfEmpty(),
        (u, c) => new
        {
            User = u,
            Contact = c
        })
    .AsEnumerable()
    .GroupBy(j => j.User, j => j.Contact)
    .ToList();

The code first performs a LEFT JOIN, then switches to client-evaluation to group the returned data in memory:

SELECT `u`.`Id`, `u`.`Name`, `c`.`Id`, `c`.`Name`, `c`.`Phone`, `c`.`UserId`
FROM `Users` AS `u`
LEFT JOIN `Contacts` AS `c` ON `u`.`Id` = `c`.`UserId`

---

All three queries return as little data as possible and then use AsEnumerable() to switch to client-evaluation to perform the actual grouping in memory.

---

Sample program

Here is a fully working sample project, that demonstrates the queries (including checks):

using System.ComponentModel.DataAnnotations.Schema;
using System.Diagnostics;
using System.Diagnostics.CodeAnalysis;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Infrastructure;

namespace IssueConsoleTemplate
{
    [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; }
    }

    [Table("Users")]
    public class WAUser
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    // 
    // DbContext:
    // 

    public class Context : DbContext
    {
        public DbSet<WAContact> Contacts { get; set; }
        public DbSet<WAUser> Users { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseMySql(
                    "server=127.0.0.1;port=3306;user=root;password=;database=So64391764",
                    b => b.ServerVersion("8.0.21-mysql")
                          .CharSetBehavior(CharSetBehavior.NeverAppend))
                .UseLoggerFactory(
                    LoggerFactory.Create(
                        b => b
                            .AddConsole()
                            .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<WAUser>()
                .HasData(
                    new WAUser {Id = 1, Name = "John"},
                    new WAUser {Id = 2, Name = "Jane"},
                    new WAUser {Id = 3, Name = "Mike"});

            modelBuilder.Entity<WAContact>()
                .HasData(
                    new WAContact {Id = 11, Name = "John's First Contact", Phone = "12345", UserId = 1},
                    new WAContact {Id = 12, Name = "John's Second Contact", Phone = "23456", UserId = 1},
                    new WAContact {Id = 21, Name = "Jane's Only Contact", Phone = "09876", UserId = 2});
        }
    }

    internal class Program
    {
        private static void Main()
        {
            using var context = new Context();

            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();
            
            // Return all WAContact entities. Because every entity must have a UserId,
            // there is no need to actually query the WAUsers table.
            // Just performs a SELECT, then switches to client-evaluation to group the returned
            // data in memory:
            //     SELECT `c`.`Id`, `c`.`Name`, `c`.`Phone`, `c`.`UserId`
            //     FROM `Contacts` AS `c`

            var userIdsWithContactsWithoutJoin = context.Contacts
                .AsEnumerable()
                .GroupBy(c => c.UserId)
                .ToList();

            Debug.Assert(userIdsWithContactsWithoutJoin.Count == 2);
            Debug.Assert(userIdsWithContactsWithoutJoin[0].Key == 1);
            Debug.Assert(userIdsWithContactsWithoutJoin[0].Count() == 2);
            Debug.Assert(userIdsWithContactsWithoutJoin[0].First().Name == "John's First Contact");

            // Return all WAUser Ids only with related WAContact entities (full).
            // First performs an INNER JOIN, then switches to client-evaluation to group the
            // returned data in memory:
            //     SELECT `c`.`UserId`, `c`.`Id`, `c`.`Name`, `c`.`Phone`
            //     FROM `Users` AS `u`
            //     INNER JOIN `Contacts` AS `c` ON `u`.`Id` = `c`.`UserId`

            var userIdsWithContacts = context.Users
                .SelectMany(
                    u => context.Contacts
                        .Where(c => u.Id == c.UserId),
                    (u, c) => new
                    {
                        c.UserId,
                        Contact = c
                    })
                .AsEnumerable()
                .GroupBy(j => j.UserId, j => j.Contact)
                .ToList();

            Debug.Assert(userIdsWithContacts.Count == 2);
            Debug.Assert(userIdsWithContacts[0].Key == 1);
            Debug.Assert(userIdsWithContacts[0].Count() == 2);
            Debug.Assert(userIdsWithContacts[0].First().Name == "John's First Contact");

            // Return all WAUser entities (full) with or without related WAContact entities (full).
            // First performs a LEFT JOIN, then switches to client-evaluation to group the returned
            // data in memory:
            //     SELECT `u`.`Id`, `u`.`Name`, `c`.`Id`, `c`.`Name`, `c`.`Phone`, `c`.`UserId`
            //     FROM `Users` AS `u`
            //     LEFT JOIN `Contacts` AS `c` ON `u`.`Id` = `c`.`UserId`

            var usersWithOrWithoutContacts = context.Users
                .SelectMany(
                    u => context.Contacts
                        .Where(c => u.Id == c.UserId)
                        .DefaultIfEmpty(),
                    (u, c) => new
                    {
                        User = u,
                        Contact = c
                    })
                .AsEnumerable()
                .GroupBy(j => j.User, j => j.Contact)
                .ToList();

            Debug.Assert(usersWithOrWithoutContacts.Count == 3);
            Debug.Assert(usersWithOrWithoutContacts[0].Key.Name == "John");
            Debug.Assert(usersWithOrWithoutContacts[0].Count() == 2);
            Debug.Assert(usersWithOrWithoutContacts[0].First().Name == "John's First Contact");
        }
    }
}

You can also run this .NET Fiddle (but with SQL Server instead of MySQL).

---

Further information

For general information about GROUP BY queries, take a look at Complex Query Operators.