The transaction associated with the current connection has completed but has not been disposed

Question

I'm following the code at Fastest Way of Inserting in Entity Framework

I'm getting this error message when I run this code and I can't tell what I'm doing wrong since I copied it exactly from the page. Any ideas on what I'm doing wrong?

Error message: The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.

The code seems to run fine for awhile but then it will stop almost at the same time at the line I marked below. I'm not sure what is going on.

Stacktrace:

at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update() at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.b__2(UpdateTranslator ut) at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update[T](T noChangesResult, Func`2 updateFunction) at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update() at System.Data.Entity.Core.Objects.ObjectContext.b__35() at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction) at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass2a.b__27() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation) at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction) at System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options) at System.Data.Entity.Internal.InternalContext.SaveChanges()

public static async Task startInitialMarketSymbolsDownload(string market)
        {
            try
            {
                List<string> symbolList = new List<string>();
                symbolList = getStockSymbols(market);

                var historicalGroups = symbolList.Select((x, i) => new { x, i })
                          .GroupBy(x => x.i / 50)
                          .Select(g => g.Select(x => x.x).ToArray());

                List<DailyAmexData>[] amexListArray = await Task.WhenAll(historicalGroups.Select(g => Task.Run(() => getLocalHistoricalStockData(g, market))));


                using (TransactionScope scope = new TransactionScope())
                {
                    ooplesfinanceEntities context = null;
                    List<DailyAmexData> amexList = null;

                    try
                    {
                        amexList = new List<DailyAmexData>();

                        foreach (List<DailyAmexData> singleList in amexListArray)
                        {
                            amexList.AddRange(singleList);
                        }

                        context = new financeEntities();
                        context.Configuration.AutoDetectChangesEnabled = false;

                        int count = 0;
                        foreach (var amexData in amexList)
                        {
                            ++count;
                            context = AddToContext(context, amexData, count, 100, true);
                        }

                        context.SaveChanges();
                        Console.WriteLine("Everything is finished!");
                    }
                    finally
                    {
                        if (context != null)
                            context.Dispose();
                    }

                    scope.Complete();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.InnerException.Message);
            }
        }

        private static financeEntities AddToContext(financeEntities context, DailyAmexData entity, int count, int commitCount, bool recreateContext)
        {
            context.Set<DailyAmexData>().Add(entity);

            if (count % commitCount == 0)
            {
                context.SaveChanges(); **// seems to happen at this line**
                Console.WriteLine("100 items saved");
                if (recreateContext)
                {
                    context.Dispose();
                    context = new financeEntities();
                    context.Configuration.AutoDetectChangesEnabled = false;
                }
            }

            return context;
        }

The answer you are referencing dates back 6 years. I am sure a lot has changed since then. What exactly are you looking to do? The way you have your code set up, you are wrapping a transaction within a transaction. — JuanR, Aug 10 '17 at 19:23
@Juan I have over a million rows to insert into a database so I was trying to find a quick way to get it done. I also posted the full exception stack — DarthVegan, Aug 10 '17 at 19:31
As you have now realized, EF is not exactly built for bulk operations. Do you care if the data is committed in chunks? Or do you need the million records to be committed at the same time? — JuanR, Aug 10 '17 at 19:36
@Juan I don't care if it is committed in chunks as long as it is done quickly — DarthVegan, Aug 10 '17 at 19:38
Then simply remove the `TransactionScope` and it should work. The EF wraps your changes in a transaction by default. — JuanR, Aug 10 '17 at 19:41
I am curious, how many items on average do you have in each `singleList`? — JuanR, Aug 10 '17 at 19:48
This might help: http://entityframework-extensions.net/?z=codeplex — JuanR, Aug 10 '17 at 20:44

score 1 · Accepted Answer · answered Aug 10 '17 at 21:17

1

If you need to insert one million rows then you should use a bulk insert. I wrote a generic bulk insert class and I posted the code here.

Bulk Insert of Generic List C# into SQL Server

I hope it helps.

Juan

answered Aug 10 '17 at 21:17

Juan

2,156
18
26

Did you use that code? Was that generic Bulk Insert helper in C# helpful or only the bulk insert concept? – Juan Oct 31 '17 at 05:11

The transaction associated with the current connection has completed but has not been disposed

1 Answers1