实体框架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?

或者有没有办法获得一个来自EF内的DataReader，而不直接使用SQL？

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

更详细的示例：

使用EF我可以编码：

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几乎没有初始查询，它们都执行类似的查询，可以是从profiler看到..

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

这篇关于实体框架6：是否有一种方法可以遍历一个表，而不必在内存中保留每一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！