Adding items to multiple tables with linq to sql

Question

Hello Everyone,

Let's say I have two tables one called Customers and one called CustomerAddresses. Then lets say I have a Customer object which contains a property that is of the type List<Address>. This property holds a list of all the different shipping addresses a customer may have used, so it could contain 0 - n addresses in it (just one string with the whole address for simplicity sake). Now I have a collection of Customer objects which I want to load into these two tables with a linq to sql procedure. The Customers table has a column called CustomerID which is an identity column and should auto-increment. The CustomerAddresses table has a column called CustomerID (and a column called Address as well) which connects the customers to the address(es) in the table. I think my main difficulty here is figuring out how to add a List to the Tables and have the CustomerID columns match up.

Thanks in advance,

Bob

Answer 1

You add the customer rows (InsertOnSumbit), then activate the SubmitChanges() function which updates the customer objects you inserted with a newly generated ID.

using(var dc = new TestDC())
{
    var cust = new Customer();

    cust.Name = "Jack";

    dc.InsertOnSubmit(cust);

    SubmitChanges();  //  cust.Id will be updated with a new generated value
}

BTW, You can surround this code with TransactionScope for transactional support.

Enjoy.

Answer 2

I think this is what you need. I named CustomerAddresses the property you have in your customer object and holds the list of addresses.

static void SaveCustomer(Customer customer)
{
    using(var dc = new TestDC())
    {
        dc.Customers.InsertOnSubmit(customer);
        dc.SubmitChanges();

        foreach (CustomerAddress address in customer.CustomerAddresses)
        {
            address.CustomerId = customer.Id;
            dc.CustomerAddresses.InsertOnSubmit(address);
            dc.SubmitChanges()
        }
    }
}

And yes, you should put a FK between these tables, although it's not compulsory.