Entity Framework: There is already an open DataReader associated with this Command

Question

I am using Entity Framework and occasionally i will get this error.

EntityCommandExecutionException
{"There is already an open DataReader associated with this Command which must be closed first."}
   at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands...

Even though i am not doing any manual connection management.

this error happens intermittently.

code that triggers the error (shortened for ease of reading):

        if (critera.FromDate > x) {
            t= _tEntitites.T.Where(predicate).ToList();
        }
        else {
            t= new List<T>(_tEntitites.TA.Where(historicPredicate).ToList());
        }

using Dispose pattern in order to open new connection every time.

using (_tEntitites = new TEntities(GetEntityConnection())) {

    if (critera.FromDate > x) {
        t= _tEntitites.T.Where(predicate).ToList();
    }
    else {
        t= new List<T>(_tEntitites.TA.Where(historicPredicate).ToList());
    }

}

still problematic

why wouldn't EF reuse a connection if it is already open.

Answer 1

It is not about closing connection. EF manages connection correctly. My understanding of this problem is that there are multiple data retrieval commands executed on single connection (or single command with multiple selects) while next DataReader is executed before first one has completed the reading. The only way to avoid the exception is to allow multiple nested DataReaders = turn on MultipleActiveResultSets. Another scenario when this always happens is when you iterate through result of the query (IQueryable) and you will trigger lazy loading for loaded entity inside the iteration.

Answer 2

Alternatively to using MARS (MultipleActiveResultSets) you can write your code so you dont open multiple result sets.

What you can do is to retrieve the data to memory, that way you will not have the reader open. It is often caused by iterating through a resultset while trying to open another result set.

Sample Code:

public class MyContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }
}

public class Blog
{
    public int BlogID { get; set; }
    public virtual ICollection<Post> Posts { get; set; }
}

public class Post
{
    public int PostID { get; set; }
    public virtual Blog Blog { get; set; }
    public string Text { get; set; }
}

Lets say you are doing a lookup in your database containing these:

var context = new MyContext();

//here we have one resultset
var largeBlogs = context.Blogs.Where(b => b.Posts.Count > 5); 

foreach (var blog in largeBlogs) //we use the result set here
{
     //here we try to get another result set while we are still reading the above set.
    var postsWithImportantText = blog.Posts.Where(p=>p.Text.Contains("Important Text"));
}

We can do a simple solution to this by adding .ToList() like this:

var largeBlogs = context.Blogs.Where(b => b.Posts.Count > 5).ToList();

This forces entityframework to load the list into memory, thus when we iterate though it in the foreach loop it is no longer using the data reader to open the list, it is instead in memory.

I realize that this might not be desired if you want to lazyload some properties for example. This is mostly an example that hopefully explains how/why you might get this problem, so you can make decisions accordingly

Answer 3

There's another way to overcome this problem. Whether it's a better way depends on your situation.

The problem results from lazy loading, so one way to avoid it is not to have lazy loading, through the use of Include:

var results = myContext.Customers
    .Include(x => x.Orders)
    .Include(x => x.Addresses)
    .Include(x => x.PaymentMethods);

If you use the appropriate Includes, you can avoid enabling MARS. But if you miss one, you'll get the error, so enabling MARS is probably the easiest way to fix it.

Answer 4

You get this error, when the collection you are trying to iterate is kind of lazy loading (IQueriable).

foreach (var user in _dbContext.Users)
{    
}

Converting the IQueriable collection into other enumerable collection will solve this problem. example

_dbContext.Users.ToList()

Note: .ToList() creates a new set every-time and it can cause the performance issue if you are dealing with large data.

Answer 5

Try in your connection string to set MultipleActiveResultSets=true. This allow multitasking on database.

Server=yourserver ;AttachDbFilename=database;User Id=sa;Password=blah ;MultipleActiveResultSets=true;App=EntityFramework

That works for me ... whether your connection in app.config or you set it programmatically ... hope this helpful

Answer 6

I solved the problem easily (pragmatic) by adding the option to the constructor. Thus, i use that only when needed.

public class Something : DbContext
{
    public Something(bool MultipleActiveResultSets = false)
    {
        this.Database
            .Connection
            .ConnectionString = Shared.ConnectionString /* your connection string */
                              + (MultipleActiveResultSets ? ";MultipleActiveResultSets=true;" : "");
    }
...

Answer 7

I had originally decided to use a static field in my API class to reference an instance of MyDataContext object (Where MyDataContext is an EF5 Context object), but that is what seemed to create the problem. I added code something like the following to every one of my API methods and that fixed the problem.

using(MyDBContext db = new MyDBContext())
{
    //Do some linq queries
}

As other people have stated, the EF Data Context objects are NOT thread safe. So placing them in the static object will eventually cause the "data reader" error under the right conditions.

My original assumption was that creating only one instance of the object would be more efficient, and afford better memory management. From what I have gathered researching this issue, that is not the case. In fact, it seems to be more efficient to treat each call to your API as an isolated, thread safe event. Ensuring that all resources are properly released, as the object goes out of scope.

This makes sense especially if you take your API to the next natural progression which would be to expose it as a WebService or REST API.

Disclosure

• OS: Windows Server 2012
• .NET: Installed 4.5, Project using 4.0
• Data Source: MySQL
• Application Framework: MVC3
• Authentication: Forms

Answer 8

I noticed that this error happens when I send an IQueriable to the view and use it in a double foreach, where the inner foreach also needs to use the connection. Simple example (ViewBag.parents can be IQueriable or DbSet):

foreach (var parent in ViewBag.parents)
{
    foreach (var child in parent.childs)
    {

    }
}

The simple solution is to use .ToList() on the collection before using it. Also note that MARS does not work with MySQL.

Answer 9

I found that I had the same error, and it occurred when I was using a Func<TEntity, bool> instead of a Expression<Func<TEntity, bool>> for your predicate.

Once I changed out all Func's to Expression's the exception stopped being thrown.

I believe that EntityFramwork does some clever things with Expression's which it simply does not do with Func's

Answer 10

2 solutions to mitigate this problem:

1. Force memory caching keeping lazy loading with .ToList() after your query, so you can then iterate through it opening a new DataReader.
2. .Include(/additional entities you want to load in the query/) this is called eager loading, which allows you to (indeed) include associated objects(entities) during he execution of a query with the DataReader.

Answer 11

A good middle-ground between enabling MARS and retrieving the entire result set into memory is to retrieve only IDs in an initial query, and then loop through the IDs materializing each entity as you go.

For example (using the "Blog and Posts" sample entities as in this answer):

using (var context = new BlogContext())
{
    // Get the IDs of all the items to loop through. This is
    // materialized so that the data reader is closed by the
    // time we're looping through the list.
    var blogIds = context.Blogs.Select(blog => blog.Id).ToList();

    // This query represents all our items in their full glory,
    // but, items are only materialized one at a time as we
    // loop through them.
    var blogs =
        blogIds.Select(id => context.Blogs.First(blog => blog.Id == id));

    foreach (var blog in blogs)
    {
        this.DoSomethingWith(blog.Posts);

        context.SaveChanges();
    }
}

Doing this means that you only pull a few thousand integers into memory, as opposed to thousands of entire object graphs, which should minimize memory usage while enabling you to work item-by-item without enabling MARS.

Another nice benefit of this, as seen in the sample, is that you can save changes as you loop through each item, instead of having to wait until the end of the loop (or some other such workaround), as would be needed even with MARS enabled (see here and here).

Answer 12

In my case I found that there were missing "await" statements before myContext.SaveChangesAsync() calls. Adding await before those async calls fixed the data reader issues for me.

Answer 13

If we try to group part of our conditions into a Func<> or extension method we will get this error, suppose we have a code like this:

public static Func<PriceList, bool> IsCurrent()
{
  return p => (p.ValidFrom == null || p.ValidFrom <= DateTime.Now) &&
              (p.ValidTo == null || p.ValidTo >= DateTime.Now);
}

Or

public static IEnumerable<PriceList> IsCurrent(this IEnumerable<PriceList> prices) { .... }

This will throw the exception if we try to use it in a Where(), what we should do instead is to build a Predicate like this:

public static Expression<Func<PriceList, bool>> IsCurrent()
{
    return p => (p.ValidFrom == null || p.ValidFrom <= DateTime.Now) &&
                (p.ValidTo == null || p.ValidTo >= DateTime.Now);
}

Further more can be read at : http://www.albahari.com/nutshell/predicatebuilder.aspx

Answer 14

This problem can be solved simply by converting the data to a list

 var details = _webcontext.products.ToList();


            if (details != null)
            {
                Parallel.ForEach(details, x =>
                {
                    Products obj = new Products();
                    obj.slno = x.slno;
                    obj.ProductName = x.ProductName;
                    obj.Price = Convert.ToInt32(x.Price);
                    li.Add(obj);

                });
                return li;
            }

Answer 15

In my situation the problem occurred because of a dependency injection registration. I was injecting a per request scope service that was using a dbcontext into a singleton registered service. Therefor the dbcontext was used within multiple request and hence the error.

Answer 16

In my case the issue had nothing to do with MARS connection string but with json serialization. After upgrading my project from NetCore2 to 3 i got this error.

More information can be found here

Answer 17

I solved this problem using the following section of code before the second query:

 ...first query
 while (_dbContext.Connection.State != System.Data.ConnectionState.Closed)
 {
     System.Threading.Thread.Sleep(500);
 }
 ...second query

you can change the time of sleep in miliseconds

P.D. Useful when using threads