none
Order of InsertOnSubmit! RRS feed

  • Question

  • hi , 

     my first question is does linq to sql context class maintains the order in which insert on submit is called ? like if i have a table which has a foreign key , so first i need to put value in the master table than in the other table ( which has foreign key ) . so does context class on linq to sql makes sure if order is correct .

    P.S , i test with a small code and it worked . , but again , i wanted to confirm it

    Tuesday, April 27, 2010 6:11 PM

Answers

  • If you have a table with a foreign key column and you insert a row you must provide the foreign key unless you allow nulls.

    So to answer your question you must first insert into the primary table, obtain an ID, then insert into the FK table, in that order.

     

    Tuesday, April 27, 2010 9:02 PM
  • If you have a table with a foreign key column and you insert a row you must provide the foreign key unless you allow nulls.

    So to answer your question you must first insert into the primary table, obtain an ID, then insert into the FK table, in that order.

     


    That's not strictly true. Your class which represents the table containing the foreign key column should have an "[Association=..." property which refers to the main table. This is otherwise known as a navigation property. You can use this to get the auto-generated identity in a single SubmitChanges.

    For example: Table1 has a primary key column 'id' which is IDENTITY(1,1). Table2 has a foreign key column 'fk_id' pointing at Table1.id. Your Table2 class should have an "[Association=..." property called something like "Table1". Then you would do these steps

    1. create your new Table1 object ... Table1 tbl1 = new Table1 ();
    2. InsertOnSubmit(tbl1)
    3. create your new Table2 object ... Table2 tbl2 = new Table2();
    4. Associate your Table1 object to your Table2 object ... tbl2.Table1 = tbl1;
    5. InsertOnSubmit(tbl2)
    6. SubmitChanges()

    You could repeat steps 3, 4 and 5 any number of times so have multiple Table2 records all with the fk_id pointing at the new Table1 record.

    After this the tbl1.id and tbl2.fk_id columns will have the auto-identity value. Note that you don't have to write anything into tbl1.id or tbl2.fk_id.

    Tuesday, April 27, 2010 10:07 PM
  • No don't set both - one or the other. You don't have to do step 4 if you already know the value of the foreign key. But if that value is generated by the database (i.e. the associated table's key is generated when the record is inserted) then you do not know the real value until after the SubmitChanges so you need to use step 4.

    Related to your other question; all of these inserts for Table1 and Table2 are 'sent' to the database in step 6 & are treated as atomic. If you have any type of error (SqlException, ChangeConflictException) then none of them will be committed to the database.

    However if you added another SubmitChanges in step 2.5, then you would need to wrap it with a TransactionScope otherwise the Table1 record would persist even if there were an error inserting Table2 record.

    Because of the navigation property, I have only had to use explicit TransactionScopes about 3 times.

    Wednesday, April 28, 2010 8:47 AM

All replies

  • If you have a table with a foreign key column and you insert a row you must provide the foreign key unless you allow nulls.

    So to answer your question you must first insert into the primary table, obtain an ID, then insert into the FK table, in that order.

     

    Tuesday, April 27, 2010 9:02 PM
  • If you have a table with a foreign key column and you insert a row you must provide the foreign key unless you allow nulls.

    So to answer your question you must first insert into the primary table, obtain an ID, then insert into the FK table, in that order.

     


    That's not strictly true. Your class which represents the table containing the foreign key column should have an "[Association=..." property which refers to the main table. This is otherwise known as a navigation property. You can use this to get the auto-generated identity in a single SubmitChanges.

    For example: Table1 has a primary key column 'id' which is IDENTITY(1,1). Table2 has a foreign key column 'fk_id' pointing at Table1.id. Your Table2 class should have an "[Association=..." property called something like "Table1". Then you would do these steps

    1. create your new Table1 object ... Table1 tbl1 = new Table1 ();
    2. InsertOnSubmit(tbl1)
    3. create your new Table2 object ... Table2 tbl2 = new Table2();
    4. Associate your Table1 object to your Table2 object ... tbl2.Table1 = tbl1;
    5. InsertOnSubmit(tbl2)
    6. SubmitChanges()

    You could repeat steps 3, 4 and 5 any number of times so have multiple Table2 records all with the fk_id pointing at the new Table1 record.

    After this the tbl1.id and tbl2.fk_id columns will have the auto-identity value. Note that you don't have to write anything into tbl1.id or tbl2.fk_id.

    Tuesday, April 27, 2010 10:07 PM
  • hi again,

    the only difference b/w what you suggested and what i tested is in step 4 . i setted the foreign key and not the whole object.and it worked fine.

    what should be done , should i set both ?

    Wednesday, April 28, 2010 7:41 AM
  • No don't set both - one or the other. You don't have to do step 4 if you already know the value of the foreign key. But if that value is generated by the database (i.e. the associated table's key is generated when the record is inserted) then you do not know the real value until after the SubmitChanges so you need to use step 4.

    Related to your other question; all of these inserts for Table1 and Table2 are 'sent' to the database in step 6 & are treated as atomic. If you have any type of error (SqlException, ChangeConflictException) then none of them will be committed to the database.

    However if you added another SubmitChanges in step 2.5, then you would need to wrap it with a TransactionScope otherwise the Table1 record would persist even if there were an error inserting Table2 record.

    Because of the navigation property, I have only had to use explicit TransactionScopes about 3 times.

    Wednesday, April 28, 2010 8:47 AM