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?

c# entity-framework-6 profiler

Question

The code I have below should generate somebook and get the first twotag s (Tag entities) mentioned in the work (Book entity). So Tags is anavigation property of theBook 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 am getting the SQL query that EF needs to produce.

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, I can see from the EF Profiler that the EF is producing arbitrary result set (like SEARCH FOR * IN..)

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

If you need a portion of a scheme, go here.

Moreover, I attempt to attach the.AsQueryable() to firstBook.Tags property, or take away.Skip(0) manner, but also It did not assist. as well, as is seen below.

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

same unfavourable conduct

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

working with Entity Framework 6 cause the same issue?

Are there any alternatives? to solve this issue, or did I construct the query incorrectly...?

Thank you for any advice!

1
5
7/1/2017 8:18:04 PM

Accepted Answer

As @hvd noted, I was forced to deal withIQueryable<Tag> , whilefirstBook.Tags Just a lazy-loaded number, navigational featuresIEnumerable<Tag> . So, based on the response from @hvd, this is the resolution to my issue.

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

The following are the minor adjustments to @hvd's solution:

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

The following SQL query, which is unquestionably not anymore or arbitrary result set, is produced by any combination of code (1) or (2).

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
4
7/3/2017 5:01:18 PM

Popular Answer

firstBook.Tags is a carelessly loadedIEnumerable<Tag> . All tags are loaded on the first access, and future attempts to make it into anIQueryable<Tag> labour is useless since you didn't begin with a foundation that allowed for logical inquiry.

Start with a known good instead.IQueryable<Tag> Possibly along the lines of

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

should operate To make the filter condition something EF can comprehend, you may just need to make a little adjustment.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow