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 get some bookand get the first 2 tag( tagentities) from this book ( bookentity). So, Tagsis the navigation propertyobject book.

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 to receive the next SQL query that should 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, EF Profiler shows me that EF generates an unlimited set of results (for example, 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 fragment of the circuit, if you need it

.AsQueryable() firstBook.Tags / .Skip(0), , .

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

:

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] */

---

- Entity Framework 6?

- ...?

!

Answer 1

@hvd, IQueryable<Tag>, firstBook.Tags - IEnumerable<Tag>. , , @hvd.

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

, @hvd:

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

-, EF

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

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

(1) (2) SQL-, .

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

Answer 2

firstBook.Tags - IEnumerable<Tag>. , IQueryable<Tag> , -, .

IQueryable<Tag>. -

Tag firstTag = context.Set<Tag>()
    .Where(tag => tag.Books.Contains(firstBook))
    .OrderBy(tag => tag.Id).Skip(0).Take(1).SingleOrDefault();

. , , , EF.