Why can I have multiple data readers with Dapper but not Entity Framework?

dapper database entity-framework

Question

I started developing an application using Dapper but decided that for some queries it would be nice to use Entity Framework. So I'm in the process of refactoring some of my queries. In the Dapper-only days, I had some code similar to this:

foreach (var itemFromDb in getRecordsWithDapper())
{
    var toInsert = doSomeComputations(itemFromDb);
    insertRecordWithDapper(toInsert);
}

This was very useful, because no matter how much data is in my database, I don't really have to worry about memory. I'm only dealing with one record at a time.

So now I've refactored so that my query does the exact same thing but with Entity Framework 6:

foreach (var itemFromDb in getRecordsWithEf())
{
    var toInsert = doSomeComputations(itemFromDb);
    insertRecordWithDapper(toInsert);
}

However now I get an InvalidOperationException in insertRecordWithDapper() with the message "There is already an open DataReader associated with this Command which must be closed first." I could easily solve this problem by using getRecordsWithEf().ToArray(), however I have too many records to just pull them into memory all at once.

So this is a two-part question:

  1. Why did this stop working?
  2. How can I fix it?

I'm currently using SQL Server, though I'd like a solution to support other database vendors as well.

Accepted Answer

Because dapper assumes that most queries are for limited results, so it buffers by default. Rather than leave the reader open, it deserializes first then hands you a list. You need to specify a parameter (buffered) to get it to hand you back an open reader over the live TDS stream. While supporting huge data sets is important, it is the exception not the norm, so dapper optimises for smallish results as the default.

Presumably EF makes the other choice, handing you an open reader by default. To compensate: call ToList or similar:

foreach (var itemFromDb in getRecordsWithEf().ToList())


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why