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  gt  x                t   tEntitites T Where predicate  ToList                      else               t  new List lt T gt   tEntitites TA Where historicPredicate  ToList                 using Dispose pattern in order to open new connection every time   using   tEntitites   new TEntities GetEntityConnection            if  critera FromDate  gt  x            t   tEntitites T Where predicate  ToList              else           t  new List lt T gt   tEntitites TA Where historicPredicate  ToList                still problematic   why wouldn t EF reuse a connection if it is already open

User · Answer

I found that I had the same error  and it occurred when I was using a Func lt TEntity  bool gt  instead of a Expression lt Func lt TEntity  bool gt  gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

2 solutions to mitigate this problem    Force memory caching keeping lazy loading with  ToList   after your query  so you can then iterate through it opening a new DataReader   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

User · Answer

If we try to group part of our conditions into a Func lt   or extension method we will get this error  suppose we have a code like this   public static Func lt PriceList  bool gt  IsCurrent       return p   gt   p ValidFrom    null    p ValidFrom  lt   DateTime Now   amp  amp                 p ValidTo    null    p ValidTo  gt   DateTime Now      Or  public static IEnumerable lt PriceList gt  IsCurrent this IEnumerable lt PriceList gt  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 lt Func lt PriceList  bool gt  gt  IsCurrent         return p   gt   p ValidFrom    null    p ValidFrom  lt   DateTime Now   amp  amp                   p ValidTo    null    p ValidTo  gt   DateTime Now       Further more can be read at   http   www albahari com nutshell predicatebuilder aspx

User · Answer

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   gt                                        Products obj   new Products                        obj slno   x slno                      obj ProductName   x ProductName                      obj Price   Convert ToInt32 x Price                       li Add obj                                        return li

User · Answer

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

User · Answer

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   gt  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   gt  context Blogs First blog   gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   gt  x Orders       Include x   gt  x Addresses       Include x   gt  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

User · Answer

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

User · Answer

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

User · Answer

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 lt Blog gt  Blogs   get  set        public DbSet lt Post gt  Posts   get  set       public class Blog       public int BlogID   get  set        public virtual ICollection lt Post gt  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   gt  b Posts Count  gt  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  gt p Text Contains  Important Text         We can do a simple solution to this by adding  ToList   like this   var largeBlogs   context Blogs Where b   gt  b Posts Count  gt  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

User · Answer

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

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

Examples related to linq

Examples related to entity-framework

Examples related to sql-server-2008