No database-like triggers in LINQ, and things to look out

In my LINQ application, I was looking for a UPDATE trigger implementation (in LINQ of course, not in DB), so that whenever a record is taken offline, I can record the time and user who does that. This actually can NOT be done in database trigger since I’m not passing over the logged in user info to DB.

It turned out that it’s impossible to do it in DataContext Level as I originally hoped.

First of all, the OnxxxChanged and OnxxxChanging events are mainly for validation and logging purpose. Modification to the value won’t be saved to DB.

Another option is to override partial UpdateXXXX in DataContext class.  However you will have to implement the complete UPDATE yourself. This is not what I want. I wanted something exactly like database trigger – something you do AFTER the DB transaction, without changing the default update behavior. Besides, LINQ doesn’t provide the old/new value in that partial function call.

What I had to do is at the UI level – handling ItemUpdating of my ListView control:

if (((bool)e.OldValues[“Active”]) == true && ((bool)e.NewValues[“Active”]) == false)
{
e.NewValues[“RemoveDate”] = DateTime.Now;
e.NewValues[“RemovedBy”] = Security.GetCurrentUserName();
}
else if (((bool)e.OldValues[“Active”]) == false && ((bool)e.NewValues[“Active”]) == true)
{
e.NewValues[“RemoveDate”] = null;
e.NewValues[“RemovedBy”] = String.Empty;
}

This is disappointing.

Another thing to note is that if there is DB side INSERT/UPDATE trigger that changes table value, then the DB value might be different from the LINQ in-memory value, and will cause update error next time LINQ updates. Of course there won’t be any problem if there is a reload before the update (which will be 99% of the case).

Advertisements
This entry was posted in ASP.NET and tagged . Bookmark the permalink.

One Response to No database-like triggers in LINQ, and things to look out

  1. Alireza says:

    You said:
    Another thing to note is that if there is DB side INSERT/UPDATE trigger that changes table value, then the DB value might be different from the LINQ in-memory value, and will cause update error next time LINQ updates. Of course there won’t be any problem if there is a reload before the update (which will be 99% of the case).

    I say:
    That’s not true. You can simply set ” IsDBGenerated = true” in attributes of a property within class definition of an Entity. After insert/update you have the latest db-generated value of that property.

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