c# Entity Framework paginated select?

Question

I need to implement pagination using Entity Framework.

What I have:

I have the number of records I need to select (Let's say 10)
I have the index of the record I want to start from (Page 2 = 2*10 = record 20)

How can I directly select 10 records without selecting them all and then use .skip and .take?

What is the issue with `.Skip` and `.Take`? It's usually how it's done. — Longoon12000, Nov 20 '19 at 12:29
Do not even know if is this possible, the only doc I found from microsoft is for MVC with some nuget packages, and there is no info on SO @HamzaHaider — teraxxx, Nov 20 '19 at 12:30
Please See This Link: https://stackoverflow.com/questions/10145815/c-sharp-entity-framework-pagination — Amin Golmahalleh, Nov 20 '19 at 12:30
@Longoon12000 using `skip` and `take` implies to select every record on the table before using them, right? If I have thousands of records wouldn't that be too slow? — teraxxx, Nov 20 '19 at 12:31
@teraxxx: *"wouldn't that be too slow?"* - Would it? Why assume that without testing it? If you're truly curious about the SQL being generated by these expressions then you can profile the database and capture the query that's executed. You can copy that query into your SQL tools, profile its execution plan, etc. It sounds like you're not trying to solve a problem you've encountered, but instead trying to solve a problem you've imagined. — David, Nov 20 '19 at 12:33
As far as I understand it, as long as you don't to `.ToList` (fetch all) before the `.Skip` and `.Take` it will use whatever means possible (eg. SQL or enumerator magic) to do it as fast as possible. At least that's how I've always done it and it has worked so far without any bad performance impacts. — Longoon12000, Nov 20 '19 at 12:35
We can just look at the source code of [Skip](https://referencesource.microsoft.com/#System.Core/System/Linq/Enumerable.cs,83ec6a20321060a1) and [Take](https://referencesource.microsoft.com/#System.Core/System/Linq/Enumerable.cs,ceb7d3d4f0a39720). To understand how it works. Skip will use `GetEnumerator()` and `MoveNext` . Then Take will iterate and return just what needed. And leave. — xdtTransform, Nov 20 '19 at 12:48

MindSwipe · Accepted Answer · 2019-11-20T12:47:38.247

Entity Framework is quite clever as to how it translates LINQ queries to query statements, as in it optimizes the query for you.

Doing

context.Employess.Where(x => x.Age >= 18).Skip(20).Take(10);

Will not load all employees into memory, filter them by age, skip 20 and then return 10. It will create an SQL query that only returns the 10 employees you actually want. That LINQ query will be translated to something like this:

SELECT id, age, col3, col4...
  FROM employees
  WHERE age >= 18
  ORDER BY id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

That SQL Query will only return 10 rows, after skipping 20. Also note that it will only execute the query once you act on the Enumerable, this is because LINQ uses Deferred Execution

Edit: The SQL statement I supplied will only work on SQL Server 2012 und upwards, as OFFSET and FETCH NEXT were only introduced then

score 0 · Answer 2 · answered Nov 20 '19 at 12:37

You should first Skip from the whole collection and after that Take. Below is the sample code -

var objList = (from c in dbcontext.Employees                         
                    orderby c.DepartmentId select c)
                    .Skip(skipQuantity).Take(numberofRecords);

c# Entity Framework paginated select?

2 Answers2