title: Multiple LINQ to SQL insert using IDENTITY from previous insert
body: <p>I want to try and insert into multiple tables in my SQL Server database, however one of my first insert generates a foreign key that is IDENTITY value that I want to use it in my subsequent inserts.  I am not sure how I go about with this in LINQ to SQL.  I think I can do it in multiple transactions but I prefer to do this in one place ... aka within the using clause.</p>
  
  <p>My pseudo code algorithm is as follow:</p>
  
  <ol>
  <li>Check if ID value exist in TABLE1.COL2 column </li>
  <li>If it does not exist then insert a new row into TABLE1</li>
  <li>Get the foreign key value of that newly inserted row from TABLE1.COL1 column.</li>
  <li><p>Create an object with the new foreign key value and update TABLE2.</p>
  
  <pre><code> using (var sms = new SmsDataDataContext(connection_string)
   {
      foreach(SomeObject i in ListofObject)
      {
        TABLE1 t1 = CheckID(sms, i.ID);
  
        if (t1== null)
        {
           TABLE1 new_row = new TABLE1();
           sms.TABLE1.InsertOnSubmit(new_row);
  
           //Ideally I want to do something like this even though i dont think it will work.
           sms.SubmitChanges();
  
  
           TABLE2 update_row = new TABLE2();
           update_row.ID = new_row.COL1.value;  //has the newly created identity value from my last insert.
           //Assume this update_row exist in my TABLE2 table.
           sms.TABLE2.InsertOnSubmit(update_row);
  
        }
      }
      sms.SubmitChanges();
    }
  </code></pre></li>
  </ol>