Handling large SQL queries with LINQ

.net, c#, entity-framework, linq, sql-server

Solution

The error happens because the method Skip needs run after the OrderBy. You cannot run the Skip without the OrderBy. The method Skip needs to know the first one to take, and if you put what is the first that needs to know the order of the select to know if the first is that number from beginning to end or end to the beginning.

You can read more here

So, your code looks like this:

public IEnumerable<IEnumerable<device>> GetDevicesInChunks(int chunkSize)
{
    using (var db = new AccountsEntities())
    {
        for (int i = 0; i < db.devices.Count(); i += chunkSize)
        {
            yield return db.devices.OrderByDescending(y => y).Skip(i).Take(chunkSize);
        }
    }
}

if you think that was a heavy query, remeber Entity Framework can do a cache of query and data. If you don't like the sql of that method you can run the query manually.

A personal experiencie: I use that with a database with 2 bi of lines and... it was not slow. But I have index in my table and I use always the cache.

For more: You can use procedures, if you prefer. See more here

Problem

I'm trying to fetch every record from a table in a MS SQL database with about 20 million entries via an entity data model. My initial idea was to retrieve the data in chunks, like so: ``` public IEnumerable<IEnumerable<device>> GetDevicesInChunks(int chunkSize) { using (var db = new AccountsEntities()) { for (int i = 0; i < db.devices.Count(); i += chunkSize) { yield return db.devices.Skip(i).Take(chunkSize); } } } ``` However, it appears that I must call `OrderBy` before I call `Skip`, judging by the exception that is thrown when I employ the above method ``` The method 'Skip' is only supported for sorted input in LINQ to Entities. The method 'OrderBy' must be called before the method 'Skip'. ``` I'm sure calling `OrderBy` on every subset of records I retrieve will be costly since the devices are in no particular order - I feel like I'm walking down the wrong path here. What's the best approach to handling large SQL queries via LINQ?

Original source