Handle Insert/Update/Delete of child entity in tiered application

Recently I've done a series of posts all related to using Linq in a tiered application:

The various posts (which have been influenced by this MSDN article) have focused on a DataContext that looks like the diagram below. The Contact class generated has a child collection property of Addresses which is of type EntitySet<Address>. This distinction is important because the example deals with a complex object that has a collection of child objects rather than a single object with a bunch of primitives. You must take care to handle the items in the child collection properly.

The SaveContact() method looks like this:

public static void SaveContact(Contact contact)
{
    using (PimDataContext dataContext = CreateDataContext())
    {
        if (contact.ContactID == 0)
        {
            dataContext.Contacts.InsertOnSubmit(contact);
        }
        else
        {
            dataContext.Contacts.Attach(contact, true);
            dataContext.Addresses.AttachAll(contact.Addresses, true);
        }
        dataContext.SubmitChanges();
    }
}

This code works fine when passing a Contact object into your data access tier for any of the following conditions:

A brand new Contact to be inserted with no child addresses.
A brand new Contact to be inserted with one or multiple child addresses.
An existing Contact to be updated with no child addresses.
An existing Contact to be updated with one or multiple existing child addresses to be updated.

At first, all seems well. The problem is that there are actually a couple of gaping holes here. Consider this scenario:

A user of the application creates a brand new Contact (with no addresses) and the code inserts it just fine.
The user then goes to update the existing Contact they previously created. But the change they want to make is the ADD a new address to this existing contact.

The above code will throw this exception: "System.Data.Linq.ChangeConflictException: Row not found or changed."

This is obviously bad. What is happening is that the AttachAll() on the contact's Addresses is failing because the Attach methods are meant to be used for updates to rows that are already existing in the database. In this case, although the Contact is existing, this is a brand new Address that we are trying to insert. So what we need to do is to call AttachAll() if there are existing Addresses or the InsertAllOnSubmit() method if they are brand new addresses. One simple way to accomplish this is by adding a property to the Address via a partial class:

public partial class Address
{
    public bool IsNew
    {
        get
        {
            return this.Timestamp == null;
        }
    }
}

Here we've utilized our Timestamp column (used for Optimistic concurrency) to determine if this is brand or or existing (but this could alternatively have been done with a simple Boolean). This means our SaveContact() method can now look like this:

public static void SaveContact(Contact contact)
{
    using (PimDataContext dataContext = CreateDataContext())
    {
        if (contact.ContactID == 0)
        {
            dataContext.Contacts.InsertOnSubmit(contact);
        }
        else
        {
            dataContext.Contacts.Attach(contact, true);
            dataContext.Addresses.AttachAll(contact.Addresses.Where(a => !a.IsNew), true);
            dataContext.Addresses.InsertAllOnSubmit(contact.Addresses.Where(a => a.IsNew));
        }
        dataContext.SubmitChanges();
    }
}

Now the code works fine regardless of whether you're adding a new address to an existing contact.

So now, we're surely good to go, right? Unfortunately, we still have one big gaping hole. Consider this next scenario:

A user creates a brand new contact and this contact has an address. User saves and everything is fine.
The user then goes to update the existing Contact they previously created. But the change they want to make is the DELETE the existing address from this existing contact.

Now you've got a couple of different choices for how you want to handle this scenario. IF you have control over the client, you can keep track of whether the address was deleted or not. You can add a new IsDeleted property to your Address partial class:

public partial class Address
{
    public bool IsNew
    {
        get
        {
            return this.Timestamp == null && !this.IsDeleted;
        }
    }
 
    public bool IsDeleted { get; set; }
}

But again, the responsibility is on the client to keep track of correctly setting this property. While it is not difficult, it is not totally trivial either. If you do that, then you can now update your SaveContact() method to this:

public static void SaveContact(Contact contact)
{
    using (PimDataContext dataContext = CreateDataContext())
    {
        if (contact.ContactID == 0)
        {
            dataContext.Contacts.InsertOnSubmit(contact);
        }
        else
        {
            dataContext.Contacts.Attach(contact, true);
            dataContext.Addresses.AttachAll(contact.Addresses.Where(a => !a.IsNew), true);
            dataContext.Addresses.InsertAllOnSubmit(contact.Addresses.Where(a => a.IsNew));
            dataContext.Addresses.DeleteAllOnSubmit(contact.Addresses.Where(a => a.IsDeleted));
        }
        dataContext.SubmitChanges();
    }
}

You now finally have a SaveContact() method that takes care of all permutations. And all in all, it's a pretty straightforward 10 lines of code.

If you don't have very much control over the client you might have a situation where the absence of an Address could mean either it was deleted or it never existed to begin with. In that scenario, you're going to have to blindly do delete/insert on the addresses (i.e., you'd never run an update on an Address) which would most likely have to rely on looping over the address types and running a line of code like this for each iteration:

dataContext.ExecuteCommand("DELETE FROM addresses WHERE ContactID={0} and AddressTypeID={1}", contactID, addressTypeID);

While it works, it's pretty clumsy and bordering on a code smell whereas the first solution was much more graceful. But either way, I continue to be impressed with the flexibility of implementation choices that Linq provides.