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};

Leatherwood

The error says it: switch to client evaluation explicitly. This specific query might not be able to be converted to SQL by LINQ. Either you have to reconstruct your query, write in bare SQL or use client side evaluation with AsEnumerable. – Kipkipling

yes you were right. – Leatherwood

Answer 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());

Bright

still failed fail: Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware[1] An unhandled exception has occurred while executing the request. System.InvalidOperationException: Processing of the LINQ expression 'GroupByShaperExpression: KeySelector: u.UserId, ElementSelector:ProjectionBindingExpression: EmptyProjectionMember ' by 'RelationalProjectionBindingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See go.microsoft.com/fwlink/?linkid=2101433 for more detailed information. – Leatherwood

as I do more research, this query was not doable in EF Core, so I grab the data first and processed later with this LINQ query. and it works. – Leatherwood

Yes, you are right. This is because most DB engines require aggregator function to be applied to a grouped data or return the first row in a group as MySQL does it. Which is completely different from the behaviour that IEnumerable group by has. – Bright

This should not be marked as the answer, as nightingale2k1 found a different solution – Romanic

Answer 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.

Declension

This is a real problem for those working with large data sets – Prostration

group by is supported client side. just implement your getHashcode to get the comparisons right. Also code written in C# isn't automatically client side executed (LINQ=> SQL). – Infecund

@Infecund I don't see anything useful in your comment. Anyways stack overflow has given you the right to oppose and downvote. – Declension