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.

score 9 · Answer 1 · edited May 23 '17 at 12:22

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.

Works in Entity Framework (4.3.1) too, use Byte[] instead of Binary for the comparer. — John Landheer, Mar 15 '12 at 15:17

score 6 · Answer 2 · answered Feb 13 '09 at 21:32

6

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.

answered Feb 13 '09 at 21:32

Kristen

4,227
2
29
36

if you only need to test if the value has changed, you can compare Binary instances with "a == b" – Lucas Feb 13 '09 at 23:21
Thanks for your post. I now have a better understanding of why I shouldn't try to re-purpose my RowVer column. – Wayne Bloss Feb 14 '09 at 03:58
1

Actually the timestamp column is quite practical for this purpose. You don't need to worry about it wrapping unless you are making more than 2^64 modifications to a single table. For reference, that would take nearly 600,000 years at 1,000,000 writes per second. – Josh Jun 26 '12 at 06:23
1

@Josh the timestamp/rowversion is across the whole database rather than just the table. – Stephen Turner Jul 20 '17 at 15:41

score 4 · Accepted Answer · answered Feb 13 '09 at 21:13

4

// 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.

answered Feb 13 '09 at 21:13

B Z

9,363
16
67
91

Thanks for your post. It made me think about the problem more to realize that a datetime is much better because it represents a more useful piece of information. Now I can detect _how_ stale a record is, not just if it's stale. – Wayne Bloss Feb 14 '09 at 04:04
don't forget to add an index for them – Simon_Weaver Sep 19 '17 at 05:29
The problem with date/time fields and using them to compare is clock variations that can come from different clients or when synchronising data between databases, where there's no single source of time. A simple timestamp (rowversion) can often be a simple and effective approach to detect changes. – mhapps Jan 28 '20 at 11:01

score 2 · Answer 4 · edited May 23 '17 at 12:14

Following on from jaraics' answer, you could also provide an implementation for the Compare method that would allow it to work outside of a query:

public static class BinaryExtensions
{
    public static int Compare(this Binary b1, Binary b2)
    {
        if (b1 == null)
            return b2 == null ? 0 : -1;

        if (b2 == null)
            return 1;

        byte[] bytes1 = b1.ToArray();
        byte[] bytes2 = b2.ToArray();
        int len = Math.Min(bytes1.Length, bytes2.Length);
        int result = memcmp(bytes1, bytes2, len);

        if (result == 0 && bytes1.Length != bytes2.Length)
        {
            return bytes1.Length > bytes2.Length ? 1 : -1;
        }

        return result;
    }

    [DllImport("msvcrt.dll")]
    private static extern int memcmp(byte[] arr1, byte[] arr2, int cnt);
}

The use of memcmp was taken from this answer to a question on comparing byte arrays. If the arrays aren't the same length, but the longer array starts with the same bytes as the shorter array, the longer array is considered to be greater than the shorter one, even if the extra bytes are all zeroes.

Querying a timestamp column from LINQ to SQL

4 Answers4

Linked