Error while flattening the IQueryable after GroupBy()

Question

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

It might help if you tell us what you think you're doing wrong (or why you think you're doing something wrong). — ProgrammingLlama, Mar 08 '21 at 08:49
Yes, of course, It gives me exception instead of 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 https://go.microsoft.com/fwlink/?linkid=2101038 for more information. — Marta, Mar 08 '21 at 08:54
If it gives you exception, always include the error message in the post. — atiyar, Mar 08 '21 at 08:56
EF fails to translate the code to a DB query. You need to use a method that will force the Query to execute before using `SelectMany` on the result. Try `....GroupBy(b => b.Genre).ToList().SelectMany(...)` — dimitar.d, Mar 08 '21 at 09:01
@Marta "_I expect it return the IQueyrable of Book objects._" - `_context.Books.AsQueryable()` will also give you an `IQueryable`. But you don't really want that. Please, state clearly (in the post) what result you are expecting. — atiyar, Mar 08 '21 at 09:02

Amy B · Accepted Answer · 2021-03-08T14:45:36.160

4

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

edited Mar 08 '21 at 14:45

answered Mar 08 '21 at 12:33

Amy B

108,202
21
135
185

It would actually help me out quite a bit if you test this and it works. I would link back to here from https://stackoverflow.com/a/491832/8155 – Amy B Mar 08 '21 at 12:40

atiyar · Answer 2 · 2021-03-08T13:09:44.137

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

But I want to return result as return await result.ToArrayAsync() in the method Task so it is not workinkg like that, and the second problem is that i need to join to it records from other tables containing book id as FK, like BookIsbn, revievs, and so on. I understand that i can do it by include, but then only for one specific genre. — Marta, Mar 08 '21 at 11:50
@Marta I have updated the answer and the query for flat list. So, try the updated query. Simply replace the `ToList()` with `ToArrayAsync()` to get a `Book[]` with an async call. If you have any other issues, please consider posting new questions for them. — atiyar, Mar 08 '21 at 12:17

Error while flattening the IQueryable after GroupBy()

2 Answers2

Linked