How do I coerce LINQ to SQL to always include a column in UPDATE statements?

Question

I have a trigger for auditing purposes that requires the presence of the audit columns in every update statement.

However, LINQ to SQL will only send the columns in an UPDATE that have changed.

Given that sometimes the same user might edit a column (and thus the audit value for "UpdatedBy" would be the same) LINQ to SQL will encounter trigger errors when attempting updates.

I've dug through Reflector and notice that SubmitChanges in DataContext is using ChangeTracker, but I can't work out if there's any good way to convince LINQ to SQL that the column has changed and so should be included. Worst solution case would be to include all fields in update statements, regardless of changes. However, the ChangeTracker reference is buried within the DataContext and doesn't allow us to inject out own ChangeTracker.

I've toyed with the idea of detaching and reattaching the entity, but this seems convoluted at best. If anyone has an idea I'd appreciate it. Cheers.

You might want to look at [this question](http://stackoverflow.com/questions/1560513/can-you-convince-a-datacontext-to-treat-a-column-as-always-dirty) — Marc Gravell, Nov 17 '10 at 04:45
Thanks, couldn't quite get the wording right but it's the *exact* same problem. For reference, I can get by if I use two data contexts, or if I do two updates (perhaps the second chasing down the audit table and modifying the latest record..), but I can't find a way to use one data context as you can't attach an instance of the same id back onto the context. Reflection might be the go. — Matt Mitchell, Nov 17 '10 at 05:17
Attempting to self-close as it is a duplicate of your question Marc. Could you post your comment as an answer so that I might accept it? Seems the best solution so far. — Matt Mitchell, Nov 23 '10 at 00:20

Matt Mitchell · Accepted Answer · 2010-11-17T04:09:26.933

Easiest solution:

You'll have to disable concurrency checking (UpdateCheck.Never) on all columns (you can shift click it in the designer, or modify your template if using T4 templates). Alternatively you'll have to copy every field in the example below (could generate a template method to do this).

In any case:

MyDataContext db = new MyDataContext();
MyDataContext db2 = new MyDataContext();
Car betsy = db.Cars.First(c => c.Name == "Betsy");
Car betsy2 = new Car();
betsy2.Id= betsy.Id;
db2.Cars.Attach(betsy2);

/* Could be GetName() or whatever, but allows same */
betsy2.UpdatedBy = betsy.UpdatedBy;  
betsy2.OtherField = "TestTestTest";   
db2.SubmitChanges();

Some other "solutions" below. Unfortunately all of these involve a double update/whatever, and don't really work if you're doing a delete. As you're auditing is probably backing up to another table somewhere you're going to want to patch in your trigger around updating the last audit entry with '|' when you get your second non '|' or something (if it's always in a transaction perhaps not the end of the world). It's all non-ideal though.

The messy solution:

MyDataContext db = new MyDataContext();
Car car = db.Cars.First(c => c.Id == 1);
car.Name = "Betsy";
car.UpdatedBy = String.Format("{0}|{1}", car.UpdatedBy, DateTime.Ticks);
db.SubmitChanges();

car.UpdatedBy = car.UpdatedBy.Substring(0, car.UpdatedBy.LastIndexOf('|'));
db.SubmitChanges();

The slightly better solution:

public partial class MyDataContext : DataContext
{
    public override void SubmitChanges(ConflictMode failureMode)
    {
        ChangeSet cs = base.GetChangeSet();
        foreach (object e in cs.Updates.Union(cs.Inserts))
        {
            PropertyInfo updatedBy = e.GetType()
                .GetProperties()
                .FirstOrDefault(p => p.Name == "UpdatedBy");

            if (updatedBy == null)
            {
                base.SubmitChanges(failureMode);
                return;
            }

            string updatedByValue = updatedBy.GetValue(e, null);
            string tempValue = String.Format("{0}|{1}", updatedByValue, DateTime.Ticks;
            updatedBy.SetValue(e, tempValue);
            base.SubmitChanges(failureMode);

            updatedBy.SetValue(e, tempValue.Substring(0, tempValue.LastIndexOf('|')));
            base.SubmitChanges(failureMode);
        }
    }
}

The best solution of this type I've found (if you're using the T4 Templates for LINQ to SQL this is even easier):

Either make a partial class file implementing a common interface for each audited entity type or modify the template so that audited entities implement a common interface, e.g.:

public interface IAuditable
{
    string UpdatedBy { get; set; }
}

Then modify your SubmitChanges as follows:

public partial class MyDataContext : DataContext
{
    public override void SubmitChanges(ConflictMode failureMode)
    {
        ChangeSet cs = base.GetChangeSet();
        foreach (object e in cs.Updates.Union(cs.Inserts))
        {
            if (typeof(IAuditable).IsAssignableFrom(e))
            {
                string tempValue = String.Format("{0}|{1}", ((IAuditable)e).UpdatedBy, DateTime.Ticks);
                ((IAuditable)e).UpdatedBy = tempValue;
                base.SubmitChanges(failureMode);

                ((IAuditable)e).UpdatedBy = tempValue.Substring(0, tempValue.LastIndexOf('|'));
                base.SubmitChanges(failureMode);
            }
        }
    }
}

How do I coerce LINQ to SQL to always include a column in UPDATE statements?

1 Answers1