Support SQL Server change tracking with Entity Framework 6

Question

I have an Entity Framework 6 Code First model generated from an existing SQL Server database. The database is using SQL Server Change Tracking, so for all the data manipulation operations generating from EF, I want to set the Change Tracking context to distinguish these from changes made by other external processes. This is usually done in T-SQL as
WITH CHANGE_TRACKING_CONTEXT (@source_id) UPDATE <table>...

The only thing I can think of is to prepend the above sql clause to the SQL generated by EF. Though it appears, wanting to modify SQL generated by an ORM is questionable itself. Still, even if I want to, I don't know where it can be done. Can EF Command Interception serve the purpose?

The question is specifically about SQL Server's Change Tracking feature's use alongside EF (not EF's change tracking). In terms of EF, the question is only about programmatically modifying SQL generated by EF

Answer 1

Unfortunately, Entity Framework 6 does not have built-in support for SQL Server Change Tracking. However, it does expose interception capabilities that enable you to modify the SQL it generates before execution. While changing the SQL an ORM generates is something that should be done carefully and only with good reason, there are absolutely cases where it is the appropriate solution.

EF6 exposes the IDbCommandInterceptor type, which gives you hooks into the entire query pipeline. You simple need to implement this interface and register your interceptor with EF.

Notably, the framework will call NonQueryExecuting before every INSERT, UPDATE, and DELETE, making it a great place for you to hook in your change tracking.

As a simple example, consider this interceptor:

public class ChangeTrackingInterceptor : IDbCommandInterceptor
{
    private byte[] GetChangeTrackingContext()
    {
        // TODO: Return the appropriate change tracking context data
        return new byte[] { 0, 1, 2, 3 };
    }

    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        command.CommandText = "WITH CHANGE_TRACKING_CONTEXT (@change_tracking_context)\r\n" + command.CommandText;

        // Create the varbinary(128) parameter
        var parameter = command.CreateParameter();
        parameter.DbType = DbType.Binary;
        parameter.Size = 128;
        parameter.ParameterName = "@change_tracking_context";
        parameter.Value = GetChangeTrackingContext();
        command.Parameters.Add(parameter);
    }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }
}

When EF generates any query that changes the state of the DB, it will call this method before executing the query. This gives you an opportunity to inject your custom change tracking context using standard SQL.

To register your interceptor with EF, simply call DbInterception.Add somewhere in your startup code:

var changeTrackingInterceptor = new ChangeTrackingInterceptor();
DbInterception.Add(changeTrackingInterceptor);

There's not a ton of great documentation on the IDbCommandInterceptor interface, but this MSDN article is a good place to start.