Call stored procedure inside TransactionScope in Entity Framework

Question

I using Entity Framework 4 and meet following issue whith executing stored procedure in ambient transaction. Here is the code:

public void UpdateOrderRequest(IOrder order, int requestId, int userId, Fee fee)
{
    using (var tscope = new TransactionScope(TransactionScopeOption.RequiresNew))
    {
        _storedProcedureDA.UpdateOrderRequest(requestId, userId, data.ClientId, data.RequestStatus, data.Date,
                              data.Type, data.Side, data.Quantity, data.ExecInst, data.Price,
                              data.StopPrice, data.TimeInForce, data.Description, data.Target);
        var feeDa = new FeeDA();
        var dbFee = new Domain.Entities.Fee
                        {
                            OrderRequestId = requestId,
                            Identifier = fee.Id,
                            Value = fee.Value,
                        };
        feeDa.Save(dbFee);
        tscope.Complete();
    }
}

_StoredProceduresDA and FeeDA are data access classes that uses one instance of DataContext for each.
_storedProcedureDA.UpdateOrderRequest() method is just wrapper under Context.ExecuteFunction<..>("AddOrderRequest",...)
feeDA.Save() adds entity into Repository and calls Context.SaveChanges()
When i trying to make this call, i catching following exception: The transaction operation cannot be performed because there are pending requests working on this transaction.

The point is that i need to do both of these operations in one transaction and i can't use workaround suggested in Can't I call a stored procedure from Entity Framework inside a transaction scope? (ado.net using its own connection) Does anyone knows how to wrap DataContext.ExecuteFunction<>() in transaction?

P.S. I've tried to wrap ExecuteFunction in its own transaction with its own TransactionScope with all possible parameters(Supress and so on) but hothing helped.

Could you further clarify if the data classes "each use a separate DataContext" or "share a single DataContext". Your "one instance of DataContext for each" isn't specific enough... — Vincent Vancalbergh, May 16 '13 at 11:51

score 3 · Answer 1 · edited May 23 '17 at 11:53

Have a look at the similar issue in this other stackoverflow question , particularly at answer #4:

"I Finally found a solution...it seems EF expects the stored proc (imported function) to return a value. so call .FirstOrDefault() on the function when it returns."

If that's applicable to your own problem, then inside of your _storedProcedureDA.UpdateOrderRequest method, where you call Context.ExecuteFunction, grab the return value (probably int) and return it back to the calling method (so change UpdateOrderRequest from void to int or whatever the return value type is).

I see this question is pretty old, so maybe you've long ago resolved it and moved on?

Call stored procedure inside TransactionScope in Entity Framework

1 Answers1