1

I recently ran into an error while trying to use skip and take in a LINQ statement.

My statement looked like this.

DbConxtext.MyTable.Get(c => c.UserID == id)
    .OrderBy(orderProperty).Skip(index).Take(length).ToList();

Which gave me this error

Incorrect syntax near 'OFFSET'.\r\nInvalid usage of the option NEXT in the FETCH statement

I found out this is caused because OFFSET NEXT and FETCH did not work on sql server 2008, But I knew I was using paging in other places in my code and they all worked fine.

The difference between the ones that worked and this one was that Skip and Take were extensions of Enumerable on the ones that worked and Queryable on the ones that didnt.

So adding AsEnumerable() to the query fixed the issue for me. This seemed to generate SQL that uses SELECT TOP(10) instead of OFFSET and FETCH.

EDIT: After reading over this again I realize that AsEnumerable will not generate different SQL. It will instead execute the query and do the Skip Take in memory.

DbConxtext.MyTable.Get(c => c.UserID == id)
    .OrderBy(orderProperty).AsEnumerable().Skip(index).Take(length).ToList();

My question is what is the difference between using Skip and Take as extensions of Enumerable vs Queryable.

And why did EF decide to generate different SQL between the two cases.

Moe
  • 110
  • 2
  • 10
  • `This seemed to generate SQL that uses SELECT TOP(10)` It will do no such thing. You should look at the *actual* SQL generated to see the difference between the two queries. – Servy Aug 25 '17 at 17:55
  • The difference is that the `Skip` and `Take` will be applied in memory instead of in the DB. Basically you're getting all the results and then filtering it down to the ones you want. – juharr Aug 25 '17 at 18:03
  • 1
    Why do you believe that a select is generated when you use AsEnumerable? AsEnumerable means *stop generating SQL here*. I am interested to learn why people believe false things about programming; what led you to this belief? – Eric Lippert Aug 25 '17 at 18:06
  • Skip() use for ignoring value like skip(2) this will ignore top 2 values. Take() use for fetching top values like take(2) this will fetch top 2 values. – Sanjay Kumaar Aug 25 '17 at 18:06
  • 1
    @EricLippert I realize now that it is not correct, but I came to that conclusion after running that query in LINQPad 4, it has a feature that allows you to see the SQL generated from a statement and I saw that the query was using SELECT TOP. So I assumed that was the query it must be using when it doesnt give me an error. Completely forgetting that AsEnumerable will execute the query. :/ – Moe Aug 25 '17 at 19:40
  • @Moe Your last sentence is technically wrong- which was Eric's point. `AsEnuerable` does not _execute_ the query - it just changes the context from building a query against the dataset to building a second query against the results of the first query. Neither query is actually _executed_ until you try to iterate the results (via a `ForEach`, an aggregator such as `Sum`, or a transformer such as `ToList`). – D Stanley Aug 26 '17 at 13:26
  • @EricLippert My thought is that the abstraction hides the actual execution details (rightly so) from the programmer. There's nothing obvious to tell someone _when_ the query actually gets executed. Without reading the documentation, it's just as reasonable to assume that `AsEnumerable` executes the query as it is to assume that the query is executed at iteration-time. Unfortunately, most don't read documentation to this level of detail and just assume that `AsEnumerable` executes the query, since the context changes from SQL to objects with the addition of that statement and nothing else. – D Stanley Aug 26 '17 at 13:31

1 Answers1

3

what is the difference between using Skip and Take as extensions of Enumerable vs Queryable.

When you call Skip or Take on a type that implements IQueryable, the Queryable extension methods will be bound, and the underlying Linq provider (e.g. Linq-to-Entities) will process the Skip and/or Take and turn it into commands for the underlying data provider (e.g. SQL statements). Whether the provider actually supports them or processes them correctly will not be known until run-time.

When you call them on a type that implements IEnumerable (but not IQueryable), the Enumerable extension methods will be bound, which just process the commands on the in-memory collection generated by the Queryable query.

why did EF decide to generate different SQL between the two cases.

In your second case, the SQL query that is generated only incorporates the commands up until you inject AsEnumerable(). That's all that the EF provider sees. From then on, the commands will be bound to the Enumerable extension methods and will process the remaining commands in-memory.

This seemed to generate SQL that uses SELECT TOP(10)

I highly doubt that. What should happen is that the SQL query will return all records, but the in-memory iterator generated by Take will only return the first ten.

If you want the SKIP and TAKE to be processed correctly for a SQL 2008 database, see this question for alternate solutions.

D Stanley
  • 149,601
  • 11
  • 178
  • 240
  • Note that .AsEnumerable().Skip().Take() is relatively efficient for small values of Skip() and SQL Server. The client just has to Read() and ignore a few rows. – David Browne - Microsoft Aug 25 '17 at 19:28
  • @DavidBrowne-Microsoft That's true for `Skip`, not `Take`. – Servy Aug 25 '17 at 19:30
  • @DavidBrowne-Microsoft But the entire set will be read into memory, correct? So if you take 10 out of 1M rows it will be much less efficient in-memory, no? – D Stanley Aug 25 '17 at 19:31
  • @Servy Take is the same in either case, those are loaded and saved in memory. @D Stanley the first Skip+Take rows are read (and added to the ChangeTracker if enabled). But the rest of the rows are not ever fetched from the database. – David Browne - Microsoft Aug 25 '17 at 19:59
  • @DavidBrowne-Microsoft Good to know. I always assumed the entire result set was loaded into memory. – D Stanley Aug 25 '17 at 20:09
  • 3
    Just tested this, and while the trailing rows are never materialized into objects, disposing the enumerator calls SqlDataReader.Dispose() will fetch the rows over the network from the server. So it's actually not cheap. Thinking about this, it's probably because the batch/proc could have additional commands that haven't run yet, and the only way to unblock the batch is to finish fetching the results. – David Browne - Microsoft Aug 25 '17 at 21:25