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

Question

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.

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?

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

More detailed example:

Using EF I can code:

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

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.

But equally importantly the latter example loses the Strong Typing of EF and should the database change, errors can be introduced.

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

Answer 1

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

EF

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

Data Reader

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

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