Inserting into related tables inside transaction

[Dennis]

Hi,

Given two tables:

Table 1:
             table1_id
             table1_some_column

Table 2:
             table2_id
             table1_id
             table2_some_column

where a foreign key relationship exists via table1_id,

I want to do the following:

using (MyContext localContext = new MyContext())
{
        table1_type tbl1 = new table1_type();
        table2_type tbl2 = new table2_type();

        tbl1.table1_some_column = some_value;
        localContext.Add(tbl1);

        // normally, I would want to do the following:
        // tbl2.table1_id = tbl1.table1_id;
        // however, tbl1.table1_id is 0 at this point

        tbl2.table2_some_column = some_other_value;
        localContext.Add(tbl2);

        localContext.SaveChanges();                   // Bombs out here since tbl2.table1_id is not valid
}

I do not want to flush or commit table 1 just to get an ID, since that will kill my ability to rollback.

Can anyone show me how to make this work within a transaction?

Thanks,

Dennis

[Dennis]

In the absence of any responses to the original post, I'll restate the problem - hopefully not stumping the band....

Given two tables:

Table 1:
             table1_id                     <<<----
             table1_some_column             |
                                                            |
Table 2:                                               |
             table2_id                                |
             table1_id     <<<-----------------
             table2_some_column

where a foreign key relationship exists via table1_id,

I need to do the following (very common) inserts:

1. Begin Transaction
2. Insert new row into table1
3. Insert new row into table2 having its table1_id = table1.table1_id (forming simple master/detail relationship)
4. Provided no problems, repeat 2 and 3 until no more records need inserted into table1/table2
5. Commit transaction or rollback (if problems were encountered)

The ID columns are SQL identity columns.

In pseudo-code.....

using (MyContext localContext = new MyContext())<br>
{<br>
        table1_type tbl1 = new table1_type();<br>
        table2_type tbl2 = new table2_type();<br>
<br>
        tbl1.table1_some_column = some_value;<br>
        localContext.Add(tbl1);<br>
<br>
        // normally, I would want to do the following:<br>
        // tbl2.table1_id = tbl1.table1_id;<br>
        // however, tbl1.table1_id is 0 at this point<br>
<br>
        tbl2.table2_some_column = some_other_value;<br>
        localContext.Add(tbl2);<br>
<br>

localContext.SaveChanges();                   // Bombs out here since  tbl2.table1_id is not valid<br>
}

I do not want to flush or commit table 1 just to get an ID, since that will kill my ability to rollback and (flush) locks tables from being able to query them along the way...

I have read that nested transactions USED to be supported and I indeed can do the following in SQL:

1. Begin Outer Transaction
2. Begin Inner Transaction
3. Insert new row into table1  
4. Commit Inner Transaction
5. If all okay, Begin new Inner Transaction (SQL has assigned  ID to the just-committed record at this point)
6. Insert new row into table2 having its table1 = tbl1.table1_id (forming master/detail relationship)
7. Commit Inner Transaction
8. Provided no problems, repeat 2 through 7 until no more records need inserted into table1/table2
9. Commit Outer Transaction or rollback (if problems were encountered) Outer Transaction

However, it appears that such nested transactions are no longer supported by Telerik Domain Model.

Can anyone show me how to make this work without making a mess of the code?

Thanks,

Dennis

[Ivailo]

Hello Dennis,

Here is how it goes with OpenAccess ORM insert operations with foreign keys:

1) Add the parent object to the model
2) Create children objects, while assigning the parent object through the navigation property, rather than using the ID property.
3) Add the children objects to the model
4) When you call SaveChanges(), in case any of the children objects insert fails, the entire transaction will fail. Since you have specified the relation between the objects, OpenAccess ORM will take care of maintaining the linkage by first inserting the parent object to get the primary key value and then inserting the children with the proper values. 

You can find a code example with a more detailed explanations in the dedicated documentation article, within the Setting One-to-Many Relations Between Objects section. 

Another approach as you can see in the same article is to add the child objects into a collection that is automatically generated for the parent Domain Class. However, you will have to explicitly set the IsManaged property for the association in the Visual Designer to True (as shown here) in order for this approach to work.

Let us know if you need any further assistance or details on this.

Greetings,
Ivailo
the Telerik team

[Dennis]

Thanks, Ivailo. I'll give that a try.

Dennis