Entity Framework 6: Skip() & Take() do not generate SQL, instead the result set is filtered after loading into memory. Or am I doing something wrong?

Question

I have the following code that should gets some book, and retrieve the first 2 tags (Tag entities) from that book (Book entity). So Tags is a navigation property of the Book entity.

using (var context = new FakeEndavaBookLibraryEntities())
{
      Book firstBook = context.Set<Book>().Take(1).First();
      var firstTwoTags = firstBook.Tags.OrderBy(tag => tag.Id).Skip(0).Take(2).ToList();
}

I expect obtaining the following SQL query that has to be generated by EF.

SELECT TOP(2)
       [Extent2].[Id]      AS [Id],
       [Extent2].[Version] AS [Version],
       [Extent2].[Name]    AS [Name]
FROM   [Literature].[BookTagRelation] AS [Extent1]
       INNER JOIN [Literature].[Tag] AS [Extent2]
         ON [Extent1].[TagId] = [Extent2].[Id]
WHERE  [Extent1].[BookId] = 1 /* @EntityKeyValue1 - [BookId] */

Instead, the EF Profiler shows me that the EF is generating unbounded result set (like SELECT * FROM ...)

SELECT [Extent2].[Id]      AS [Id],
       [Extent2].[Version] AS [Version],
       [Extent2].[Name]    AS [Name]
FROM   [Literature].[BookTagRelation] AS [Extent1]
       INNER JOIN [Literature].[Tag] AS [Extent2]
         ON [Extent1].[TagId] = [Extent2].[Id]
WHERE  [Extent1].[BookId] = 1 /* @EntityKeyValue1 - [BookId] */

Here is a scheme fragment if you need it

I also tried to append the .AsQueryable() to firstBook.Tags property and/or remove .Skip(0) method as is shown below, but this didn't help as well.

      var firstTwoTags = firstBook.Tags.AsQueryable().OrderBy(tag => tag.Id).Skip(0).Take(2).ToList();

The same undesired behavior:

SELECT [Extent2].[Id]      AS [Id],
       [Extent2].[Version] AS [Version],
       [Extent2].[Name]    AS [Name]
FROM   [Literature].[BookTagRelation] AS [Extent1]
       INNER JOIN [Literature].[Tag] AS [Extent2]
         ON [Extent1].[TagId] = [Extent2].[Id]
WHERE  [Extent1].[BookId] = 1 /* @EntityKeyValue1 - [BookId] */

---

Have you ever encountered the same problem when working with Entity Framework 6?

Are there any workarounds to overcome this problem or I've designed the query in a wrong way...?

Thanks for any tip!

Answer 1

As @hvd pointed out, I had to work with IQueryable<Tag>, whereas firstBook.Tags navigation property is just a lazy-loaded IEnumerable<Tag>. So here is the solution of my problem, based on the @hvd's answer.

Tag firstTag = context.Set<Tag>() // or even context.Tags
    .Where(tag => tag.Books.Any(book => book.Id == firstBook.Id))
    .OrderBy(tag => tag.Id)
    .Skip(0).Take(1)
    .SingleOrDefault();

So the minor changes of @hvd's solution are: replacing the

.Where(tag => tag.Books.Contains(firstBook)) with

Something that EF understands

1) .Where(tag => tag.Books.Any(book => book.Id == firstBook.Id)).

or

2) .Where(tag => tag.Books.Select(book => book.Id).Contains(firstBook.Id))

Any sequence of code (1) or (2) generates the following SQL query, which is definitely no longer an unbounded result set.

SELECT [Project2].[Id]      AS [Id],
       [Project2].[Version] AS [Version],
       [Project2].[Name]    AS [Name]
FROM   (SELECT [Extent1].[Id]      AS [Id],
               [Extent1].[Version] AS [Version],
               [Extent1].[Name]    AS [Name]
        FROM   [Literature].[Tag] AS [Extent1]
        WHERE  EXISTS (SELECT 1 AS [C1]
                       FROM   [Literature].[BookTagRelation] AS [Extent2]
                       WHERE  ([Extent1].[Id] = [Extent2].[TagId])
                              AND ([Extent2].[BookId] = 1 /* @p__linq__0 */))) AS [Project2]
ORDER  BY [Project2].[Id] ASC
OFFSET 0 ROWS
FETCH NEXT 1 ROWS ONLY