Bulk inserts with EntityFramework 4.0 causes abort of transaction

Question

We are receiving a file from a client (Silverlight) via WCF and on the serverside I parse this file. Each line in the file is transformed into an object and stored into the database. if the file is very large (10000 entries and more), I get the following error (MSSQLEXPRESS):

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.

I tried a lot (TransactionOptions timeout set and so on), but nothings works. The above exception message is either raised after 3000, sometimes after 6000 objects processed, but I can't succeed in processing all objects.

I append my source, hopefully somebody got an idea and can help me:

public xxxResponse SendLogFile (xxxRequest request
{
   const int INTERMEDIATE_SAVE = 100;



   using (var context = new EntityFramework.Models.Cubes_ServicesEntities())
   {
            // start a new transactionscope with the timeout of 0 (unlimited time for developing purposes)
            using (var transactionScope = new TransactionScope(TransactionScopeOption.RequiresNew,
            new TransactionOptions
            {
                IsolationLevel = System.Transactions.IsolationLevel.Serializable,
                Timeout = TimeSpan.FromSeconds(0)
            }))
            {
                try
                {
                    // open the connection manually to prevent undesired close of DB
                    // (MSDTC)
                    context.Connection.Open();
                    int timeout = context.Connection.ConnectionTimeout;

                    int Counter = 0;

                    // read the file submitted from client
                    using (var reader = new StreamReader(new MemoryStream(request.LogFile)))
                    {
                        try
                        {
                            while (!reader.EndOfStream)
                            {
                                Counter++;
                                Counter2++;
                                string line = reader.ReadLine();
                                if (String.IsNullOrEmpty(line)) continue;

                                // Create a new object
                                DomainModel.LogEntry le = CreateLogEntryObject(line);

                                // an attach it to the context, set its state to added.
                                context.AttachTo("LogEntry", le);
                                context.ObjectStateManager.ChangeObjectState(le, EntityState.Added);

                                // while not 100 objects were attached, go on
                                if (Counter != INTERMEDIATE_SAVE) continue;

                                // after 100 objects, make a call to SaveChanges.
                                context.SaveChanges(SaveOptions.None);
                                Counter = 0;
                            }
                        }
                        catch (Exception exception)
                        {
                            // cleanup
                            reader.Close();
                            transactionScope.Dispose();
                            throw exception;

                        }

                    }
                    // do a final SaveChanges
                    context.SaveChanges();
                    transactionScope.Complete();
                    context.Connection.Close();
                }
                catch (Exception e)
                {
                    // cleanup
                    transactionScope.Dispose();
                    context.Connection.Close();
                    throw e;
                }
            }

            var response = CreateSuccessResponse<ServiceSendLogEntryFileResponse>("SendLogEntryFile successful!");
            return response;
        }
    }

Try refactoring that code so something more readable. Nobody likes to wander through the mess you posted. Here is a good book that might help you create cleaner code: http://amzn.to/1G1pk. And take a look at this video: http://bit.ly/irhPnl. — Steven, May 02 '11 at 09:56

score 7 · Answer 1 · edited May 23 '17 at 11:45

7

There is no bulk insert in entity framework. You call SaveChanges after 100 records but it will execute 100 separate inserts with database round trip for each insert.

Setting timeout of the transaction is also dependent on transaction max timeout which is configured on machine level (I think default value is 10 minutes). How lond does it take before your operation fails?

The best way you can do is rewriting your insert logic with common ADO.NET or with bulk insert.

Btw. throw exception and throw e? That is incorrect way to rethrow exceptions.

Important edit:

SaveChanges(SaveOptions.None) !!! means do not accept changes after saving so all records are still in added state. Because of that the first call to SaveChanges will insert first 100 records. The second call will insert first 100 again + next 100, the third call will insert first 200 + next 100, etc.

edited May 23 '17 at 11:45

Community

1
1

answered May 02 '11 at 10:51

Ladislav Mrnka

360,892
59
660
670

Hi, thank you for your hint with the default-value of 10 minutes - i will try to check this. The rethrow of e is intended because down the source (you cannot see this), the exception will be caught and mapped into a valid response object with error-description and so on. – HarryK May 02 '11 at 12:10
I will also give the standard ADO a chance and UPPPSSS: you are right with the savechanges() behavior!!! Thanks!!! – HarryK May 02 '11 at 12:12
Ah now I understand what you mean with "inexact way of rethrowing..." – HarryK May 02 '11 at 12:27

score 1 · Answer 2 · edited Oct 28 '12 at 14:56

I had exactly same issue. I did EF code to insert bulk 1000 records each time.

I was working since the beginning, with a little problem with msDTC that I put to allow remot clients and admin , but after that it was ok. I did lot of work with this, but one day it JUST STOP WORKING.

I am getting

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.

VERY WEIRD! Sometimes the error changes. My suspect is the msDTC somehow , strange behaviors. I am changing now for not using TransactionScope!

I hate when it did work and just stop. I also tried to run this in a vm, another enourmous waste of time... My code:

    private void AddTicks(FileHelperTick[] fhTicks)
    {
        List<ForexEF.Entities.Tick> Ticks = new List<ForexEF.Entities.Tick>();

        var str = LeTicks(ref fhTicks, ref Ticks);

        using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions()
        {
            IsolationLevel = System.Transactions.IsolationLevel.Serializable,
            Timeout = TimeSpan.FromSeconds(180)
        }))
        {
            ForexEF.EUR_TICKSContext contexto = null;                
            try
            {
                contexto = new ForexEF.EUR_TICKSContext();

                contexto.Configuration.AutoDetectChangesEnabled = false;

                int count = 0;
                foreach (var tick in Ticks)
                {
                    count++;
                    contexto = AddToContext(contexto, tick, count, 1000, true);
                }
                contexto.SaveChanges();
            }
            finally
            {
                if (contexto != null)
                    contexto.Dispose();
            }
            scope.Complete();
        }   
    }

    private ForexEF.EUR_TICKSContext AddToContext(ForexEF.EUR_TICKSContext contexto, ForexEF.Entities.Tick tick, int count, int commitCount, bool recreateContext)
    {
        contexto.Set<ForexEF.Entities.Tick>().Add(tick);

        if (count % commitCount == 0)
        {
            contexto.SaveChanges();
            if (recreateContext)
            {
                contexto.Dispose();
                contexto = new ForexEF.EUR_TICKSContext();
                contexto.Configuration.AutoDetectChangesEnabled = false;
            }
        }

        return contexto;
    }

score 0 · Answer 3 · answered May 06 '11 at 21:49

0

It times out due the TransactionScope default Maximum Timeout, check the machine.config for that.

Check out this link:

http://social.msdn.microsoft.com/Forums/en-US/windowstransactionsprogramming/thread/584b8e81-f375-4c76-8cf0-a5310455a394/

answered May 06 '11 at 21:49

Bongo Sharp

9,450
8
25
35

Bulk inserts with EntityFramework 4.0 causes abort of transaction

3 Answers3