Rolling Back Stored Procedures in Entity Framework

Question

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

• Colin 't Hart: Probably depends entirely on the DBMS. And definitely depends on whether said stored procedure has any commit statements in it, or invokes anything that does en explicit or implicit commit.
• Antarr Byrd: @Colin'tHart I'm using SQL Server 2012. There are no commits, only a try and catch block. I will post an example above.
• Colin 't Hart: Then it should be completely transactional, ie: a rollback will also rollback all of the actions done by the stored procedure. You've written it just like it should be done -- ie: without a commit in the stored procedure -- so that it can be used in this way. The commits should always be done in the calling environment. Another way of putting it: do the commits at an as high level as possible so that you benefit most from re-use of these atomic blocks.

Answer 1

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.