Querying a timestamp column from LINQ to SQL

Question

My table has a timestamp column named "RowVer" which LINQ maps to type System.Data.Linq.Binary. This data type seems useless to me because (unless I'm missing something) I can't do things like this:

// Select all records that changed since the last time we inserted/updated.
IEnumerable<UserSession> rows = db.UserSessions.Where
( usr => usr.RowVer > ???? );

So, one of the solutions I'm looking at is to add a new "calculated column" called RowTrack which is defined in SQL like this:

CREATE TABLE UserSession
(
RowVer timestamp NOT NULL,
RowTrack  AS (convert(bigint,[RowVer])),
-- ... other columns ...
)

This allows me to query the database like I want to:

// Select all records that changed since the last time we inserted/updated.
IEnumerable<UserSession> rows = db.UserSessions.Where
( usr => usr.RowTrack > 123456 );

Is this a bad way to do things? How performant is querying on a calculated column? Is there a better work-around?

Also, I'm developing against Sql Server 2000 for ultimate backwards compatibility, but I can talk the boss into making 2005 the lowest common denominator.

Answer 1

AS Diego Frata outlines in this post there is a hack that enables timestamps to be queryable from LINQ.

The trick is to define a Compare method that takes two System.Data.Linq.Binary parameters

public static class BinaryComparer
{
 public static int Compare(this Binary b1, Binary b2)
 {
 throw new NotImplementedException();
 }
}

Notice that the function doesn't need to be implemented, only it's name (Compare) is important.

And the query will look something like:

Binary lastTimestamp = GetTimeStamp();
var result = from job in c.GetTable<tblJobs>
             where BinaryComparer.Compare(job.TimeStamp, lastTimestamp)>0
             select job;

(This in case of job.TimeStamp>lastTimestamp)

EDIT: See Rory MacLeod's answer for an implementation of the method, if you need it to work outside of SQL.

Answer 2

SQL Server "timestamp" is only an indicator that the record has changed, its not actually a representation of Date/Time. (Although it is suppose to increment each time a record in the DB is modified,

Beware that it will wrap back to zero (not very often, admittedly), so the only safe test is if the value has changed, not if it is greater than some arbitrary previous value.

You could pass the TimeStamp column value to a web form, and then when it is submitted see if the TimeStamp from the form is different to the value in the current record - if its is different someone else has changed & saved the record in the interim.

Answer 3

// Select all records that changed since the last time we inserted/updated.

Is there a better work-around?

Why not have two columns, one for createddate another for lastmodifieddate. I would say that is more traditional way to handle this scenario.