Entity Framework/Linq to SQL: Skip & Take

entity framework skip/take paging
ef core skip/take slow
entity framework paging
sql skip take
linq skip first
entity framework paging performance
entity framework vs sql query performance
entity framework issues

Just curious as to how Skip & Take are supposed to work. I'm getting the results I want to see on the client side, but when I hook up the AnjLab SQL Profiler and look at the SQL that is being executed it looks as though it is querying for and returning the entire set of rows to the client.

Is it really returning all the rows then sorting and narrowing down stuff with LINQ on the client side?

I've tried doing it with both Entity Framework and Linq to SQL; both appear to have the same behavior.

Not sure it makes any difference, but I'm using C# in VWD 2010.

Any insight?

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

Resulting SQL (Note: I'm excluding the Count query):

SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[Name] AS [Name], 
[Extent1].[LegalName] AS [LegalName], 
[Extent1].[YearEstablished] AS [YearEstablished], 
[Extent1].[DiskPath] AS [DiskPath], 
[Extent1].[URL] AS [URL], 
[Extent1].[SecureURL] AS [SecureURL], 
[Extent1].[UseSSL] AS [UseSSL]
FROM [dbo].[tec_Stores] AS [Extent1]

After some further research, I found that the following works the way I would expect it to:

public IEnumerable<Store> ListStores(Func<Store, string> sort, bool desc, int page, int pageSize, out int totalRecords)
{
    var context = new TectonicEntities();
    totalRecords = context.Stores.Count();
    int skipRows = (page - 1) * pageSize;           
    var qry = from s in context.Stores orderby s.Name ascending select s;
    return qry.Skip(skipRows).Take(pageSize);           
}

Resulting SQL:

SELECT TOP (3) 
[Extent1].[ID] AS [ID], 
[Extent1].[Name] AS [Name], 
[Extent1].[LegalName] AS [LegalName], 
[Extent1].[YearEstablished] AS [YearEstablished], 
[Extent1].[DiskPath] AS [DiskPath], 
[Extent1].[URL] AS [URL], 
[Extent1].[SecureURL] AS [SecureURL], 
[Extent1].[UseSSL] AS [UseSSL]
FROM ( SELECT [Extent1].[ID] AS [ID], [Extent1].[Name] AS [Name], [Extent1].[LegalName] AS [LegalName], [Extent1].[YearEstablished] AS [YearEstablished], [Extent1].[DiskPath] AS [DiskPath], [Extent1].[URL] AS [URL], [Extent1].[SecureURL] AS [SecureURL], [Extent1].[UseSSL] AS [UseSSL], row_number() OVER (ORDER BY [Extent1].[Name] ASC) AS [row_number]
    FROM [dbo].[tec_Stores] AS [Extent1]
)  AS [Extent1]
WHERE [Extent1].[row_number] > 3
ORDER BY [Extent1].[Name] ASC

I really like the way the first option works; Passing in a lambda expression for sort. Is there any way to accomplish the same thing in the LINQ to SQL orderby syntax? I tried using qry.OrderBy(sort).Skip(skipRows).Take(pageSize), but that ended up giving me the same results as my first block of code. Leads me to believe my issues are somehow tied to OrderBy.

====================================

PROBLEM SOLVED

Had to wrap the incoming lambda function in Expression:

Expression<Func<Store,string>> sort

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

Optimizing LINQ to SQL Skip/Take, Optimizing LINQ to SQL Skip/Take. Written by Bill Given a LINQ statement like: context.Cars .OrderBy(x => x.Id) .Skip(50000) .Take(1000) .ToList();. This roughly gets How to avoid NullReferenceException in C# .NET. LINQ to SQL - order by, group by and order by each group with skip and take. c# entity-framework-6 linq linq-to-sql. This is an extension of already answered question by Jon Skeet that you can find here. The desired result is following: A 100 A 80 B 80 B 50 B 40 C 70 C 30.

As long as you don't do it like queryable.ToList().Skip(5).Take(10), it won't return the whole recordset.

Take

Doing only Take(10).ToList(), does a SELECT TOP 10 * FROM.

Skip

Skip works a bit different because there is no 'LIMIT' function in TSQL. However it creates an SQL query that is based on the work described in this ScottGu blog post.

If you see the whole recordset returned, it probably is because you are doing a ToList() somewhere too early.

Speed Up Skip and Take in Entity Framework with Lambda , If you're using Skip and Take in LINQ to page through your data, tree for your LINQ statement and submitting a new SQL statement on each request. The benefit here is that LINQ+EF will now recognize your Skip and Take  Luckily, Entity Framework 6 includes a lambda version of the Skip() and Take() extensions (available in System.Data.Entity.QueryableExtensions). These generate LINQ expression trees that retain whether a value came from a constant or variable.

Entity Framework 6 solution here...

http://anthonychu.ca/post/entity-framework-parameterize-skip-take-queries-sql/

e.g.

using System.Data.Entity;
....

int skip = 5;
int take = 10;

myQuery.Skip(() => skip).Take(() => take);

Parameterize Skip and Take SQL Queries with Entity Framework, How SQL queries are generated by Entity Framework depends largely on how the LINQ queries are written. One example of this is how EF  Picture a scenario where you want to page a large dataset and your LINQ statement has several Include-calls. The first few pages load fairly quickly, but the Optimizing LINQ to SQL Skip/Take

I created simple extension:

public static IEnumerable<T> SelectPage<T, T2>(this IEnumerable<T> list, Func<T, T2> sortFunc, bool isDescending, int index, int length)
{
    List<T> result = null;
    if (isDescending)
        result = list.OrderByDescending(sortFunc).Skip(index).Take(length).ToList();
    else
        result = list.OrderBy(sortFunc).Skip(index).Take(length).ToList();
    return result;
}

Simple use:

using (var context = new TransportContext())
{
    var drivers = (from x in context.Drivers where x.TransportId == trasnportId select x).SelectPage(x => x.Id, false, index, length).ToList();
}

Take and Skip Operator In LINQ to SQL, Take and Skip Operator In LINQ to SQL. In this article I am going to explain the Take and Skip operators in LINQ to SQL. The Take operator is used to return a given number of rows from a database table and the Skip operator skips over a specifed number of rows in a database table. Entity Framework: Overview. LINQ to Entities (ADO.Net Entity Framework) is an ORM (Object Relational Mapper) API which allows for a broad definition of object domain models and their relationships to many different ADO.Net data providers. The Entity Framework includes LINQ to Entities which exposes many of the same features as LINQ to SQL

Try this:

public IEnumerable<Store> ListStores(Func<Store, string> sort, bool desc, int page, int pageSize, out int totalRecords)
{
    var context = new TectonicEntities();
    var results = context.Stores;

    totalRecords = results.Count();
    int skipRows = (page - 1) * pageSize;

    if (desc)
        results = results.OrderByDescending(sort);

    return results.Skip(skipRows).Take(pageSize).ToList();
}

in truth, that last .ToList() isn't really necessary as you are returning IEnumerable...

There will be 2 database calls, one for the count and one when the ToList() is executed.

SKIP (Entity SQL), For example, SKIP 5 will skip the first five rows and return from the sixth row forward. Note. An Entity SQL query is invalid if both the TOP modifier  If you're using Entity Framework 6, then you can switch to using lambda expressions with Skip and Take. The benefit here is that LINQ+EF will now recognize your Skip and Take values as parameters that can be passed to a compiled query. The change looks like this in Visual Basic: Dim sos = db.SalesOrders.Skip(Function() pos).Take(Function() size)

Return Or Skip Elements in a Sequence, LINQ to SQL translates Skip by using a subquery with the SQL NOT C# Copy. IQueryable<Employee> firstHiredQuery = (from emp in db. In this article I am going to explain the Take and Skip operators in LINQ to SQL. The Take operator is used to return a given number of rows from a database table and the Skip operator skips over a specifed number of rows in a database table. 1. Create a Data Context Class.

Getting COUNT and SKIP TAKE in one operation with Linq to Entities, Getting COUNT and SKIP TAKE in one operation with Linq to Entities. c# entity-​framework entity-framework-6 linq sql-server. English  Tag: c#,sql-server,linq,entity-framework,tsql I need to query multiple tables with one query, and I need to limit the results from each table individually. An example

Entity Framework Skip and Take too slow, I would first suggest logging the database operation to see what SQL is executed. EF may not be running the SQL you think it is. The red flag I  Introduction. In the previous article, you have learned about how to create a new MVC project step by step in a visual studio.. In this article, you will learn how to establish a SQL Server database connection to MVC 5 applications using Entity Framework(ORM), and connect SQL databases to perform operations in a project.

Comments
  • can you give us the code for the sort func?
  • Sure, I'm just passing a lambda. Examples: x => x.Name, x => x.LegalName, x => x.YearEstablished.ToString()
  • Starting to think I should just pass a string then use a switch statement to set the appropriate orderby parameter for the LINQ query :( The first method was so much cooler and involved much less code. I can't understand why it isn't working properly. Without knowing exactly what is happening, it seems like .OrderBy and .OrderByDescending are triggering a database fetch, then applying the sort, then skipping and taking. Maybe that is it though...maybe OrderBy doesn't know how to convert x => x.Name into the appropriate SQL so it fetches the resultset then applies the ordering and filtering.
  • I had a similar problem. I spotted in SQL Profiler that the queries were simply SELECT * FROM's and traced it back to the fact that maybe this Expression<> parameter was missing from the call. I updated it (I was using EF4.1 for reference) and it solved my issue.
  • Anyone know why this is the case? I had the exact same issue. I thought I had kept my whole expression as an IQueryable without calling ToList or anything else that enumerates the expression. Does Func cause enumeration while Expression does not?
  • @BrianSweeney The reason for this, I believe, is that if the sort query is not wrapped in an expression then linq is unable to build an expression tree for it in order to turn it into sql. Because of this it has to enumerate the query to sort it before doing the skip / take
  • Making it generic; code public IEnumerable<TEntity> ListEntities(Expression<Func<TEntity, string>> sort, bool desc, int page, int pageSize, out int totalRecords) { totalRecords = _dbSet.Count(); int skipRows = (page - 1) * pageSize; if (desc) { return _dbSet.OrderByDescending(sort).Skip(skipRows).Take(pageSize).ToList(); } return _dbSet.OrderBy(sort).Skip(skipRows).Take(pageSize).ToList(); } code
  • you solved my problem of pagination i have implement generic repository and i was getting problem i have taken your snippet and it solved.
  • Doing ToList() at the end. Example: db.Stores.OrderBy(x => x.Name).Skip(5).Take(5).ToList()
  • Yes, but earlier on. Is some other method doing a ToList() on your original set.
  • The code above won't return all your data. You either have to better look into the profiler, or you are doing ToList on some other place in code to this data. Try running the profiler when only doing this piece of code.
  • I've used SQL Profiler for a long time and this function is the only one that is firing during the program execution (was looking at the underlying SQL to decide on entity framework vs nhibernate). I tried an alternative way of doing it and saw the SQL I expected (code & SQL above). However, I really like being able to pass a lambda expression for sorting as opposed to having to set it in the linq query.
  • im not sure about your answer. at least in data transfer matter, even i dont use .ToList(), i can see all data context returned from database before .take.skip. it may consume more Kb