使用 LinqToSql 返回数据库记录? [英] Yield return database records using LinqToSql?

问题描述

我在 DAL 中有一个提供方法:

I have a supplying method in DAL:

public IEnumerable<RecordType> GetRecords()
{
    using (LinqDataContext context = new LinqDataContext())
    {
        var records = context.RecordTable;

        foreach (RecordType record in records)
        {
            yield return record;
        }
    }
}

消费者方法使用 foreach 循环中的记录.这样我想通过不缓存 RecordTable 中的所有记录来节省内存使用量，因为 LinqToSql 在幕后使用 DataReader.

The consumer method uses the records in a foreach loop. This way I want to save a memory usage by not caching all the records from RecordTable, since LinqToSql uses a DataReader behind the scene.

我对这个场景有两个问题:

I have two questions on this scenario though:

1. 与将所有记录缓存到数组 (.ToArray()) 相比，上面的 yield-returning 是否可以节省资源并且运行速度更快?

1. Is that true that yield-returning above saves resources and works faster than caching all records to an array (.ToArray())?

如果在消费方法的 foreach 循环内发生错误，数据连接是否会自动正确关闭(我的意思是 using 语句)消费方法会在中间中断 foreach 循环(比如找到所需的记录和 break)?

Will the data connection be automatically properly closed (I mean the using statement) if an error will occur inside the foreach loop of the consuming method OR if the consuming method will break the foreach loop in the middle (like found a required record and break)?

推荐答案

在执行基本查询的情况下，它可以以这种方式工作(当然这是可能的)——但是，在这种情况下查询一个裸Table，它可能首先是所有的缓冲区；您也许可以尝试在迭代期间查询计数，或运行跟踪.在这种情况下，我怀疑它会先缓冲.

In the case of executing a basic query, it can work that way (certainly it is possible) - however, in the case of querying a naked Table<T>, it might be that it all buffers first; you could perhaps try querying the count during the iteration, or running a trace. In this case I suspect it will buffer first.

重新关闭:这也取决于 ;p 如果有人使用 foreach，那么是的:因为 foreach 通过 finally 显式处理迭代器.然而！不能保证有人这样做，例如(非常顽皮和松懈):

Re closed: that also depends ;p If someone is using foreach, then yes: since foreach explicitly disposes the iterator via finally. However! It is not guaranteed if someone does, for example (very naughty and lax):

var iter = yourData.GetEnumerator();
if(iter.MoveNext()) {
    Console.WriteLine(iter.Current.Name); // first record of, say, 20
}
// and don't dispose the iterator == bad

然后由于迭代器没有 a: 被处理，b: 耗尽自身，并且 c: 没有崩溃，它不会正确关闭(这 3 个条件中的任何一个都会关闭正确).强调:这是一个病态的案例:通常说它会关闭，是的"是相当安全的.

then since the iterator doesn't a: get disposed, b: exhaust itself, and c: doesn't crash, it won't shut down properly (any of those 3 conditions will close it properly). Emphasis: this is a pathological case: normally it is reasonably safe to say "it will close, yes".

如果你想保证非缓冲，请注意dapper"有那个，如果你将 buffered 设置为 false:

If you want guaranteed non-buffering, note that "dapper" has that, if you set buffered to false:

IEnumerable<Customer> customers = connection.Query<Customer>(
       "select * from Customer", buffered: false);

(它还可以处理参数等)

(it can also handle the parameters etc)

这篇关于使用 LinqToSql 返回数据库记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！