locked
EF CORE 2.1 GroupBy translations RRS feed

  • Question

  • User1961793307 posted

    Hello guys 

    I updated my project to EF Core 2.1 and i'm updating a query to use GroupBy translations . I run in to the problem 

    The LINQ expression 'GroupBy(new <>f__AnonymousType19`2(ClientId = [b].ClientId, ClientName = [b.Client].ClientName), new <>f__AnonymousType18`2(b = [b], m = [m]))' could not be translated and will be evaluated locally.

    This is a SQL query that i'm currently  using:

    SELECT b.ClientId, c.ClientName
    "FROM dbo.Blocks b 
    "JOIN dbo.Clients c ON b.ClientId = c.ClientId
    "JOIN Memberships m ON b.ClientId = m.ClientId AND b.CategoryId = m.CategoryId
    "WHERE b.Reviewed IS NULL
    "GROUP BY b.ClientId, c.ClientName

    LINQ query that does not translate Group By into SQL

    var clientsWithReviewData = await _context.Blocks
    .Join(_context.Memberships , b => new { b.ClientId, b.CategoryId }, m => new { m.ClientId, m.CategoryId }, (b, m) => new {b, m})
    .Where(w => w.b.Reviewed == null)
    .GroupBy(g => new { g.b.ClientId, g.b.Client.ClientName})
    .Select(s => new
    {
    Id = s.Key.ClientId,
    Value = s.Key.ClientName
    })
    .OrderBy(o => o.Value)
    .ToListAsync();

    Wednesday, June 20, 2018 8:49 PM

Answers

  • User1168443798 posted

    Hi lgorsky,

    Currently, GroupBy is not full supported under EF Core.

    Making groups of multiple entityTypes using anonymous types is not supported in EF Core 2.1

    #Reference:

    https://github.com/aspnet/EntityFrameworkCore/issues/2341

    For your current query, it seems you only want to return Blocks.Clientid and Blocks.Client.ClientName.

    If so, I suggest you try code below:

                var clientsWithReviewData = await _context.Block
                    .Join(_context.Membership, b => new { b.ClientId, b.CategoryId }, m => new { m.ClientId, m.CategoryId }, (b,m) => b)                        
                            .Where(w => w.Reviewed == null)
                            .GroupBy(g => new { g.ClientId, g.Client.ClientName })
                            .Select(s => new
                            {
                                Id = s.Key.ClientId,
                                Value = s.Key.ClientName
                            })
                            .OrderBy(o => o.Value)
                            .ToListAsync();
    

    Best Regards,

    Edward

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 21, 2018 7:45 AM

All replies

  • User2053451246 posted

    Still in preview:

    Another unexpected limitation of EF Core is its inability to generate GROUP BY operations in SQL. Currently the whole dataset is transferred to the client, after which EF Core will perform the grouping operations in memory. This can cause significantly higher network, memory, and CPU utilization when compared to performing these operations in the database.

    Until EF Core 2.1 is released, the workaround is to perform the grouping operation in a view or inline SQL. You can then use the workaround described above to treat the view as a table.

    Reference: https://www.infoq.com/news/2018/02/EF-Core-2.1-Road-Map

    Wednesday, June 20, 2018 8:57 PM
  • User1961793307 posted

    Thank you for quick response. According to this blog EF Core 2.1 is released.

    https://blogs.msdn.microsoft.com/dotnet/2018/05/30/announcing-entity-framework-core-2-1/

    Wednesday, June 20, 2018 9:03 PM
  • User1168443798 posted

    Hi lgorsky,

    Currently, GroupBy is not full supported under EF Core.

    Making groups of multiple entityTypes using anonymous types is not supported in EF Core 2.1

    #Reference:

    https://github.com/aspnet/EntityFrameworkCore/issues/2341

    For your current query, it seems you only want to return Blocks.Clientid and Blocks.Client.ClientName.

    If so, I suggest you try code below:

                var clientsWithReviewData = await _context.Block
                    .Join(_context.Membership, b => new { b.ClientId, b.CategoryId }, m => new { m.ClientId, m.CategoryId }, (b,m) => b)                        
                            .Where(w => w.Reviewed == null)
                            .GroupBy(g => new { g.ClientId, g.Client.ClientName })
                            .Select(s => new
                            {
                                Id = s.Key.ClientId,
                                Value = s.Key.ClientName
                            })
                            .OrderBy(o => o.Value)
                            .ToListAsync();
    

    Best Regards,

    Edward

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 21, 2018 7:45 AM
  • User1961793307 posted

    That's exactly what i need it.

    Thank you very much. 

    Tuesday, June 26, 2018 4:03 PM