Predefined order of executed SQL statements in Linq 2 Sql

On one of the projects we used Linq 2 Sql as ORM for our domain model in Sharepoint application. It doesn’t give you 100% persistence ignorance, however from my point of view it is good alternative keeping in mind that in Sharepoint 2010 we are limited with .Net 3.5 and can’t use e.g. Entity Framework starting from 4 version where codefirst approach was introduced. NHibernate is also can’t be applied in some projects with strong enterprise requirements (don’t ask me why, often we just should live with it). I’m going to write separate post(s) about building testable DDD-style infrastructure for projects with help of Linq 2 Sql soon. Here I would like to share one of the problems which we faced during implementation.

We use transactional logic via modified UnitOfWork pattern. With Linq 2 Sql transactions are implemented via generated DataContext class. We make all modifications in DataContext instance and when everything is ready call DataContext.SubmitChanges(). Here problem came out: SQL statements are executed in the following order:

• Insert
• Update
• Delete

regardless of how you called these operations when perform updates in DataContext. It may cause problems if there are e.g. unique key constraints on the table. E.g. imagine that we have 2 entities:

• Company
• Contact

with one-to-many relation. I.e. each Company may have multiple Contacts:

Contact.Name should be unique, so we add unique key constraint to the table Contact on Name column. Then we need to update some company and its contacts. In order to update contacts the simplest approach is to remove all old contacts and add new one:

   1: while (company.Contacts.Count > 0)

   2: {

   3:     company.Contacts.RemoveAt(0);

   4: }

   5:  

   6: foreach (var contact in newContacts)

   7: {

   8:     company.Contacts.Add(contact);

   9: }

With this approach you don’t need to care about synchronization of each field in each contact. But what will happen when we will call SubmitChanges? As Linq 2 Sql executes inserts before deletes – it will add contacts which of course may be the same as previous if no changes were made in them. As Contact.Name field is unique – we will get violation of unique key constraint exception.

There are some workarounds available, e.g. here: Workaround LINQ to SQL annoying limitations. Author used reflection in order to change operations order, but I can’t be sure that it doesn’t have any side effects so I didn’t use it in production code. Instead we had to implement more complicated mechanism for updates. Instead of deleting all child contacts and inserting new ones we calculated difference between existing contacts set and new contacts set in order to determine which contacts should be deleted and which added. Also we calculated intersection of 2 sets in order to get list of contacts which should be updated. With this approach predefined operations order in Linq 2 Sql won’t cause exception (assuming that Id and unique Name will remain in one-to-one relation, i.e. that we won’t delete existing item and create new one with the same Name. Instead we will update existing item):

   1: private void syncContacts(Company existingCompany, Company newCompany)

   2: {

   3:     if (existingCompany.Contacts.IsNullOrEmpty() &&

   4:         newCompany.Contacts.IsNullOrEmpty())

   5:     {

   6:         // both are empty - nothing to synchronize

   7:         return;

   8:     }

   9:  

  10:     if (existingCompany.Contacts.IsNullOrEmpty() &&

  11:         !newCompany.Contacts.IsNullOrEmpty())

  12:     {

  13:           // source doesn't contain contacts, but target does. Remove target contacts

  14:           while (newCompany.Contacts.Count > 0)

  15:           {

  16:               newCompany.Contacts.RemoveAt(0);

  17:           }

  18:         return;

  19:     }

  20:  

  21:     if (!existingCompany.Contacts.IsNullOrEmpty() &&

  22:         newCompany.Contacts.IsNullOrEmpty())

  23:     {

  24:         // source contains contacts, but target doesn't. Add contacts to target

  25:         existingCompany.Contacts.ToList().ForEach(c => to.Contacts.Add(c));

  26:         return;

  27:     }

  28:  

  29:     // both source and target contain contacts. Need syncronize them

  30:     var itemsToAdd = existingCompany.Contacts

  31:         .Where(s => newCompany.Contacts.All(t => t.Name != s.Name)).ToList();

  32:     var itemsToDelete = newCompany.Contacts

  33:         .Where(t => existingCompany.Contacts.All(s => s.Name != t.Name)).ToList();

  34:  

  35:     // delete

  36:     itemsToDelete.ForEach(i => to.Contacts.Remove(i));

  37:  

  38:     // update

  39:     existingCompany.Contacts.ToList().ForEach(

  40:         s =>

  41:             {

  42:                 var t = newCompany.Contacts.FirstOrDefault(c => c.Name == s.Name);

  43:                 if (t != null)

  44:                 {

  45:                     this.updateContact(s, t);

  46:                 }

  47:             });

  48:  

  49:     // add

  50:     itemsToAdd.ForEach(i => newCompany.Contacts.Add(i));

  51: }

Here I used convenient extension method IsNullOrEmpty() for enumerations (see Checking For Empty Enumerations). This is more complicated way, however it allowed to avoid violation of unique key constraint. Hope it will help someone who will encounter with the same issue with Linq 2 Sql in their projects.