Introduction

Last week at MIX we announced proposed extensions to the OData protocol for obtaining changes (deltas) to the results of an OData Request. The extensions provide a hypermedia-driven model for obtaining a "Delta Link" from a delta-enabled feed that can later be used to obtain changes from the original results.

Along with the announcement, we released a new CTP of WCF Data Services named "Microsoft WCF Data Services For .NET March 2011 Community Technical Preview with Reference Data Caching Extensions". As the (incredibly long) name implies, this CTP builds on (and is fully side-by-side with) the functionality exposed in the "Microsoft WCF Data Services March 2011 CTP 2 for .NET Framework 4", adding the ability to expose and consume delta links in WCF Data Services.

This "Reference Data Caching CTP" is accompanied by a walkthrough that shows how to create a code-first entity model that supports delta links, and how to expose that model through WCF Data Services. The combination of Code-First for ease of use and delta support for change tracking makes it easy to build reference data caching applications without having to worry about the details of tracking changes in the database. But what if you have existing data that you want to expose and track changes on? In this write-up we explore how to delta-enable your service for more advanced scenarios by looking at the WCF Data Services component in a little more detail, and showing how to expose and consume delta queries over existing entity models.

Please keep in mind that this CTP is only a preview of the Reference Data Caching Extensions and is subject (okay, guaranteed) to change, but this should be representative of the way we are thinking about defining the functionality and we'd love to get your input.

Reference Data Caching with the Entity Framework

As part of this CTP, the Data Service Provider for the Entity Framework has been enhanced to support getting and applying "delta tokens" to queries against your entity model. While the production version will likely be based on extensions built into the Entity Framework, this release works with the existing Entity Framework version 4.0. To support delta tokens in this release you need to a single new (possibly virtual) entityset for tombstones, along with a separate (possibly virtual) entityset for each delta-enabled entityset. This "deltaset" exposes LastChange values; Int64 values that are guaranteed to increase with each change to a set.

Exposing the ability to request LastChange and tombstone values through the model enables delta tracking to be supported on top of a variety of different implementations. For example, a database may track change information through rowversion (i.e., timestamp) columns in base tables, insert/update/delete triggers to populate physical side tables, or storage-specific built-in change tracking mechanisms.

The current CTP relies on naming conventions for determining the entitysets in the entity model that contain the delta information. In a future version we expect to use annotations to allow you to explicitly specify the source for delta information for a given entityset.

Exposing LastChange values through your Entity Model

In order to track changes against an entityset in your model, define an entityset in your Entity Model named "Get[EntityTypeName]Deltas" (for example: "GetCustomerDeltas.") This rather strange naming convention is in anticipation of using composable table valued functions in a future version of the Entity Framework.

This “deltaset” is expected to have the same key fields as the target entityset, along with an Int64 column named (again, currently by convention) "LastChange." It may be backed by an actual table (or view) in the database, or may be defined through a defining query in SSDL.

Example: Exposing LastChange values for Northwind.Customers

Taking the Northwind Customers table as an example, the easiest way to expose a LastChange column is by adding a timestamp (rowversion) column to the table:

alter table Customers add LastChange rowversion

This column doesn't need to be exposed (mapped) in the base entityset in your entity model (i.e., Customers), but can be exposed as a separate (virtual) entityset in the SSDL section of your .edmx file through something like the following:

<EntitySet Name="GetCustomerDeltas" EntityType="NorthwindModel.Store.CustomersDelta">
    <DefiningQuery>
        Select CustomerID, convert(bigint, LastChange) as LastChange
        From [Customers]
    </DefiningQuery>
</EntitySet>

Here NorthwindModel.Store.CustomersDelta entity type is defined as:

<EntityType Name="CustomersDelta">
    <Key>
        <PropertyRef Name="CustomerID" />
    </Key>
    <Property Name="CustomerID" Type="nchar" Nullable="false" MaxLength
="5" />
    <Property Name="LastChange" Type="bigint" StoreGeneratedPattern="Computed" />
</EntityType>

Having defined this virtual entity set in your storage model, you can use the designer to map it to an entityset named "GetCustomerDeltas" in your entity model.

Note that this entityset does not need to be exposed by your DataService. The set of entitysets that are exposed by your WCF Data Service are controlled through the configuration object passed to the InitializeService method of your DataService:

config.SetEntitySetAccessRule("GetCustomerDeltas", EntitySetRights.None);

Note that EntitySetRights.None is the default, so unless you’ve given read rights to “*” (which is not recommended) you should not need to add this line.

Repeat the above steps to define additional “deltasets” for each entityset for which you want to track changes.

Exposing Tombstones through your Entity Model

In order to support deletions through the Data Service Provider for the Entity Framework, you need to expose an additional entityset named (again by convention) “GetTombstones”. The entities returned by this entityset must contain the following properties:

TableName – The name of the entityset mapped to the table whose row was deleted

KeyValues – A string representation of the key values of the deleted row. This must match the format of Key Values in an OData key reference; strings must be enclosed in single quotes and multi-part keys must be comma separated, in the order they appear in the entity type declaration, and qualified with “[KeyPropertyName]=” (for example, “OrderID=123,ProductID=1”).

DeletedTime – The DateTime value when the column was deleted

LastChange – The delta value for the deletion

In the future we will likely have separate tombstone tables for each entityset so that we can break keys out as individual columns, but for now we merge them into a single “KeyValues” column as described above.

Example: Exposing Tombstones for Northwind.Customers

Going back to the Northwind database, you can expose tombstones by first creating a table to contain information for deleted rows.

CREATE TABLE [dbo].[GetTombstones](
      [TableName] [nvarchar](125) NOT NULL,
      [KeyValues] [nvarchar](255) NOT NULL,
      [DeletedTime] [datetime] NOT NULL,
      [LastChange] [timestamp] NOT NULL,
      CONSTRAINT [PK_Deletions] PRIMARY KEY CLUSTERED (
               [TableName] ASC,
               [KeyValues] ASC,
               [LastChange] ASC))

In order to populate the tombstone table when a customer is deleted, you can define a DELETE trigger on your Customers table:

CREATE TRIGGER [dbo].[Customers_Delete] 
     ON [dbo].[Customers]
     AFTER DELETE
AS
BEGIN
     SET NOCOUNT ON
;
     INSERT INTO Tombstones(TableName, KeyValues, DeletedTime) (
         SELECT 'Customers' as TableName,
               '''' + CustomerID + '''' as KeyValues,
               getdate() as DeletedTime
         FROM Deleted )
END

Define similar DELETE triggers for each additional table you on which you want to track deletions.

Finally, map this table to an EntitySet in your EntityModel named “GetTombstones”. Again, this entityset does not have to be exposed through your data service; it will be picked up and used by the Data Service Provider for the Entity Framework.

This example shows one way of mapping a delta-enabled model that can be applied to virtually any store with simple timestamp and trigger capabilities. But by using the Entity Framework to decouple how changes are tracking in the database from how delta information is exposed in the entity model we can support a number of different change-tracking capabilities, such as built-in change tracking functions or more advanced Change Data Capture (CDC) functionality in Microsoft SQL Server 2008.

Referencing the Delta-Enabled WCF Data Services CTP in your Service

Finally, to use the Delta-enabled WCF Data Services you will need to reference the delta-enabled assemblies, Microsoft.Data.Services.Delta.dll and Microsoft.Data.Services.Delta.Client.dll, instead of the shipping System.Data.Services.dll and System.Data.Services.Client.dll. In addition, you will need to make sure that the mark-up on your .svc class references the correct assembly. To do this, right-click your .svc file, select “View Markup”, and change “System.Data.Services” to “Microsoft.Data.Services.Delta” as in the following example:

Change:

<%@ ServiceHost Language="C#" Factory="System.Data.Services.DataServiceHostFactory, System.Data.Services, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Service="MyDataService" %>

To:

<%@ ServiceHost Language="C#" Factory="System.Data.Services.DataServiceHostFactory, Microsoft.Data.Services.Delta, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Service="MyDataService" %>

Assuming you have set everything up correctly, you should see a delta link returned on the last page of results from any “delta-capable” request against your entityset. To be delta-capable, the request must not contain any conditions on non-key fields and must not include $expand.

Reference Data Caching with Custom Providers

In addition to supporting delta tracking on top of a delta-enabled Entity Framework model, this WCF Data Services CTP supports delta tracking against custom providers through a new IDataServiceDeltaProvider interface. Stay tuned for information on implementing this interface in order to support delta tracking over your custom Data Service Provider.

In the meantime, looking forward to your thoughts & comments, either in the comments for this blog post or in our prerelease forums here.

Michael Pizzo
Principal Architect
Data Frameworks