Transactions in LINQ to SQL

Question

Hello guys,

I need some help regarding transactions in Linq to sql. Below is the typical transaction layout. If any operation fails, then all the operations are rolled back.

mainTransaction (tScope)
     Operation 1 changes
     db.submitChanges()

     Operation 2 changes
     db.submitChanges()    
     ...

     catch(TransactionException ex)
         'rollback mainTransaction

     tScope.complete()

However, I would like to have a scenario where Operation 2 should be able to see the changes from Operation 1 and if any of the operations fails, then all the prior operations are to be rolled back.

I could think of nested transactions in Linq to Sql but perhaps there is a better way. I have not yet tried the nested transaction approach.

mainTransaction
    childTransaction #1 
        child 1 changes

    childTransaction #2 changes
        should see child 1 changes
        child 2 changes

    childTransaction #3 changes
        should see child 1 changes
        should see child 2 changes
        child #3 changes

catch(TransactionException ex)
    'rollback child 1
    'rollback child 2
    ...
tScope.complete()

Update: Let's say we have a parent and a child relations. The parent would have a reference to the child collection as parent.Childs EntitySet. In all the operations I pass the parent reference and fetch the child EntitySet as parent.Childs. I add child records in Childs Linq table and not the Childs EntitySet. After op# 1, I do db.submitChanges(). For op# 2, I do not see inserted records of op# 1 in parent.Childs EntitySet even after db.SubmitChanges() - but, I see in Child Linq table. Any ideas?

I want to do this using Linq to Object. Linq to Object does not seem to "see" the InsertOnSubmit records in the subsequent operations (ex: if I insert a record in the child table, the EntitySet returned from the parent's relation does not show the inserted record).

I was able to achieve this in Linq to SQL but at the cost of re-querying the whole Linq table. I would have to write duplicate code for each of the operations.

Any ideas? Please let me know if you can think of a simpler way. Thanks.

Answer 1

if the datasize of each parent table is manageable enough, I used a list(of linqToSqlRowType) to track the population and new rows added. so I queried the list for child operations where the changes weren't submitted yet.

Also in Linq to sql probably linq to objects also, you can set the foreign key row field to the not yet submitted row and it will take care of any identity fields automatically.