4

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?

Ahmad
  • 5,551
  • 8
  • 41
  • 57
  • EF's change tracking and that of Sql Server's are two completely different concepts. They're in no way related, nor can they be made to cooperate in any way. Of course, as the answers suggest, you can run raw SQL to get SQL Server's change tracking information, but that "implement" any "trick". What are you trying to achieve? What't the bigger picture here? – Gert Arnold Oct 08 '18 at 18:55
  • @GertArnold You've misunderstood the concept, my friend. I'm talking about "SQL Server Change Tracking". Please Google it to find out more. – Ahmad Oct 08 '18 at 20:46
  • Well, were I talking about something else? Anyway, it would be more constructive if you'd explain better what you're trying to achieve. Or accept one of the answers if they actually answered your unclear question. – Gert Arnold Oct 08 '18 at 20:58
  • @GertArnold "EF's change tracking and that of Sql Server's are two completely different concepts". What does it mean? The question seems so clear to me, I want to execute `CHANGETABLE()` queries and I'm going to use EF in a proper way for it. – Ahmad Oct 08 '18 at 21:43
  • Look, it's you that mentions these two completely different concepts in one question so the obvious conclusion is that you're trying to connect them somehow. If not, the first paragraph is just noise and can be removed. Maybe you should respond to the given answers. It's not clear if they were helpful to you. – Gert Arnold Oct 09 '18 at 08:53

2 Answers2

2

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.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
1

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>();
}
RThomas
  • 10,702
  • 2
  • 48
  • 61