Develop UNDO feature with Entity Framework 6 and trigger

EF 6.0 overview

Entity Framework 6.0 maintains connection and transaction internally when SaveChanges() method is called. It maintains a transaction for multiple entity insert, update and delete in a single SaveChanges() method. Each time calling this method EF creates a new connection and transaction. This become an issue in our UNDO feature, because context_info only available in the same transaction scope.

In earlier version of EF, System.Transaction.TransactionScope was used to control the transaction. So passing information to triggers is as easy as adding one additional call to a stored procedure. However, in EF6 this approach was out.

New API with EF 6.0

EF 6.0 introduced two new APIs for transaction.

1. DbContext.Database.BeginTransaction: It allows us to start a transaction manually. It allows us to combine several operations to be combined within the same transaction and hence all the transactions are either committed or rolled back. This method allows us to specify the isolation level for the transaction. It returns a DbContextTransaction object. The BeginTransaction method has two overloads, one has no argument and the other accepts an explicit Isolation Level.
2. DbContext.Database.UseTransaction: It allows DbContext to use a transaction that was started outside of the Entity Framework. It means using this API we can use any existing transaction with Entity Framework.

Implementation

using(var tran = _context.Database.BeginTransaction())
{
    try
    {
        await CallProcAsync("dbo.USP_HIS_ContextInfo @empId={0}, @recordId={1}", UserId, 0);
        await _context.SaveChangesAsync();
        tran.Commit();
    }
    catch
    {
        tran.Rollback();
    }
}

ALTER trigger [dbo].[trg_update_TBL_E2DIALER_MSI_CellCodes] on [dbo].[TBL_E2DIALER_MSI_CellCodes]
after update as
begin
	SET NOCOUNT ON
	declare @empId varchar(50), @recordId int, @changeID int, @info binary(128)
	select @info = context_info(),@empId=replace(convert(varchar(255),@info),0x0,'')
	if coalesce(@empId,'')='' set @empId = SYSTEM_USER
end

More detail for trigger portion please read Develop undo feature with trigger.

Author: IT Team