Error while flattening the IQueryable<T> after GroupBy()

user_14850419 0
Viewed 2354

I am trying to get flattened object from the database, but I get an error. I want to get 2 books for all genres in the database, the code looks like this:

IQueryable<Book> query = _context.Books
                                 .GroupBy(b => b.Genre)
                                 .SelectMany(bc => bc.Select(b => b).Take(2));

Does anyone know what am I doing wrong in here?

I get this exception instead of a result :

The LINQ expression 'bc => bc .AsQueryable() .Select(b => b) .Take(2)' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See go.microsoft.com/fwlink/?linkid=2101038 for more information.

I tried also something like that :

IQueryable<Book> query = _context.Genres.GroupJoin(
     _context.Books,
     g => g,
     b => b.Genre,
     (g, books) => new
     {
         Genre = g,
         BookCollection = books
     }
    ).SelectMany(bc => bc.BookCollection.Select(b => b)
    .Take(2)).Include(b => b.Author).Include(b => b.Rating)
             .Include(b => b.BookISBNs).Include(b => b.Reviews)
             .ThenInclude(r => r.User);
entity-framework linq c#
2 Answers

This attempt moves away from group by, which is restrictive in the database and EF query translation. Instead, we're going for a subquery.

var genres = _context.Books.Select(b => b.Genre).Distinct();
var books =
  from genre in genres
  from book in _context.Books.Where(b => b.Genre == genre).Take(2)
  select book;

var results = books.ToArrayAsync();

Aiming for this kind of translation:

SELECT c.*
FROM
  (SELECT DISTINCT Genre FROM Books) as a,
  (SELECT top 2 b.* FROM Books b WHERE b.Genre = a.Genre) as c
user_81550

So, you want to group the Book entities by Genre, and then get a flat list of books containing two books from each group. I don't think you can express that with a LINQ query using GroupBy() which Entity Framework can translate to SQL.

But its not EF's fault or shortcoming. GROUP BY in SQL is restricted to return only the grouping key(s) and/or any aggregation performed on the group. For example, you can group the books by Genre, and then want the genre and total number of books per group -

var result = dbCtx.Books
    .GroupBy(p => p.Genre)
    .Select(g => new
    {
        Genre = g.Key,       // grouping key
        Count = g.Count()    // aggregation on group
    })
    .ToList();

EF can generate SQL for that -

SELECT [b].[Genre] AS [Key], COUNT(*) AS [Count]
FROM [Books] AS [b]
GROUP BY [b].[Genre]

This reference might help - GroupBy

Since no database structure can represent an IGrouping, GroupBy operators have no translation in most cases. When an aggregate operator is applied to each group, which returns a scalar, it can be translated to SQL GROUP BY in relational databases. The SQL GROUP BY is restrictive too. It requires you to group only by scalar values. The projection can only contain grouping key columns or any aggregate applied over a column.

Therefore, for your query, you have to fetch the books and do the grouping on the client side, like -

var result = dbCtx.Books.AsEnumerable()
    .GroupBy(p => p.Genre)
    .SelectMany(g => g.Select(book => book).Take(2).ToList())
    .ToList();
user_4465190
Related