Skip() and Take() as Enumerable vs as Queryable

c# entity-framework entity-framework-6 linq-to-sql

Question

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.

1
0
8/25/2017 7:44:05 PM

Accepted Answer

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.

1
8/25/2017 6:12:54 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow