Why transaction fails in Entity Framework

Question

I have Kendo application using Entity Framework to access stored procedures. There is transaction used in calling stored procedures. But it gives,

The transaction operation cannot be performed because there are pending requests working on this transaction.

The code is as follow,

    FlightCenterEntities _repository = new FlightCenterEntities(connectionString);
    ...
    using (TransactionScope scope = new TransactionScope())
    {
        this._repository.uspDeleteSalesSupplier(salesId);

        // Write to Sales table
        this._repository.uspUpdateSales(
                            salesId,
                            userId,
                            shop,
                            createdDateTime,
                            bookingDate,
                            passengerName,
                            destination,
                            deposit,
                            saleLocation
                            ).SingleOrDefault();

        for (int i = 0; i < list.Count; i++)
        {
            // Write to SalesSupplier table
            var returnValue = this._repository.uspAddSalesSupplier(
                                                    salesId,
                                                    list[i].SourceTypeName,
                                                    list[i].SupplierName,
                                                    list[i].SaleValue,
                                                    list[i].NumberPassengers
                                                    ).SingleOrDefault();

            status = returnValue == null ? 0 : (int)returnValue;
            if (status == 0)
                break;
        }

        scope.Complete();

The error happens at scope.Complete(). The stored procedures are as follow,

CREATE PROCEDURE [dbo].[uspDeleteSalesSupplier]
    @SalesID    int
    AS

BEGIN
  SET NOCOUNT ON

    BEGIN TRY

        UPDATE [dbo].[SalesSupplier]
        SET IsDeleted = 1
        WHERE SalesID = @SalesID

        SELECT @@Rowcount

    END TRY
    BEGIN CATCH
        --Standard Error Handling

        SET     @Error_Num = Error_Number()
        SET     @Error_Message = Error_Message()

        RAISERROR(@Error_Message , 16, 1, @Procedure_Name) WITH NOWAIT, SETERROR    
    END CATCH;   
END

CREATE PROCEDURE [dbo].[uspUpdateSales]
    @SalesID            int,
    @UserID             varchar(50),
    @Shop               varchar(50),
    @CreatedDateTime    Datetime,
    @BookingDate        Datetime,
    @PassengerName      varchar(50),
    @Destination        varchar(50),
    @Deposit            decimal,
    @SaleLocation       varchar(50)
    AS

BEGIN
  SET NOCOUNT ON

    BEGIN TRY

        Update [dbo].[Sales]
            Set     UserID = @UserID,
                    Shop = @Shop,
                    CreatedDateTime = @CreatedDateTime,
                    BookingDate =@BookingDate,
                    PassengerName = @PassengerName,
                    Destination = @Destination,
                    Deposit = @Deposit,
                    SaleLocation = @SaleLocation
            Where   SalesID = @SalesID      

        SELECT @@Rowcount

    END TRY
    BEGIN CATCH
        --Standard Error Handling

        SET     @Error_Num = Error_Number()
        SET     @Error_Message = Error_Message()

        RAISERROR(@Error_Message , 16, 1, @Procedure_Name) WITH NOWAIT, SETERROR
    END CATCH;
END

CREATE PROCEDURE [dbo].[uspAddSalesSupplier]
    @SalesID            int,
    @SourceType         varchar(50),
    @Supplier           varchar(50),
    @SaleValue          decimal,
    @Passengers         int
    AS

BEGIN
  SET NOCOUNT ON

    BEGIN TRY

        INSERT INTO [dbo].[SalesSupplier]
                   (SalesID,
                    SourceType,
                    Supplier,
                    SaleValue,
                    Passengers,
                    IsDeleted
                    )
             VALUES
                   (@SalesID,
                    @SourceType,
                    @Supplier,
                    @SaleValue,
                    @Passengers,
                    0
                   )

        SELECT cast(@@rowcount as int)

    END TRY
    BEGIN CATCH
        --Standard Error Handling

        SET     @Error_Num = Error_Number()
        SET     @Error_Message = Error_Message()

        RAISERROR(@Error_Message , 16, 1, @Procedure_Name) WITH NOWAIT, SETERROR
    END CATCH;
END

Answer 1

Actually, the reason of the problem for me was another thing, I will post it here because it may be helpful for others.

the problem was that I am not closing Context after using it, In another word, I was not call dispose on this context object. so the solution was using the context object in using statement like the following

using(var context = new DbContext()) {
        // do your work here
}

Answer 2

Edit

It looks like this issue here.

The workaround is to evaluate the return result of all imported stored procs via .FirstOrDefault() or SingleOrDefault() as you've done with the other two procedures, i.e.:

var foo = this._repository.uspDeleteSalesSupplier(salesId).FirstOrDefault();

Original Answer (not helpful)

It might be that you are doing other repository updates in the section marked ...?

If there are other pending updates on the repository, you will need to flush these via SaveChanges() before you start the Transaction around your SPROCs on the same DbContext

Alternatively, create a NEW DbContext inside the TransactionScope just for use by your SPROC calls, as follows:

using (var scope = new TransactionScope(
    TransactionScopeOption.RequiresNew, 
    new TransactionOptions
    {
       IsolationLevel.ReadCommitted
    }))
{
  var sprocRepository = new FlightCenterEntities(connectionString);
  sprocRepository.uspDeleteSalesSupplier(salesId);
  ... replace the this._repository with sprocRepository for the other usp calls.

  scope.Complete();
}

Assuming that you don't require a nested TransactionScope, I've added an explicit TransactionScopeOption.RequiresNew and also reduced the isolation to ReadCommitted.