linqlinq-to-sqldatacontexttriggerssubmitchanges

Mimicking SQL Insert Trigger with LINQ-to-SQL

Using LINQ-to-SQL, I would like to automatically create child records when inserting the parent entity. Basically, mimicking how an SQL Insert trigger would work, but in-code so that some additional processing can be done.

The parent has an association to the child, but it seems that I cannot simply add new child records during the DataContext's SubmitChanges().

For example,

public partial class Parent 
{
    partial void OnValidate(System.Data.Linq.ChangeAction action)
    {
        if(action == System.Data.Linq.ChangeAction.Insert)
        {
            Child c = new Child();
            ... set properties ...
            this.Childs.Add(c);
        }
    }
}

This would be ideal, but unfortunately the newly created Child record is not inserted to the database. Makes sense, since the DataContext has a list of objects/statements and probably doesn't like new items being added in the middle of it.

Similarly, intercepting the partial void InsertParent(Parent instance) function in the DataContext and attempting to add the Child record yields the same result - no errors, but nothing added to the database.

Is there any way to get this sort of behaviour without adding code to the presentation layer?

Update: Both the OnValidate() and InsertParent() functions are called from the DataContext's SubmitChanges() function. I suspect this is the inherent difficulty with what I'm trying to do - the DataContext will not allow additional objects to be inserted (e.g. through InsertOnSubmit()) while it is in the process of submitting the existing changes to the database.

Ideally I would like to keep everything under one Transaction so that, if any errors occur during the insert/update, nothing is actually changed in the database. Hence my attempts to mimic the SQL Trigger functionality, allowing the child records to be automatically inserted through a single call to the DataContext's SubmitChanges() function.

Solution

  • If you want it to happen just before it is saved; you can override SubmitChanges, and call GetChangeSet() to get the pending changes. Look for the things you are interested in (for example, delta.Inserts.OfType<Customer>(), and make your required changes.

    Then call base.SubmitChanges(...).

    Here's a related example, handling deletes.