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 the same problem. Did you find a solution ?

Answer 2

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=" + 
            m.LibraryMemberId);
    }
}

m_Db.SubmitChanges()

Answer 3

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;
}

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

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 6

There is a way to modify the order of the operations while calling SubmitCahnges. Have a look at the solution I have implemented here