Change Tracking Entity framework

Question

i make table for property name that changed and value before and value after
How i can use Change Tracking to store changed in this table?

Answer 1

You can track the operation, the changed columns and the new values by using Change Tracking. However getting the old Value out of Change Tracking is not possible. SQL Server 2016 offers the new feature "Change data capture", which gives you the needed Information about the old value before the update/delete happened ( see https://msdn.microsoft.com/en-us/library/bb933994.aspx ).

If you don't have access to a SQL Server 2016, here is how you can configure Change Tracking:

• Activate at Database

  ALTER DATABASE <YourDatabase> f.e. DeviceDatabase
  SET CHANGE_TRACKING = ON  
  (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON) 
  

• Activate Change Tracking for your needed tables

  ALTER TABLE <YourTable> f.e. Devices  
  ENABLE CHANGE_TRACKING  
  WITH (TRACK_COLUMNS_UPDATED = ON)  
  

• Setup a DB Job, which will copy change-information into your custom table every minute,hour,day (what you need)

  DECLARE @minversion bigint;
  SET @minversion = (SELECT MinVersion = CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('Devices')) )
  
  SELECT SYS_CHANGE_COLUMNS, e.Id FROM
  CHANGETABLE(CHANGES Devices, @minversion) AS C
  LEFT OUTER JOIN Devices AS e  
  ON e.Id = c.Id;  
  

• To Get the latest Value of the Changed Column you can try this (but beware of multiple updates of the same row. you only get the latest value).

  CHANGE_TRACKING_IS_COLUMN_IN_MASK
  (COLUMNPROPERTY(OBJECT_ID('Devices'), 'Id', 'ColumnId')
  ,c.sys_change_columns)
  

This will return 1 if Column changed, 0 if not. You can add this for every column of your table and join on value = 1 and then add the value to your query.

Finally, I would just recommend to use Stored Procedures to Update/Insert/Delete on your Tables. In those you can easily insert all information you want to store about the change in your custom table. If you have SQL Server 2016 tho, try what I mentioned above, eventually.

Answer 2

Actually if you override the SaveChanges() method in your data context class you can access ChangeTracker. This gives you all the entities currently tracked by the context and their EntityState (if they are added/modified/deleted/unchanged etc).

Here you can get the DbEntityEntry class and from that get the entitys current values and/or its previous values if the entity is in the modified state.

public override int SaveChanges()
{
    var allTrackedEntities = this.ChangeTracker.Entries().ToList();

    return base.SaveChanges();
}

I currently use this method to do some basic auditing of who is doing that to what entity.