Linq to SQL: Dynamic insert during an update to another entity

Question

I am using Linq to SQL and have a scenario in which I want to insert into a table whenever a specific record is updated.

So lets say I have Table1 and Table2, whenever a record in Table1 is updated I want to insert a new record into Table2.

Currently I have working code that allows me to update a couple of fields in Table1 whenever it is updated, this has been achieved by creating a partial method for the UpdateTable1 function, like so:

partial void UpdateTable1(Table1 instance)
{
   //update some fields
   this.ExecuteDynamicUpdate(instance);
}

This works great, and is so useful that I want to use it to ensure the new record is always created in Table2 when Table1 is updated. This is mostly for logging purposes by the way.

So this is what I tried to do next:

partial void UpdateTable1(Table1 instance)
{
   //update some fields
   this.ExecuteDynamicUpdate(instance);
   this.ExecuteDynamicInsert(instance.ConvertToTable2());
}

(I am using an extension method here to create a Table2 record based on the Table1 instance)

The problem is I am getting the following error:

The operation cannot be performed for the entity because it is not being change tracked.

Any ideas how I can make this work?

score 3 · Accepted Answer · edited May 23 '17 at 10:24

Forget my unreliable workaround (see edit history if you must), I have found a much more reliable way to get this done!

Firstly, forget about using partial methods for this (e.g. UpdateTable1 in the question) this do not work so well unless you are modifying the data for the instance record only. So I have keep these partial methods only for my need to update the related fields in the instance.

The way to do this is to override the SubmitChanges method in your partial DataContext class. This will allow insert/update/remove of any table records before you allow the default SubmitChanges to process (and thus lock-out this functionality).

Inside the override you can access the current DataContext change set using this.GetChangeSet(), from this you can loop all changes and perform any additional table work needed (e.g. add log records).

For the example below, lets assume I have a standard database table called Table1 and I have a matching log table called Table1Log, I want to record any changes made to Table1 inside of Table1Log so the log table contains all the same fields as the original but extends that to store a DateTime (time of change) and a LogType (i.e. Add/Edit/Delete).

Here is the important parts of my final code:

partial class MyDataContext
{

    public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
    {

        var set = this.GetChangeSet();//get a list of all pending changes

        foreach (var item in set.Inserts)
        {
            AddLog(item, LogType.Add);
        }
        foreach (var item in set.Updates)
        {
            AddLog(item, LogType.Edit);
        }
        foreach (var item in set.Deletes)
        {
            AddLog(item, LogType.Delete);
        }

        base.SubmitChanges(failureMode);//allow the DataContext to perform it's default work (including your new log changes)

    }

    public void AddLog(object item, LogType logType)
    {

        //some painful type testing, so feel free to refactor this as you wish
        if(item is Table1)
        {
           var log = (item as Table1).ToLog(logType);//ToLog() is an extension method (one for each type)
           this.Table1_Logs.InsertOnSubmit(log);//add the log record ready to be submitted
        }
        else if(item is Table2)
        {
           //same again
        }
        //...repeat for each table class type

    }

}

NOTE: An alternative to the if/else type testing can be found here. I actually used this in my code and it works very nicely, I doubt there is a performance benefit but when you need to switch 50+ types it does help to keep things a bit more readable.

score 0 · Answer 2 · answered Aug 13 '12 at 15:20

0

Well, for inserting, you really only have two options. well, 3 actually.

You can add a trigger to the database. We use this for change tracking where I work. We have an update trigger that inserts a records with the changes. It's effective, and easy.

If you want to use LINQ to SQL your only options are to insert the table as you have specified, i.e. db.Table2s.InsertOnSubmit(...) and then submit the changes.

The other option, assuming you have a foreign key relationship between your log table and your entity is to assign that entity relationship via LINQ to SQL, then you can simply insert your log entity, without a reference to the data context.

partial void UpdateEntity(Entity instance)
{
    instance.Logs.Add(instance.createLogEntry());
}

When done this way, you will not need a reference to the data context, however depending on your situation this may not be an option.

answered Aug 13 '12 at 15:20

Master Morality

5,837
6
31
43

Triggers are no good due to the extra data I need to log which comes from the system. The triggers wont be able to work this out. Your options involving relationships had already occurred to me, however I tried this (just like you posted) and the code ran fine but there was no new log record afterwards - have you actually seen this working? – musefan Aug 13 '12 at 15:42
I have seen the entity method working. It basically depends on when you call it. If you call it before calling `DataContext.SubmitChanges()` then it should work, as adding in to an entity set is analogous to calling `log.MyForeignKeyId = x; db.Logs.InsertOnSubmit(x);` however if it is called AFTER your initial call to `db.SubmitChanges()` then it will not be added, you would have to call `SubmitChanges` again. – Master Morality Aug 13 '12 at 16:48
Worked it out! See my answer if you are interested ;) – musefan Aug 14 '12 at 10:38

Linq to SQL: Dynamic insert during an update to another entity

2 Answers2