Entity Framework 6: is there a way to iterate through a table without holding each row in memory

entity-framework entity-framework-6 entity-framework-6.1


Without storing each entry in memory, I would like to be able to loop through every row in an entity table. Since this is a read-only procedure, each processed row may be dropped.

It would be nice if there was a mechanism to throw out the processed row thereafter. I am aware that a DataReader may be used to accomplish this (and that this is outside the purview of EF), but can this be done inside EF?

Alternatively, is there a method to get a DataReader from within EF without using SQL directly?

More specific illustration:

EF allows me to code:

foreach (Quote in context.Quotes)

but I have to write code in order to get the same thing using a DataReader:

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

// open a new connection to the database

// 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())
    sw.WriteLine(((int)L[0]).ToString() + "," + (string)L[1]);

The difference between the two is that the later runs to completion (and is considerably faster) because it only keeps one row in memory at a time, whilst the former runs out of memory (since it is pulling in the complete table in the client memory).

The latter example, however, removes EF's Strong Typing, and mistakes may be introduced if the database changes.

Therefore, my query is: Can we obtain a comparable outcome with strongly typed rows returning in EF?

7/17/2014 7:09:30 AM

Popular Answer

I'm still unclear after reading your most recent remark. Look at the two pieces of code below.


using (var ctx = new AppContext())
    foreach (var order in ctx.Orders)

EF Profiler

Reader of Data

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

Data Reader Profiler

Even though EF only executes a few initial queries, the profiler can still clearly observe that both of them do so.

7/17/2014 6:47:48 AM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow