SqlException from Entity Framework - New transaction is not allowed because there are other threads running in the session

Question

I am currently getting this error      System Data SqlClient SqlException  New transaction is not allowed because there are other threads running in the session    while running this code   public class ProductManager   IProductManager        region Declare Models     private RivWorks Model Negotiation RIV Entities  dbRiv   RivWorks Model Stores RivEntities AppSettings RivWorkEntities connString       private RivWorks Model NegotiationAutos RivFeedsEntities  dbFeed   RivWorks Model Stores FeedEntities AppSettings FeedAutosEntities connString        endregion      public IProduct GetProductById Guid productId                   Do a quick sync of the feeds            SyncFeeds                           get a product                        return product             private void SyncFeeds                 bool found   false          string feedSource    AUTO           switch  feedSource     companyFeedDetail FeedSourceTable ToUpper                          case  AUTO                   var clientList   from a in  dbFeed Client Include  Auto   select a                  foreach  RivWorks Model NegotiationAutos Client client in clientList                                        var companyFeedDetailList   from a in  dbRiv AutoNegotiationDetails where a ClientID    client ClientID select a                      foreach  RivWorks Model Negotiation AutoNegotiationDetails companyFeedDetail in companyFeedDetailList                                                if  companyFeedDetail FeedSourceTable ToUpper       AUTO                                                         var company    from a in  dbRiv Company Include  Product   where a CompanyId    companyFeedDetail CompanyId select a  First                                foreach  RivWorks Model NegotiationAutos Auto sourceProduct in client Auto                                                                foreach  RivWorks Model Negotiation Product targetProduct in company Product                                                                        if  targetProduct alternateProductID    sourceProduct AutoID                                                                                found   true                                          break                                                                                                          if   found                                                                        var newProduct   new RivWorks Model Negotiation Product                                        newProduct alternateProductID   sourceProduct AutoID                                      newProduct isFromFeed   true                                      newProduct isDeleted   false                                      newProduct SKU   sourceProduct StockNumber                                      company Product Add newProduct                                                                                                dbRiv SaveChanges            THIS BREAKS                                                                                          break                      Model  1 - This model sits in a database on our Dev Server  Model  1 http   content screencast com users Keith Barrows folders Jing media bdb2b000-6e60-4af0-a7a1-2bb6b05d8bc1 Model1 png   Model  2 - This model sits in a database on our Prod Server and is updated each day by automatic feeds   alt text http   content screencast com users Keith Barrows folders Jing media 4260259f-bce6-43d5-9d2a-017bd9a980d4 Model2 png  Note - The red circled items in Model  1 are the fields I use to  map  to Model  2   Please ignore the red circles in Model  2  that is from another question I had which is now answered   Note   I still need to put in an isDeleted check so I can soft delete it from DB1 if it has gone out of our client s inventory   All I want to do  with this particular code  is connect a company in DB1 with a client in DB2  get their product list from DB2 and INSERT it in DB1 if it is not already there   First time through should be a full pull of inventory   Each time it is run there after nothing should happen unless new inventory came in on the feed over night      So the big question - how to I solve the transaction error I am getting   Do I need to drop and recreate my context each time through the loops  does not make sense to me

User · Answer

The code below works for me   private pricecheckEntities  context   new pricecheckEntities          private void resetpcheckedtoFalse         try               foreach  var product in  context products                        product pchecked   false               context products Attach product                context Entry product  State   EntityState Modified                     context SaveChanges              catch  Exception extofException                MessageBox Show extofException ToString                productsDataGrid Items Refresh

User · Answer

After much pulling out of hair I discovered that the foreach loops were the culprits   What needs to happen is to call EF but return it into an IList lt T gt  of that target type then loop on the IList lt T gt    Example   IList lt Client gt  clientList   from a in  dbFeed Client Include  Auto   select a  foreach  RivWorks Model NegotiationAutos Client client in clientList       var companyFeedDetailList   from a in  dbRiv AutoNegotiationDetails where a ClientID    client ClientID select a

User · Answer

I know it is an old question but i faced this error today    and i found that  this error can be thrown when a database table trigger gets an error   for your information  you can check your tables triggers too when you get this error

User · Answer

Just put context SaveChanges   after end of your foreach loop

User · Answer

FYI  from a book and some lines adjusted because its stil valid   Invoking SaveChanges   method begins a transaction which automatically rolls back all changes persisted to the database if an exception occurs before iteration completes  otherwise the transaction commits  You might be tempted to apply the method after each entity update or deletion rather than after iteration completes  especially when you re updating or deleting massive numbers of entities   If you try to invoke SaveChanges   before all data has been processed  you incur a  New transaction is not allowed because there are other threads running in the session  exception  The exception occurs because SQL Server doesn t permit starting a new transaction on a connection that has a SqlDataReader open  even with Multiple Active Record Sets  MARS  enabled by the connection string  EF s default connection string enables MARS   Sometimes its better to understand why things are happening  -

User · Answer

We have now posted an official response to the bug opened on Connect   The workarounds we recommend are as follows   This error is due to Entity Framework creating an implicit transaction during the SaveChanges   call  The best way to work around the error is to use a different pattern  i e   not saving while in the midst of reading  or by explicitly declaring a transaction  Here are three possible solutions      1  Save after iteration  recommended approach in most cases  using  var context   new MyContext          foreach  var person in context People                   Change to person           context SaveChanges          2  Declare an explicit transaction using  var transaction   new TransactionScope          using  var context   new MyContext                  foreach  var person in context People                           Change to person             context SaveChanges                        transaction Complete          3  Read rows ahead  Dangerous   using  var context   new MyContext          var people   context People ToList       Note that this forces the database                                              to evaluate the query immediately                                              and could be very bad for large tables       foreach  var person in people                   Change to person         context SaveChanges

User · Answer

I needed to read a huge ResultSet and update some records in the table  I tried to use chunks as suggested in  Drew Noakes s answer   Unfortunately after 50000 records I ve got  OutofMemoryException  The answer Entity framework large data set  out of memory exception explains  that      EF creates second copy of data which uses for change detection  so   that it can persist changes to the database   EF holds this second set   for the lifetime of the context and its this set thats running you out   of memory    The recommendation  is to re-create your context for each batch   So I ve retrieved Minimal and Maximum values of the primary key- the tables have primary keys as auto incremental integers Then I retrieved from the database chunks of records by opening context for each chunk  After processing the chunk  context closes and releases the memory  It insures that memory usage is not growing   Below is a snippet from my code      public void ProcessContextByChunks                var tableName    MyTable            var startTime   DateTime Now          int i   0           var minMaxIds   GetMinMaxIds            for  int fromKeyID  minMaxIds From  fromKeyID  lt   minMaxIds To  fromKeyID   fromKeyID  chunkSize                        try                               using  var context   InitContext                                             var chunk   GetMyTableQuery context  Where r   gt   r KeyID  gt   fromKeyID   amp  amp   r KeyID  lt  fromKeyID   chunkSize                        try                                               foreach  var row in chunk                                                        foundCount   UpdateRowIfNeeded   i  row                                                     context SaveChanges                                              catch  Exception exc                                                LogChunkException i  exc                                                                     catch  Exception exc                                LogChunkException i  exc                                   LogSummaryLine tableName  i  foundCount  startTime              private FromToRange lt int gt  GetminMaxIds                 var minMaxIds   new FromToRange lt int gt             using  var context   InitContext                          var allRows   GetMyTableQuery context               minMaxIds From   allRows Min n   gt   int  n KeyID    0                 minMaxIds To   allRows Max n   gt   int  n KeyID    0                     return minMaxIds             private IQueryable lt MyTable gt  GetMyTableQuery MyEFContext context                return context MyTable             private  MyEFContext InitContext                 var context   new MyEFContext            context Database Connection ConnectionString    connectionString            context Database Log   SqlLog          return context          FromToRange is a simple structure with From and To properties

User · Answer

Indeed you cannot save changes inside a foreach loop in C  using Entity Framework    context SaveChanges   method acts like a commit on a regular database system  RDMS     Just make all changes  which Entity Framework will cache  and then save all of them at once calling SaveChanges   after the loop  outside of it   like a database commit command   This works if you can save all changes at once

User · Answer

So in the project were I had this exact same issue the problem wasn t in the foreach or the  toList   it was actually in the AutoFac configuration we used  This created some weird situations were the above error was thrown but also a bunch of other equivalent errors were thrown   This was our fix  Changed this   container RegisterType lt DataContext gt    As lt DbContext gt    InstancePerLifetimeScope    container RegisterType lt DbFactory gt    As lt IDbFactory gt    SingleInstance    container RegisterType lt UnitOfWork gt    As lt IUnitOfWork gt    InstancePerRequest      To   container RegisterType lt DataContext gt    As lt DbContext gt    As lt DbContext gt     container RegisterType lt DbFactory gt    As lt IDbFactory gt    As lt IDbFactory gt    InstancePerLifetimeScope    container RegisterType lt UnitOfWork gt    As lt IUnitOfWork gt    As lt IUnitOfWork gt       InstancePerRequest

User · Answer

I am a little bit late  but I had this error too  I solved the problem by checking what where the values that where updating   I found out that my query was wrong and that there where over 250  edits pending  So I corrected my query  and now it works correct      So in my situation  Check the query for errors  by debugging over the result that the query returns  After that correct the query    Hope this helps resolving future problems

User · Answer

I am much late to the party but today I faced the same error and how I resolved was simple  My scenario was similar to this given code I was making DB transactions inside of nested for-each loops   The problem is as a Single DB transaction takes a little bit time longer than for-each loop so once the earlier transaction is not complete then the new traction throws an exception  so the solution is to create a new object in the for-each loop where you are making a db transaction   For the above mentioned scenarios the solution will be like this   foreach  RivWorks Model Negotiation AutoNegotiationDetails companyFeedDetail in companyFeedDetailList                    private RivWorks Model Negotiation RIV Entities  dbRiv   RivWorks Model Stores RivEntities AppSettings RivWorkEntities connString                       if  companyFeedDetail FeedSourceTable ToUpper       AUTO                                                 var company    from a in  dbRiv Company Include  Product   where a CompanyId    companyFeedDetail CompanyId select a  First                            foreach  RivWorks Model NegotiationAutos Auto sourceProduct in client Auto                                                        foreach  RivWorks Model Negotiation Product targetProduct in company Product                                                                if  targetProduct alternateProductID    sourceProduct AutoID                                                                        found   true                                      break                                                                                              if   found                                                                var newProduct   new RivWorks Model Negotiation Product                                    newProduct alternateProductID   sourceProduct AutoID                                  newProduct isFromFeed   true                                  newProduct isDeleted   false                                  newProduct SKU   sourceProduct StockNumber                                  company Product Add newProduct                                                                                    dbRiv SaveChanges            THIS BREAKS

User · Answer

I was getting this same issue but in a different situation  I had a list of items in a list box  The user can click an item and select delete but I am using a stored proc to delete the item because there is a lot of logic involved in deleting the item  When I call the stored proc the delete works fine but any future call to SaveChanges will cause the error  My solution was to call the stored proc outside of EF and this worked fine  For some reason when I call the stored proc using the EF way of doing things it leaves something open

User · Answer

As you ve already identified  you cannot save from within a foreach that is still drawing from the database via an active reader   Calling ToList   or ToArray   is fine for small data sets  but when you have thousands of rows  you will be consuming a large amount of memory   It s better to load the rows in chunks   public static class EntityFrameworkUtil       public static IEnumerable lt T gt  QueryInChunksOf lt T gt  this IQueryable lt T gt  queryable  int chunkSize                return queryable QueryChunksOfSize chunkSize  SelectMany chunk   gt  chunk              public static IEnumerable lt T   gt  QueryChunksOfSize lt T gt  this IQueryable lt T gt  queryable  int chunkSize                int chunkNumber   0          while  true                        var query    chunkNumber    0                    queryable                    queryable Skip chunkNumber   chunkSize               var chunk   query Take chunkSize  ToArray                if  chunk Length    0                  yield break              yield return chunk              chunkNumber                        Given the above extension methods  you can write your query like this   foreach  var client in clientList OrderBy c   gt  c Id  QueryInChunksOf 100            do stuff     context SaveChanges        The queryable object you call this method on must be ordered   This is because Entity Framework only supports IQueryable lt T gt  Skip int  on ordered queries  which makes sense when you consider that multiple queries for different ranges require the ordering to be stable   If the ordering isn t important to you  just order by primary key as that s likely to have a clustered index   This version will query the database in batches of 100   Note that SaveChanges   is called for each entity   If you want to improve your throughput dramatically  you should call SaveChanges   less frequently   Use code like this instead   foreach  var chunk in clientList OrderBy c   gt  c Id  QueryChunksOfSize 100         foreach  var client in chunk                   do stuff           context SaveChanges        This results in 100 times fewer database update calls   Of course each of those calls takes longer to complete  but you still come out way ahead in the end   Your mileage may vary  but this was worlds faster for me   And it gets around the exception you were seeing   EDIT I revisited this question after running SQL Profiler and updated a few things to improve performance   For anyone who is interested  here is some sample SQL that shows what is created by the DB   The first loop doesn t need to skip anything  so is simpler   SELECT TOP  100                      -- the chunk size   Extent1   Id  AS  Id     Extent1   Name  AS  Name    FROM  dbo   Clients  AS  Extent1  ORDER BY  Extent1   Id  ASC   Subsequent calls need to skip previous chunks of results  so introduces usage of row number   SELECT TOP  100                      -- the chunk size  Extent1   Id  AS  Id     Extent1   Name  AS  Name    FROM       SELECT  Extent1   Id  AS  Id    Extent1   Name  AS  Name   row number       OVER  ORDER BY  Extent1   Id  ASC  AS  row number      FROM  dbo   Clients  AS  Extent1    AS  Extent1  WHERE  Extent1   row number   gt  100   -- the number of rows to skip ORDER BY  Extent1   Id  ASC

User · Answer

Always Use your selection as List  Eg   var tempGroupOfFiles   Entities Submited Files Where r   gt  r FileStatusID    10  amp  amp  r EventID    EventId  ToList      Then Loop through the Collection while save changes   foreach  var item in tempGroupOfFiles                                  var itemToUpdate   item                   if  itemToUpdate    null                                          itemToUpdate FileStatusID   8                       itemToUpdate LastModifiedDate   DateTime Now                                      Entities SaveChanges

User · Answer

Here are another 2 options that allow you to invoke SaveChanges   in a for each loop   The first option is use one DBContext to generate your list objects to iterate through  and then create a 2nd DBContext to call SaveChanges   on   Here is an example     Get your IQueryable list of objects from your main DBContext db      IQueryable lt Object gt  objects   db Object Where whatever where clause you desire      Create a new DBContext outside of the foreach loop     using  DBContext dbMod   new DBContext               Loop through the IQueryable            foreach  Object object in objects                  Get the same object you are operating on in the foreach loop from the new DBContext dbMod  using the objects id                    Object objectMod   dbMod Object Find object id              Make whatever changes you need on objectMod         objectMod RightNow   DateTime Now             Invoke SaveChanges   on the dbMod context                  dbMod SaveChanges             The 2nd option is to get a list of database objects from the DBContext  but to select only the id s   And then iterate through the list of id s  presumably an int  and get the object corresponding to each int  and invoke SaveChanges   that way   The idea behind this method is grabbing a large list of integers  is a lot more efficient then getting a large list of db objects and calling  ToList   on the entire object   Here is an example of this method     Get the list of objects you want from your DBContext  and select just the Id s and create a list List lt int gt  Ids   db Object Where enter where clause here Select m   gt  m Id  ToList     var objects   Ids Select id   gt  db Objects Find id     foreach  var object in objects        object RightNow   DateTime Now      db SaveChanges

User · Answer

In my case  the problem appeared when I called Stored Procedure via EF and then later SaveChanges throw this exception  The problem was in calling the procedure  the enumerator was not disposed  I fixed the code following way   public bool IsUserInRole string username  string roleName  DataContext context                 var result   context aspnet UsersInRoles IsUserInRoleEF      username  roleName         using here solved the issue    using  var en   result GetEnumerator               if   en MoveNext           throw new Exception  emty result of aspnet UsersInRoles IsUserInRoleEF         int  resultData   en Current        return resultData    1   1   success  see T-SQL for return codes

User · Answer

I was also facing same issue   Here is the cause and solution   http   blogs msdn com b cbiyikoglu archive 2006 11 21 mars-transactions-and-sql-error-3997-3988-or-3983 aspx  Make sure before firing data manipulation commands like inserts  updates  you have closed all previous active SQL readers   Most common error is functions that read data from db and return values  For e g functions like isRecordExist   In this case we immediately return from the function if we found the record and forget to close the reader

User · Answer

Making your queryable lists to  ToList   and it should work fine

User · Answer

We started seeing this error  New transaction is not allowed because there are other threads running in the session  after migrating from EF5 to EF6    Google brought us here but we are not calling SaveChanges   inside the loop  The errors were raised when executing a stored procedure using the ObjectContext ExecuteFunction inside a foreach loop reading from the DB   Any call to ObjectContext ExecuteFunction wraps the function in a transaction  Beginning a transaction while there is already an open reader causes the error   It is possible to disable wrapping the SP in a transaction by setting the following option     context Configuration EnsureTransactionsForFunctionsAndCommands   false    The EnsureTransactionsForFunctionsAndCommands option allows the SP to run without creating its own transaction and the error is no longer raised   DbContextConfiguration EnsureTransactionsForFunctionsAndCommands Property

User · Answer

If you get this error due to foreach and you really need to save one entity first inside loop and use generated identity further in loop  as was in my case  the easiest solution is to use another DBContext to insert entity which will return Id and use this Id in outer context  For example       using  var context   new DatabaseContext                              using  var context1   new DatabaseContext                                             context1 SaveChanges                                                 get id of inserted object from context1 and use is           context SaveChanges

[c#] SqlException from Entity Framework - New transaction is not allowed because there are other threads running in the session

Examples related to c#

Examples related to entity-framework

Examples related to transactions

Examples related to inversion-of-control