How to force Linq to update last edit time of a row?

Question

In database, I have a LastEditTime column of type datetime. I used this to track last row update/insert time. Linq throws exception claiming that it can not insert null at that column.

Naturally, since LastEditTime column is NOT NULL, an exception is expected. My hand generated query inserts getutcdate(). How can I ask Linq to do similar?

Answer 1

Create a partial class of whatever table it is. In the partial class have the following:

public partial class MyTable{

    partial void OnValidate(System.Data.Linq.ChangeAction action)
    {
        LastEditTime = DateTime.Now;
    }
}

OnValidate is always called before doing a database.submitchanges()

Answer 2

The simplest answer would be to assign a default value for the field in the database, i.e. getdate(). Which will ensure the field is populated with the current time/date but only if it's blank.

Linq to SQL is only an access layer on top of SQL so it's fairly dumb in that respect and you'd either need to craft the Linq to SQL classes yourself or ensure that you populate the value when you create your object instance that you are adding to the DB.

If you are looking for something that updates automatically to ensure concurrency every time you update the record then take a look at the timestamp field type.

Answer 3

You could create a trigger in the database and assign it to the update action. I know it works, we do this all the time on our Postgres database.

I don´t know if SQL Server has triggers, but you can use a stored procedure.