Sql-server – Rolling Back Stored Procedures in Entity Framework

entity-frameworksql-server-2012stored-procedures

I currently working a on project that operates within a transaction in entity framework. Before now all of the changes were made using a DataContext. I need to add a call to a stored procedure using that same context. My question is will the stored procedure be rolled back as well in the case of an exception.

Below is how the SP is structured

CREATE PROCEDURE dbo.UpdateObject
@ObjectId VARCHAR(20),@ObjectStatus VARCHAR(20)='Yes'
AS 
SET NOCOUNT ON;

DECLARE @object_status_enddate  DATE;
DECLARE @object_optout VARCHAR(10);
DECLARE @current_date  DATE=REPLACE(CONVERT(VARCHAR,GETDATE(),111),'/','-');

BEGIN TRY
    SELECT TOP(1)
     @object_status_enddate=a.EndDate,@object_optout=a.OptOut
    FROM xxx a
    WHERE ObjectId=@ObjectId
    ORDER BY LastModified DESC;
    --TERM EXISTING STATUS
    WITH CurrentStatus AS
    (
    SELECT TOP(1) *,
    ROW_NUMBER() OVER (ORDER BY LastModified DESC) AS RN
    FROM XXX
    WHERE ObjectId= @ObjectId
    )
    UPDATE CurrentStatus SET EndDate = @current_date, LastModified = CURRENT_TIMESTAMP


    --ADD NEW STATUS
    INSERT INTO [dbo].[XXX](ObjectId,ObjectIndicator,StartDate,EndDate,LastModified,ModifiedBy,OptOut)
    SELECT @ObjectId,@ObjectStatus,@current_date,@object_status_enddate,CURRENT_TIMESTAMP,USER,@object_optout
END TRY
BEGIN CATCH
    --SOMETHING WENT WRONG
    DECLARE @ErrorMessage NVARCHAR(MAX),@ErrorSeverity INT,@ErrorState INT;
    SELECT @ErrorMessage=ERROR_MESSAGE()+' Line '+CAST(ERROR_LINE() AS NVARCHAR(5)),@ErrorSeverity=ERROR_SEVERITY(),@ErrorState=ERROR_STATE();
    RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState);
END CATCH;
GO

Best Answer

The rollback will rollback all opening (active) transaction. In case using Entity Framework, EF always auto create an root (outermost) transaction, so that the rollback inside a stored proc will rollback the transaction created by EF as well and it will cause error.

There is a way to get over, using Save point -> see https://msdn.microsoft.com/en-us/library/ms188378.aspx

I've applied the template save point for EF and it works well.