[Giagnocavo]Michael::Write() - SQL 2008 Change Tracking with LINQ-to-SQL

Thursday, September 18, 2008

SQL 2008 Change Tracking with LINQ-to-SQL

I hacked up a little class to enable us to use SQL 2008's Change Tracking feature with LINQ-to-SQL. Change Tracking allows you to see which keys (and optionally columns) have changed in the database from a specific version. The SQL docs have a great overview with lots of examples and information.

Basically, we get a special CHANGETABLE function to SELECT from, which gives us the change information and keys. Additionally, there is the issue of versioning. Changes are only kept so long, so we want to make sure the last version we sync'd is still compatible, otherwise we have to re-initialize.

Finally, in order for our change SELECTs to be coherent, we need to snapshot the database. The easiest way to get this is by turning on Snapshot Isolation. Snapshot isolation allows us to read a virtual snapshot of the database. Any changes made from when we begin our transactions are not visible to us and we do not lock anything we read.

Here's an excerpt from a class I have to provide change tracking for our database:

public DbDataChangeProvider(long lastVersion) {
    this.lastVersion = lastVersion;
    this.txScope = ChangeTracking.GetSnapshotScope();
    
    var validV = ChangeTracking.GetValidVersionForAll(dataContext);
    baseline = lastVersion < validV;
    currentVersion = ChangeTracking.GetCurrentVersion(dataContext);
}

We take in the last version, then initialize a SnapshotScope. We get the minimum valid version and see if we're going to have to generate a baseline (re-init) or not. Next, we grab the current version of the database, so consumers can save the version for when they sync up next.

To get changed keys, you can do this:
ChangeTracking.GetChangedKeys<string>(dataContext, "Accounts", "AccountName", lastVersion, System.Data.Linq.ChangeAction.Delete);

This will give you an enumeration of all the Deleted keys; use other ChangeActions to get Insert or Updated. There's also a filter (SQL string) to limit further.

To get changed _items_, you can pass in a Queryable, like this:
ChangeTracking.GetChangedItems<Account>(dataContext, dataContext.Accounts.Where(a=>a.Balance>10), "Accounts", "AccountName", lastVersion);

The code should (seems to work for me) figure out your query and inject the JOIN to the CHANGETABLE function. The code is linked at the end of this article. Some of the functions use a Tuple type; if you don't have it, I've posted it elsewhere on this site. Or, you can delete those methods; they are only for 2-key tables.

ChangeTracking.cs.txt (9 KB)

Code | Misc. Technology

Thursday, September 18, 2008 2:03:46 AM UTC

Comments [0] | Trackback

OpenID
Please login with either your OpenID above, or your details below.

Name
E-mail
Home page

	Remember Me
Comment (HTML not allowed)
Enter the code shown (prevents robots):
Live Comment Preview