LINQ group by in Entity Framework Core 3.1

Question

I have a database table to connect data between user and clients.

db: class UserClientCorporate{
 int UserId; 
 User User;
 int ClientCorporateId;
 ClientCorporate ClientCorporate;
}

I want to query to get list of ClientCorporates grouped by userid. I have follow some example on Stack Overflow like Group by in LINQ

and here is my query:

var data3 = from db in _context.UserClientCorporate
            group db.ClientCorporateId by db.UserId into g
            select new { UserId = g.Key, Clients = g.ToList() };

return Ok(await data3.ToListAsync());

When I run this, I got error:

fail: Microsoft.AspNetCore.Server.Kestrel[13] Connection id "0HLT67LJQA4IP", Request id "0HLT67LJQA4IP:0000000F": An unhandled exception was thrown by the application. System.InvalidOperationException: The LINQ expression 'ToList(GroupByShaperExpression: KeySelector: u.UserId, ElementSelector:ProjectionBindingExpression: EmptyProjectionMember )' 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(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

How to solve this problem?

SOLVED ! After I did more research it seems EF Core has limitation doing this query on database server. so I need to get the data first and processed it on my dotnet server (client).

Here is the

var data = await _context.UserClientCorporate.Include(x => x.User).Include( x => x.ClientCorporate).
var res2 = from db in data 
            group db by db.UserId into g
            select new {UserId = g.Key, Clients = g};

Solution 1

Delete this .ToList():

var data3 = from db in _context.UserClientCorporate
            group db.ClientCorporateId by db.UserId into g
            select new { UserId = g.Key, Clients = g };

return Ok(await data3.ToListAsync());

Solution 2

Client side GroupBy is not supported in .netcore 3.1

You may write your query as simple as this:

var data3 = __context.UserClientCorporate.ToList().GroupBy(x => x.UserId);

Code writter in C# is client side.