3

I use this method to get each page of data from EF:

public IEnumerable<MyObj> GetByPage(int page, int perPage)
{
    return context.MyObj.Skip((page - 1) * perPage).Take(perPage);
}

I want to know ;would this code fetch all rows of MyObj and store in memory and then will Skip and Take or all of above code will translate to SQL command?

If all first will store in memory,How can I use LINQ to entity to not to use memory to Skip and Take?

Improve this question
4
  • Consider returning IQueryable<MyObj> too so that the calling code can add additional query operations to be performed in the backing data store (whereas IEnumerable<MyObj> will cause those operations to be performed by the CLR, after the query returns from the data store). Commented Sep 9, 2013 at 17:06
  • 1
    @cdhowie I saw in stackoverflow.com/a/2876655/2040375 that says " If you use Take and Skip on IQueryable, you will only get the number of rows requested; doing that on an IEnumerable<T> will cause all of your rows to be loaded in memory". what about this? Commented Sep 9, 2013 at 17:17
  • 1
    That's half-true. The Skip() operation would force all of the skipped-over records to be fetched and then discarded, one-by-one. The Take() operation will dispose of its enumerator after it has fetched the specified number of rows, so the rows after those you fetch may not necessarily be fetched from disk by the database server, nor returned to the database client in your application. (But they might, depending on the database and client library implementations.) Commented Sep 9, 2013 at 17:29
  • 1
    It's important to note that depending on the client library implementation, all of the records might exist in memory, but if they are read one-by-one by the client library then each skipped-over record is eligible for garbage collection immediately after it is processed. So if the client library is smart then you still wouldn't be loading the entire result set into memory; the entire overhead would consist of fetching and discarding all of the skipped-over records (which would increase processing time, but not necessarily significant memory usage). Commented Sep 9, 2013 at 17:33

4 Answers 4

Reset to default
7

As long as you're not materializing the query (i.e calling ToList()/ToArray() etc, or iterating over it), your Skip and Take method will be translated to SQL by the Linq to Entities provider that is part of the Entity Framework.

So to answer your question: no, it won't fetch all data and load it into memory.

See this MSDN article for a full explanation.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I saw in stackoverflow.com/a/2876655/2040375 that says " If you use Take and Skip on IQueryable, you will only get the number of rows requested; doing that on an IEnumerable<T> will cause all of your rows to be loaded in memory". what about this?
That is correct. because calling Skip on an IEnumerable will execute the Linq to Objects.Skip method (rather than Linq to Entities.Skip) that will have to materialize the collection. same goes for Where/Select etc.
so if I save result of some linq to entity query in IEnumerable<T>var;next linq on this var,will not fetching database? what if I use IQueryable<T>?
Generally, yes. but if Proxy Creation is enabled by the Context, you may still hit the database when loading Navigation Properties data.
3

Both Skip and Take are listed as LINQ to Entities supported methods, so they will be transformed into proper SQL statements and only necessary rows will be retrieved from database.

And because your method returns IEnumerable<T> instead of IQueryable<T> every call to the query returned from that method will cause query execution.

Improve this answer

2 Comments

I saw in stackoverflow.com/a/2876655/2040375 that says " If you use Take and Skip on IQueryable, you will only get the number of rows requested; doing that on an IEnumerable<T> will cause all of your rows to be loaded in memory". what about this against your last paragraph?
@MajidR You're doing it on IQueryable, because context.MyObj is IQueryable. But because your method returns IEnumerable everything that is done on an object returned from your method will be done on IEnumerable.
2

Skip and Take will be evaluated and then the result will be stored to memory, so it will not hold the whole data set in memory. This is called Deferred Execution.

Improve this answer

Comments

0

Ultimately it depends on your data store, but for SQL Server it would not pull any records not defined in that query. In the case of T-SQL, the TOP predicate is used to "skip" records.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.