处理大型SQL查询使用LINQ [英] Handling large SQL queries with LINQ

问题描述

我想通过一个实体数据模型来从一个MS SQL数据库中的表有大约20万个条目中的每个记录。我最初的想法是检索块中的数据，像这样：

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.

什么是通过LINQ处理大型SQL查询的最佳方法呢？

What's the best approach to handling large SQL queries via LINQ?

推荐答案

错误发生，因为该方法跳过需求排序依据后运行。您不能运行没有排序依据跳过。该方法跳过需要知道第一个拿的，如果你把什么是第一个需要知道选择的顺序知道，如果第一个是从一开始，这个数字将结束或结束的开始。

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.

您可以阅读更多这里

那么，你的code是这样的：

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);
        }
    }
}

如果你认为这是一个沉重的查询，实体的记住框架可以做的查询和数据 缓存。如果你不喜欢这种方法的SQL您可以<一href="http://msdn.microsoft.com/en-us/library/system.data.objects.objectcontext.executestorecommand.aspx"相对=nofollow>运行查询手动的。

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.

个人experiencie：的 予使用与2双向线路和一个数据库...这是不慢。但我有指数在我的表，我一直使用的缓存。

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.

欲了解更多：的 您可以使用过程，如果preFER。查看更多<一href="http://weblogs.asp.net/dwahlin/archive/2011/09/23/using-entity-framework-$c$c-first-with-stored-procedures-that-have-output-parameters.aspx"相对=nofollow>这里

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

这篇关于处理大型SQL查询使用LINQ的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！