Using Linq2Sql to insert data into multiple tables using a primary key with automatic addition

Question

I have a Customer table with a primary key (int auto increment) and an address table with a foreign key in the Customer table. I am trying to insert both rows into a 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 would automatically have the correct keys in the database, as the context knows that it is an automatically incremented key and will make two inserts with the right key in both tables.

The only way I can get this to work is to make SubmitChanges () in the Customer object first, and then create an address and make SubmitChanges (). This will create two reverse transitions to the database, and I would like to see if I can do this in one transaction. Is it possible?

thanks

Answer 1

Klaus basically already determined what you need to do - try this code:

 using (DatabaseDataContext db = new DatabaseDataContext()) { Customer newCustomer = new Customer() { Email = customer.Email }; Address b = new Address() { Address1 = billingAddress.Address1 }; newCustomer.Address = b; db.Customers.InsertOnSubmit(newCustomer); db.SubmitChanges(); } 

If you linked address b to the client you just created, then paste that client into the db.Customers collection, calling db.SubmitChanges() should automatically save the address, save the client, and correct any of the IDENTITY columns to make this work. This really works for me in a test case.

You cannot use the address or customer ID yet - they have not been set yet. But you can definitely link complete objects to each other and thus get a “connection” between the two places.

To do this, you need to make sure that in the DBML design in the Properties window for both columns of the Auto Generated Value identifier is True , and Auto-Sync is ste until OnInsert (both of which are not default values).

Answer 2

If you have a foreign key relationship in the database, the Customer object must have a collection called Addresses , which you can Add to indicate your instance of your address b to. If you do this, you do not need to explicitly add the address in db.Addresses , it will be automatically added by the framework and the correct client identifier will be inserted.