How to use SQL Server Change Tracking with Entity Framework

Question

SQL Server has a very useful capability called Change Tracking that enables clients to track update and insert on data in a table.

I'm wondering, does EF support using these queries that use CHANGETABLE() function? Otherwise do you know any third-party library? Or any trick to implement it using EF?

Answer 1

You would probably start by creating UDFs in the database to encapsulate the CHANGETABLE access. Something like:

create or alter function GetChanges_Employee_Insert(@last_sync_version bigint)
returns table
as
return
SELECT e.*
FROM CHANGETABLE (CHANGES HumanResources.Employee, @last_sync_version) AS c  
    LEFT OUTER JOIN HumanResources.Employee AS e  
        ON e.[BusinessEntityID] = c.[BusinessEntityID] 
where c.SYS_CHANGE_OPERATION = 'I'

That creates an Employee-shaped result that you can load into your existing Employee entity.

Answer 2

You can always pass raw TSQL in EF. But I assume that you'd like an entity to reference the change table in the same way you would a table or a view.

Though I have no personal experience, in theory this should still work.

You are essentially mapping an entity to a table valued function. I believe that as of EF 6 you can add a TVF in the same manner you'd add a call to a stored proc, which creates a complex type but one you can work with.

The problem, I'd see is that CHANGETABLE() is a SQLServer system syntax, not a 1-1 mapping with a user defined or system defined table value function, so you might have to build your own scaffolding around it with your own user defined TVF or stored procedure and then call that from EF.

using (var ctx = new TestEntities())
{
    /* Execute TVF that calls changetable */

    /* wrapper for a call to CHANGETABLE() on the server side */
    var changes = ctx.GetChangeTable().ToList<Change>();
}