LINQ to SQL FAQ – Beware of the Trigger

I love triggers (and tiggers) but they can have nasty side-effects.

My current app has an issue logger with an Issues table and an IssueActivity sub-table. When trying to insert a new activity on an issue I was getting a "Row not found or changed" exception. Since both tables had timestamp columns it was really puzzling. I wrote a unit test, and traced the SQL being generated. Everything looked okay in the SQL, but the code failed when it ran.

Finally I stepped through the code checking what the in-memory classes had with the database version said, and noticed the timestamp on the Issue changed after I inserted the IssueActivity.

The cause was an insert trigger on IssueActivity that updated the Issue.ModifiedDate. This meant the timestamp on the database was modified, but the LINQ in-memory version was now out of date. It was essentially, doing its job.

The simple solution was to use db.Refresh(..) on the issue to tell it to reload the changed values.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s