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?

Majid
  • 13,853
  • 15
  • 77
  • 113
  • Consider returning `IQueryable` too so that the calling code can add additional query operations to be performed in the backing data store (whereas `IEnumerable` will cause those operations to be performed by the CLR, after the query returns from the data store). – cdhowie Sep 09 '13 at 17:06
  • 1
    @cdhowie I saw in http://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 will cause all of your rows to be loaded in memory". what about this? – Majid Sep 09 '13 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.) – cdhowie Sep 09 '13 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). – cdhowie Sep 09 '13 at 17:33

4 Answers4

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.

haim770
  • 48,394
  • 7
  • 105
  • 133
  • I saw in http://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 will cause all of your rows to be loaded in memory". what about this? – Majid Sep 09 '13 at 17:22
  • 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. – haim770 Sep 09 '13 at 17:25
  • so if I save result of some linq to entity query in `IEnumerable`var;next linq on this var,will not fetching database? what if I use `IQueryable`? – Majid Sep 09 '13 at 17:29
  • Generally, yes. but if `Proxy Creation` is enabled by the Context, you may still hit the database when loading `Navigation Properties` data. – haim770 Sep 09 '13 at 17:36
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.

MarcinJuraszek
  • 124,003
  • 15
  • 196
  • 263
  • 1
    I saw in http://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 will cause all of your rows to be loaded in memory". what about this against your last paragraph? – Majid Sep 09 '13 at 17:25
  • 2
    @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`. – MarcinJuraszek Sep 09 '13 at 17:26
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.

Christian Phillips
  • 18,399
  • 8
  • 53
  • 82
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.

Dave Swersky
  • 34,502
  • 9
  • 78
  • 118