Handling large SQL queries with LINQ

Question

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?

Can you not use Where and filter by a primary key instead of Skip? Or simply OrderBy the primary key? — Simon Whitehead
– Simon Whitehead, Commented Mar 6, 2013 at 23:53
Is there any particular reason why you must do this in chunks? — Tory
– Tory, Commented Mar 6, 2013 at 23:56
@Tory I think that loading 20 million entities at once would be reason enough.. — Simon Whitehead
– Simon Whitehead, Commented Mar 7, 2013 at 0:04
OrderBy the clustered index, if one is present on the table; if not, OrderBy the Primary Key ... if you must OrderBy. Seems strange that it's required. — David T. Macknet
– David T. Macknet, Commented Mar 7, 2013 at 0:15

Juliano Alves · Accepted Answer · 2013-03-07 14:36:31Z

4

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

edited Mar 7, 2013 at 14:36

Juliano Alves

2,0164 gold badges36 silver badges38 bronze badges

answered Mar 7, 2013 at 0:05

MayogaX

4693 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Wood Over a year ago

Very comprehensive answer, thanks for all the info and explanation! Just to make sure I'm understanding correctly, the OrderByDescending expression should be y => y.id not y => y, right?

David T. Macknet · Accepted Answer · 2013-03-07 00:18:09Z

-1

You may not have to do an OrderBy - you simply may need to populate the list before doing Skip and Take. I think you can use a .Count() to populate the query, after which you can use Skip and Take.

answered Mar 7, 2013 at 0:18

David T. Macknet

3,1803 gold badges29 silver badges36 bronze badges

2 Comments

Marco Medrano Over a year ago

That will be very heavy, using Count or ToList as you said will populate the query, but will retrieve the 20 million of records.

David T. Macknet Over a year ago

True ... but it won't push the entire set out of the server just to do a count - it'll populate it in MSSQL.

Collectives™ on Stack Overflow

Handling large SQL queries with LINQ

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related