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.