none
Delete and Insert in the same transaction RRS feed

  • Question

  • I'm trying to do the following with LINQ To SQL:

    1 - Start a transaction;
    2 - Get a Table<> object from the DataContext, using dataContext.GetTable<>();
    3 - Filter some data and store some objects in a temporary list, using table.Where().Select().ToList();
    4 - Iterate the temporary list and delete the objects from the table, using table.DeleteOnSubmit(object);
    5 - Submit the changes, using dataContext.SubmitChanges();
    6 - Create some new objects and insert them in the table, using table.InsertOnSubmit(object);
    7 - Submit the changes again;
    9 - Commit the transaction.

    When I do the last SubmitChanges, the program freezes and after some time I receive a timeout exception.
    If I try, insead of SubmitChanges, to execute any method that enumeration in the table, like Any(), Single(), Where(), ToList(), the same error occurrs. Nothing in the table works, until I commit the transaction.

    Resume: After delete something and submit the changes, my Table<> object does not work anymore.

    I tryied everything I could. All I can do to workaround it is to split the sequence in two transaction, commiting and restarting a new one after the step 5.

    Anyone can help me?
    Wednesday, September 24, 2008 3:37 PM

Answers

  • Question solved!

     

    My problem was that, in my mess (A framework running over LinqToSql), I used two DataContexts. One for reading and another for writing, but I forgot to put both to use the same Connection and the same Transaction, so the reading DataContext did not see the writing DataContext changes and get locked.

     

    Wednesday, September 24, 2008 8:08 PM

All replies

  • Question solved!

     

    My problem was that, in my mess (A framework running over LinqToSql), I used two DataContexts. One for reading and another for writing, but I forgot to put both to use the same Connection and the same Transaction, so the reading DataContext did not see the writing DataContext changes and get locked.

     

    Wednesday, September 24, 2008 8:08 PM
  •  Rafael Romão wrote:

    Question solved!

     

    My problem was that, in my mess (A framework running over LinqToSql), I used two DataContexts. One for reading and another for writing, but I forgot to put both to use the same Connection and the same Transaction, so the reading DataContext did not see the writing DataContext changes and get locked.

     

     

    You may want to take a look at System.Transactions.TransactionScope - that way you don't have to worry about manually handling transactions and connections.

    Thursday, September 25, 2008 2:46 AM
    Answerer