Linq to SQL: execution order when calling SubmitChanges()

Question

I have 2 related database tables which in simplified form look like this

Product(
  product_id,
  name
)

ProductSpecs(
  spec_id,
  product_id,
  name,
  value
)

Foreign key is set via product_id field and ProductSpecs table has a unique constraint on (product_id, name) pair.

Now in my ASP.NET MVC application when user edits product specs and saves the data I delete old specs and insert all as new ones.

I do this by first calling DataContext.DeleteAllOnSubmit() and providing current (old) ProductSpecs as a parameter, and then I add new specs to the Product.ProductSpecs collection.

Then I call DataContext.SubmitChanges() and get an error that my unique constraint was violated.

By looking at the SQL statements returned by DataContenxt.GetChangeText() I can see that INSERTs are executed before DELETEs (even though I called DeleteAllOnSubmit() before Add).

What is the reason of this behavior and how to fix or workaround it?

Thanks.

Answer 1

I have been having this same problem. I came up with a hack to work around it, which seems to work for me: I manually execute some SQL for each potentially problematic delete in the change set before calling SubmitChanges():

foreach (object o in m_Db.GetChangeSet().Deletes)
{
    LibraryMember m = o as LibraryMember;

    if (m != null)
    {
        m_Db.ExecuteCommand("DELETE FROM LibraryMembers WHERE LibraryMemberId={0}", m.LibraryMemberId);
    }
}

m_Db.SubmitChanges()

Answer 2

A better solution that does not require knowing in advance what entities have changed is to use the DataContext's partial methods to intercept the inserts and push the deletes through first.

public partial class YourDataContext
{
    List<EntityX> _deletedEntities = new List<EntityX>();

    partial void InsertEntityX(EntityX instance)
    {
        // Run deletes before inserts so that we don't run into any index violations
        var deletes =
            this.GetChangeSet().Deletes
            .Where(d => d.GetType().Equals(instance.GetType()))
            .Cast<EntityX>().ToList();

        var replaced = deletes.SingleOrDefault(d => d.UniqueKeyId == instance.UniqueKeyId);

        if (replaced != null)
        {
            DeleteEntityX(replaced);
            _deletedEntities.Add(replaced);
        }

        this.ExecuteDynamicInsert(instance);
    }

    partial void DeleteEntityX(EntityX instance)
    {
        if (_deletedEntities.Contains(instance))
        {
            _deletedEntities.Remove(instance);
            return;
        }

        this.ExecuteDynamicDelete(instance);
    }
}

Answer 3

Instead of just throwing away all of the children and recreating them, consider taking a bit more effort and do this in two steps: Add the items that aren't already added and remove those that are previuosly added, but no longer desired. For example, in ThinqLinq.com site, I have posts that can be assigned to categories. In the editing screen, I supply a list of categories that can be selected and unselected. When I receive the post containing the list of selected categories, I use the following to delete and update the appropriate records:

Dim selectedCats() As String = CType(ValueProvider("categories").RawValue, String())
For Each catId In selectedCats.Except(From cp In currentPost.CategoryPosts _
                                      Select id = cp.CategoryID.ToString())
   'Add new categories
   currentPost.CategoryPosts.Add(New CategoryPost With {.CategoryID = CInt(catId)})
Next

'Delete removed categories
dc.CategoryPosts.DeleteAllOnSubmit(From cp In currentPost.CategoryPosts _
                                   Where Not selectedCats.Contains(cp.CategoryID.ToString))

Answer 4

Yes, for some reason, Linq to SQL perform all deletes as the last thing. And there is no way to change that.

Or maybe there is. I haven't looked into the codegen DataContext to see if we can override something there.

You can call SubmitChanges() as many times you want. My workaround when I need to delete as the first thing, is mark my deletions, call SubmitChanges(), then perform inserts and updates, and call SubmitChanges once again.

You can wrap everything inside of a TransactionScope:

    var deletables =
        from toDelete in db.NamedValues
        where toDelete.Name == knownValue.Name
        select toDelete;

    using (var scope = new TransactionScope())
    {
        db.NamedValues.DeleteAllOnSubmit(deletables);
        db.SubmitChanges();

        db.NamedValues.InsertOnSubmit(knownValue);
        db.SubmitChanges();

        scope.Complete();
    }

Answer 5

Another solution is to attach the changes to another data context in the correct order. However the downside to this solution is that any entities still referenced from the rest of your application that were from the original data context will now be invalid :(

I've tried a slight variation on this, creating a second temporary data context for the commit. But I can't work out how then to get the original data context into a clean state.

It's very frustrating :(

public void Save()
{
    string connectionString = m_Db.Connection.ConnectionString;
    IList<object> deletes = m_Db.GetChangeSet().Deletes;
    IList<object> inserts = m_Db.GetChangeSet().Inserts;
    IList<object> updates = m_Db.GetChangeSet().Updates;

    m_Db.Dispose();
    m_Db = new MyDataContext(connectionString);

    Attach(m_Db, deletes);
    m_Db.SubmitChanges();
    Attach(m_Db, updates);
    m_Db.SubmitChanges();
    Attach(m_Db, inserts);
    m_Db.SubmitChanges();
}

void Attach(DataContext context, IList<object> items)
{
    foreach (object o in items)
    {
        context.GetTable(o.GetType()).Attach(Clone(o));
    }
}

object Clone(object o)
{
    object result = o.GetType().GetConstructor(Type.EmptyTypes).Invoke(null);
    foreach (PropertyInfo property in o.GetType().GetProperties())
    {
        if (property.PropertyType.IsValueType)
        {
            property.SetValue(result, property.GetValue(o, null), null);
        }
    }
    return result;
}