Call stored procedure inside transaction using Entity Framework

Question

I am trying to run the following transaction using Entity Framework. Inside transaction scope I call stored procedure from the DB.

 using (mother_Entities entitiesContext = context.Value)
 {
    using (var transactionScope = new TransactionScope())
    {   
        // a lot of create, insert, update operations goes here
        ...
        entitiesContext.SaveChanges();

        //Execute stored procedure:
        var paramMessage = new ObjectParameter("MESSAGE", "");
        var paramMotherid = new ObjectParameter("MOTHERID", motherProductId);
        var paramTochteridlist = new ObjectParameter("TOCHTER_ID_LIST", string.Join(";", motherIds));
        var paramError = new ObjectParameter("ERROR", typeof(int));
        var paramErrorText = new ObjectParameter("ERR_TEXT", typeof(string));

        entitiesContext.ExecuteFunction("SP_DOCUWARE_UPDATE", paramMessage, paramMotherid,
                                            paramTochteridlist, paramError, paramErrorText);

        ...
        transactionScope.Complete();
     }
 }

On the line entitiesContext.ExecuteFunction() I get exception Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0

My stored procedure doesn't use any transactions and doesn't call any others functions or procedures. So I don't understand why I can't execute strored procedure inside transaction.

UPDATE:

Oh, I found this in the stored procedure:

...
    IF @COMMIT = 1
    BEGIN
        IF @CANCEL = 1 
            ROLLBACK
        ELSE
            COMMIT
    END
    ELSE IF @CHECK = 1
        ROLLBACK
    END
...

May be after commit exception is thrown. But how to escape this error?

I think it's not to do with TransactionScope but with Stored proc. Please check if stored procedure is working with parameters you are passing/setting before call. — Pranav Singh, Oct 08 '13 at 13:11
What do you mean saying "is working with parameters"? I updated my question. — algreat, Oct 08 '13 at 13:19
I mean profiler trace having same values for which it is giving exception is able to run in SQL server Management studio or not... — Pranav Singh, Oct 08 '13 at 19:40

score 3 · Accepted Answer · answered Oct 09 '13 at 07:33

I solved my problem.

In the stored procedure there is a ROLLBACK and COMMIT keywords. But there is no BEGIN TRANSACTION anywhere in the procedure. From the beginning, I thought it is strange.

As you know COMMIT decrements @@TRANCOUNT by 1. Or to be more precise:

If @@TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications performed since the start of the transaction a permanent part of the database, frees the resources held by the transaction, and decrements @@TRANCOUNT to 0. If @@TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @@TRANCOUNT only by 1 and the transaction stays active.

In my case I begin a transaction in the code. And COMMIT in the procedure is trying to commit my transaction and decrement @@TRANCOUNT but it isn't completed yet.

So I added BEGIN TRANSACTION to the stored procedure and it works fine.

score 0 · Answer 2 · answered Oct 08 '13 at 14:26

0

You can't commit the outer transaction (SaveChanges) before you commit the inner transaction (Complete). I suspect the call to SaveChanges internally commits the inner transaction as well. (Not verified.)

answered Oct 08 '13 at 14:26

tne

7,071
2
45
68

1

When using transaction scope then SaveChanges doesn't commit any changes. You have to cammit it using transactionScope.Complete(); – algreat Oct 08 '13 at 16:41
It's expected behavior. What do you do outside of the inner transaction scope? Do you really need it? I believe using `ExecuteFunction` inside an implicit `DbContext` transaction scope would execute it inside the same transaction, which looks like what you want. You should also look at your backend limitations anyway, SQL Server for instance doesn't really support nested transactions (it just [flattens out all transactions to the outer transaction](http://technet.microsoft.com/en-us/library/ms189336.aspx)), which explains the behavior. So yes, does removing the explicit transaction scope help? – tne Oct 09 '13 at 07:16

Call stored procedure inside transaction using Entity Framework

2 Answers2

Linked