Using Linq2Sql to insert data into multiple tables using an auto incremented primary key

Question

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

Answer 1

If you have a foreign key relationship in the database the Customer object should have a collection called Addresses which you can Add your address instance called b to. If you do this, you don't have to explicitly add the address to db.Addresses it will be added by the framework automatically, and the right customer id will be inserted.

Answer 2

Klaus basically already specified 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 associate the address b with the customer you've just created, and then insert that customer into the db.Customers collection, calling db.SubmitChanges() should automatically save the address, save the customer and fix up any of the IDENTITY columns to make this work. It does work for me in a test case, for sure.

You cannot use the address' or customer's ID just yet - those haven't been set yet. But you can definitely associate the full objects with one another and thus get the "connection" between the two in place.

For this to work, you need to make sure that in the DBML designer, in the Properties window for both ID columns, the Auto Generated Value is True and the Auto-Sync is ste to OnInsert (both of which aren't the defaults).