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?

Viewed 2763

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!

2 Answers
Related