Using Linq2Sql to insert data into multiple tables using an auto incremented primary key
- by Thomas
I have a Customer table with a Primary key (int auto increment) and an Address table with a foreign key to the Customer table. I am trying to insert both rows into the database in one nice transaction.
using (DatabaseDataContext db = new DatabaseDataContext())
{
Customer newCustomer = new Customer()
{
Email = customer.Email
};
Address b = new Address()
{
CustomerID = newCustomer.CustomerID,
Address1 = billingAddress.Address1
};
db.Customers.InsertOnSubmit(newCustomer);
db.Addresses.InsertOnSubmit(b);
db.SubmitChanges();
}
When I run this I was hoping that the Customer and Address table automatically had the correct keys in the database since the context knows this is an auto incremented key and will do two inserts with the right key in both tables.
The only way I can get this to work would be to do SubmitChanges() on the Customer object first then create the address and do SubmitChanges() on that as well. This would create two roundtrips to the database and I would like to see if I can do this in one transaction. Is it possible?
Thanks
