实体框架 6:有没有办法在不将每一行保存在内存中的情况下遍历表 [英] Entity Framework 6: is there a way to iterate through a table without holding each row in memory

问题描述

我希望能够遍历实体表中的每一行，而无需将每一行都保存在内存中.这是一个只读操作，每一行在处理后都可以丢弃.

I would like to be able to iterate through every row in an entity table without holding every row in memory. This is a read only operation and every row can be discarded after being processed.

如果有办法在处理后丢弃该行，那很好.我知道这可以使用 DataReader 来实现(不在 EF 的范围内)，但是可以在 EF 内实现吗?

If there is a way to discard the row after processing that would be fine. I know that this can be achieved using a DataReader (which is outside the scope of EF), but can it be achieved within EF?

或者有没有办法在不直接使用 SQL 的情况下从 EF 中获取 DataReader?

Or is there a way to obtain a DataReader from within EF without directly using SQL?

更详细的例子:

使用 EF 我可以编码:

Using EF I can code:

foreach (Quote in context.Quotes)
   sw.WriteLine(sw.QuoteId.ToString()+","+sw.Quotation);

但要使用 DataReader 获得相同的结果，我需要编写代码:

but to achieve the same result with a DataReader I need to code:

// get the connection to the database
SqlConnection connection = context.Database.Connection as SqlConnection;

// open a new connection to the database
connection.Open();

// get a DataReader for our table
SqlCommand command = new SqlCommand(context.Quotes.ToString(), connection);
SqlDataReader dr = command.ExecuteReader();

// get a recipient for our database fields
object[] L = new object[dr.FieldCount];

while (dr.Read())
{
    dr.GetValues(L);
    sw.WriteLine(((int)L[0]).ToString() + "," + (string)L[1]);
}

不同之处在于前者内存不足(因为它正在拉入客户端内存中的整个表)，而后者运行完成(并且速度更快)因为它在任何时候都只在内存中保留一行一次.

The difference is that the former runs out of memory (because it is pulling in the entire table in the client memory) and the later runs to completion (and is much faster) because it only retains a single row in memory at any one time.

但同样重要的是，后一个示例失去了 EF 的强类型，如果数据库发生变化，可能会引入错误.

因此，我的问题是:在 EF 中返回强类型行是否可以得到类似的结果?

Hence, my question: can we get a similar result with strongly typed rows coming back in EF?

推荐答案

根据您的上一条评论，我仍然感到困惑.看看下面两个代码.

Based on your last comment, I'm still confused. Take a look at both of below code.

using (var ctx = new AppContext())
{
    foreach (var order in ctx.Orders)
    {
        Console.WriteLine(order.Date);
    }
}

var constr = ConfigurationManager.ConnectionStrings["AppContext"].ConnectionString;
using (var con = new SqlConnection(constr))
{
    con.Open();    
    var cmd = new SqlCommand("select * from dbo.Orders", con);
    var reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine(reader["Date"]);
    }
}

尽管 EF 的初始查询很少，但它们都执行从分析器中可以看到的类似查询..

Even though EF has few initial query, both of them execute similar query that can be seen from profiler..

这篇关于实体框架 6:有没有办法在不将每一行保存在内存中的情况下遍历表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！