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};
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());
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.
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.
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.