none
SubmitChanges fails on large amount of data RRS feed

  • Question

  • Hi everybody,

     

    Please consider the following lines of code:

    MyContext context = MyContext();
    Cluster newCluster = new Cluster();
    context.Clusters.InsertOnSubmit(newCluster);
    
    List<Document> documents = new List<Documents>(/* I fill this list */)
    
    using (TransactionScope scope = new TransactionScope())
    {
      context.SubmitChanges();
      documents.ForEach(doc => doc._clusterID = newCluster.ID);
      context.Documents.InsertAllOnSubmit(documents);<br/>
      context.SubmitChange(); // exception will be thrown here
    }

    The above lines successfully execute when the amount of data which is going to be inserted is not very large, otherwise I encounter getting the following exception:

    System.InvalidOperationException: 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.
       at System.Data.SqlClient.SqlInternalConnectionTds.CheckEnlistedTransactionBinding()
       at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
       at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
       at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
       at System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicInsert(TrackedObject item)
       at System.Data.Linq.ChangeDirector.StandardChangeDirector.Insert(TrackedObject item)
       at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
       at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)

    I used SQL Profiler to see if SubmitChanges execute insert statements at all and I've to say that all the data inside documents will be inserted in database but when it finishes executing insert statements the exception will be thrown.

    Note that I increased Timeout property of TransactionScopeOption but it didn't take effect.

     

    Any idea how to solve this issue?


    learn to learn
    Thursday, June 17, 2010 2:09 PM

All replies

  • I think your problem is with the Ids.  If they are auto ints, they will not be populated until the Transaction Scope is complete.  So instead of assigning the Ids, assign the object.  As provided below

    MyContext context = MyContext();
    Cluster newCluster = new Cluster();
    context.Clusters.InsertOnSubmit(newCluster);
    
    List<Document> documents = new List<Documents>(/* I fill this list */)
    
    using (TransactionScope scope = new TransactionScope())
    {
     documents.ForEach(doc => doc.Cluster = newCluster);
     context.Documents.InsertAllOnSubmit(documents);<br/>
     context.SubmitChange(); // exception will be thrown here
    }
    

    Or you could do it like this...

    MyContext context = MyContext();
    Cluster newCluster = new Cluster();
    
    
    List<Document> documents = new List<Documents>(/* I fill this list */)
    foreach(Document doc in documents)
    {
       newCluster.Documents.Add(doc);
    }
    
    context.Clusters.InsertOnSubmit(newCluster); //Should insert documents attached also.
    context.SubmitChange();
    
    
    Thursday, June 17, 2010 2:37 PM
  • Thanks for your reply,

    Dear friend, At first I had written my code as same as what you provided me with, but as documents contains more than 3 million objects and it's really time consuming to use above codes, I ended up using the code I mentioned, moreover it didn't solve my problem.

     

    Any other idea would be appreciated


    learn to learn
    Thursday, June 17, 2010 5:27 PM
  • MyContext context = MyContext();
    Cluster newCluster = new Cluster();
    context.Clusters.InsertOnSubmit(newCluster);
    
    List<Document> documents = new List<Documents>(/* I fill this list */)
    
    using (TransactionScope scope = new TransactionScope())
    {
     context.SubmitChanges();
     documents.ForEach(doc => doc._clusterID = newCluster.ID);
     context.Documents.InsertAllOnSubmit(documents);<br/>
     context.SubmitChange(); // exception will be thrown here
     scope.Complete();
    }
    

     I am sorry I didn't see it before, but you forgot to do scope.Complete();

    Additionally I think you need to break your 3 million objects into smaller work units. 

    MSDN : "In general, a DataContext instance is designed to last for one "unit of work" however your application defines that term. A DataContext is lightweight and is not expensive to create. A typical LINQ to SQL application creates DataContext instances at method scope or as a member of short-lived classes that represent a logical set of related database operations."

    Thursday, June 17, 2010 10:26 PM
  • I typed the code, Sorry, I forgot to type scope.Complete(). but in my code I do scope.Complete(), this line of code never executes because exception is thrown on calling context.SubmitChanges()
    learn to learn
    Friday, June 18, 2010 7:57 AM