Entity Framework/Linq to SQL: Skip & Take

Entity Framework/Linq to SQL: Skip & Take

The following works and accomplishes the simplicity I was looking for:

public IEnumerable<Store> ListStores(Expression<Func<Store, string>> sort, bool desc, int page, int pageSize, out int totalRecords)
{
    List<Store> stores = new List<Store>();
    using (var context = new TectonicEntities())
    {
        totalRecords = context.Stores.Count();
        int skipRows = (page - 1) * pageSize;
        if (desc)
            stores = context.Stores.OrderByDescending(sort).Skip(skipRows).Take(pageSize).ToList();
        else
            stores = context.Stores.OrderBy(sort).Skip(skipRows).Take(pageSize).ToList();
    }
    return stores;
}

The main thing that fixed it for me was changing the Func sort parameter to:

Expression<Func<Store, string>> sort

LINQ to Entities Skip and Take

So whats the best solution?

This is the best solution.

If yes I think this is pretty bad solution

You are right, had it been implemented that way, it would be a pretty bad solution. Fortunately, it is not implemented that way: the values from Skip and Take are passed to your RDBMS server in a way specific to the SQL dialect, at which point the database decides how to find and serve you the records. In case of SQL Server, syntax similar to this one is used:

SELECT ...
FROM ...
WHERE ...
...
OFFSET <skip-value> ROWS
FETCH NEXT <take-value> ROWS ONLY;

Skip() and Take() in Entity Framework

You should first Skip from the whole collection and after that Take.

var outletList = (from c in db.OutletList
                    where c.EmployeeId == 1 
                    orderby c.VisitId descending select c)
                    .Skip(skipQuantity).Take(10);

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?

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

LINQ to Entities - Skip and Take very slow

I got to the end of writing this question, and then struck upon a slightly different approach. I thought I'd post anyway because it may be useful.

I've changed my code structure. I now do an extended .Take() first. This gets me all pages up to, and including, the page I want to return. Then I do the order by and skip to get only the page I want.

        query = query
            .Take((page.GetValueOrDefault(0) + 1) * recordCount.GetValueOrDefault(100));

        // Now skip to the required page. 
        daResults = daResults
            .OrderBy(x => x.Id)
            .Skip(page.GetValueOrDefault(0) * recordCount.GetValueOrDefault(100))
            .ToList();

The original Skip/Take results in the following SQL, which needs that internal query that was previously fully evaluated, and is slow:

ORDER BY row_number() OVER (ORDER BY [Project1].[Id] ASC)
OFFSET 100 ROWS FETCH NEXT 100 ROWS ONLY

Changing it, that internal query is much smaller. That internal sub-query uses SELECT TOP(200), which is lightning fast, and then applies the OFFSET etc. to the reduced results

I'm still only enumerating results (.ToList()) after all this has happened, so it all stays in the database and the results are now pretty much instant again.

Entity Framework: Skip/Take functionality

The accepted answer is mostly correct in describing how emitted queries different though the details can vary. I have commonly seen the entity framework emit the query as "TOP (@maxPageSize) .... WHERE [ROW_NUMBER] > @skip" which is probably the same difference, but I am not 100% certain if it generates the same query execution plan.

The important difference to clearly note is that this can produce differing results when your "ORDER BY" does not contain a unique column name.

In code that I had written, we omitted the "Skip" when the @skip value was 0. We had one entry appear as the final entry. If you then went to the next page where the @skip value was applied, the same entry appeared as the first item on that page. The "tied" item that should have been in at least one of those positions never appeared. Here is the SQL that each emitted:

No skip (generated on page 1):

SELECT TOP ({take number}) [Extent1].[Table1ID] AS [Table1ID], {snip a lot of other columns}
        FROM  [dbo].[Table1] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Table2] AS [Extent2] ON [Extent1].[Table2ID] = [Extent2].[Table2ID]
        WHERE ({my where clause conditions})
        ORDER BY [Extent2].[MySortColumn] ASC

Skip:

SELECT TOP ({take number}) [Filter1].[Table1ID], {snip a lot of other columns}
        FROM ( SELECT [Extent1].[Table1ID] AS [Table1ID], {snip a lot of other columns}, row_number() OVER (ORDER BY [Extent2].[MySortColumn] ASC) AS [row_number]
            FROM  [dbo].[Table1] AS [Extent1]
            LEFT OUTER JOIN [dbo].[Table2] AS [Extent2] ON [Extent1].[Table2ID] = [Extent2].[Table2ID]
            WHERE ({my where clause conditions})
        )  AS [Filter1]
        WHERE [Filter1].[row_number] > {skip number}
        ORDER BY [Filter1].[MySortColumn] ASC

The important take away is to go all the way back to "SQL 101" and remember that without "order by", order is not guaranteed. This difference in emitting causing an item to be duplicated on multiple pages of a grid while another item never shows up at all has made me see that this is even more paramount in Entity Framework where the exact SQL produced is not as directly in your control and can differ in future EF versions unexpectedly.

You can currently avoid this by always including Skip(0) which will emit the second query but with {skip number} simply being zero. This appears to, in my tests, always emit the same ordering for tiebreakers using the default rules of T-SQL. I believe it is not best practice to assume that this will necessarily work in future versions of Entity Framework as such though. I would suggest that instead, you consider exploring a tie-breaking strategy of your own. In my case, it should be possible to break the tie on "Table1ID" as that represents a unique identity primary key column. The attached article gives suggestions for more complicated situations though.

Where does EF linq skip / take execute?

The OrderBy/Skip/Take return an IEnumerable<T>

OrderBy has an overload that returns IOrderedQueryable<T>, not only IOrderedEnumerable<T>. So do Skip and Take. C# compiler is smart enough to prefer these overloads to ones returning IEnumerable<T>, as long as you do not force it to do otherwise. The operation is going to be performed in the database, as long as you do not convert it to IEnumerable too early.

EntityFramework Core Project Joined Rows For Skip/Take Pagination

The query you are looking for using LINQ query syntax is something like this

var query =
    from c in context.Customers
    from o in c.Orders.DefaultIfEmpty()
    select new
    {
        CustomerId = c.Id,
        OrderId = (long?)o.Id,
        Total = (int?)o.Total
    };

Some things to note.

First, DefaultIfEmpty() is what produces left outer join. Without it it would be treated as inner join.

Second, since now Order data is coming from the (optional) right side of the left outer join, you need to take into account that it could be null. In LINQ to Object that would require using conditional operator with null check, or null coalescing operator. In LINQ to Entities this is handled naturally by SQL, but you need to change the result type of non nullable fields to their nullable equivalent. Which in anonymous projections is achieved with explicit cast as shown above.

Finally, why query syntax? Of course it can be written with method syntax (SelectMany as in Steve Py's answer), but since EF Core team seems to be testing against compiler generated LINQ constructs, you can easily hit EF Core bug if you use the "wrong" overload / pattern. "Wrong" here is not really wrong, just something EF Core translator does not take into account. The "proper" here is to use the SelectMany overload with result selector:

var query = context.Customers
    .SelectMany(c => c.Orders.DefaultIfEmpty(), (c, o) => new 
    {
        CustomerId = c.Id,
        OrderId = (long?)o.Id,
        Total = (int?)o.Total
    });

With query syntax you just don't have such issues.

---

Related Topics