Bulk inserts in EntityFramework 4.0 result in transaction abort.

entity-framework transactions

Question

On the serverside, I parse a file that is being sent to us by a client (Silverlight) over WCF. The database is filled with objects that represent each line of the file. The following error (MSSQLEXPRESS) occurs if the file is too big (10000 entries or more):

Although it has finished, the transaction connected to the present connection has not been disposed of. Before SQL commands may be executed via the connection, the transaction must be terminated.

I tried a lot (setting the TransactionOptions timeout, for example), but nothing seems to work. The aforementioned error notice is either shown after processing 3,000 or, in some cases, 6,000 items, however I am unable to process all of the data.

I've included my source in case it might be helpful to someone:

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;
        }
    }
1
1
5/2/2011 12:08:57 PM

Popular Answer

Zzz-5-Zzz is present in entity framework. You dialSaveChanges after 100 records, but it will carry out 100 distinct inserts with a round trip to the database for each insert.

Timeout settings for transactions are also influenced by transaction timeout limit, which is set up at the machine level (I think default value is 10 minutes). How long does it typically take for an operation to fail?

The best thing you can do is rewrite your insert logic using bulk insert or standard ADO.NET.

Btw. throw exception and throw e ? This is not the proper method to throw exceptions again.

Significant edit:

SaveChanges(SaveOptions.None) All records are still in an unchanged condition because!!! stands for do not accept modifications after saving. Consequently, the first call toSaveChanges inserted the first 100 records. First 100 will be inserted again together with the following 100 on the second call, first 200 along with the following 100 on the third call, etc.

7
5/23/2017 11:45:38 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow