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.
what is the difference between using
Takeas extensions of
When you call
Take on a type that implements
Queryable extension methods will be bound, and the underlying Linq provider (e.g. Linq-to-Entities) will process the
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
Enumerable extension methods will be bound, which just process the commands on the in-memory collection generated by the
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.