Mimicking SQL Insert Trigger with LINQ-to-SQL

Question

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.

Answer 1

The Add method only sets up a link between the two objects: it doesn't mark the added item for insertion into the database. For that, you need call InsertOnSubmit on the Table<Child> instance contained within your DataContext. The trouble, of course, is that there's no innate way to access your DataContext from the method you describe.

You do have access to it by implementing InsertParent in your DataContext, so I'd go that route (and use InsertOnSubmit instead of Add, of course).

EDITED I assumed that the partial method InsertParent would be called by the DataContext at some point, but in looking at my own code that method appears to be defined but never referenced by the generated class. So what's the use, I wonder?

Answer 2

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.