Use EF Repository and Store Procedure in same transaction #6358

4 Answer(s)

• 

  0

  ismcagdas created 5 years ago

  Support Team

  Hi,

  You can check the document below to run SP. I'm not sure about getting uncommitted data.

  https://aspnetboilerplate.com/Pages/Documents/Articles/Using-Stored-Procedures,-User-Defined-Functions-and-Views/index.html

  Save Cancel
• 

  0

  chekairi created 5 years ago

  Dear, Please review my sample code. I think to understand better what i want.

  when i called sql query to get user count after ef insert within same transaction scope, but result is still zero. How to read uncommitted data before commit.

  I will used SP instead of raw query.

  public class UserRepository : WTMRepositoryBase<User, long>, IUserRepository {

      private readonly IActiveTransactionProvider _transactionProvider;
  
      public UserRepository(
          IDbContextProvider<WTMDbContext> dbContextProvider,
          IActiveTransactionProvider transactionProvider) : 
          base(dbContextProvider) {
          _transactionProvider = transactionProvider;
      }
  
      public void CreateUser(CreateUserInput input) {
          var user = new User {
              Name = input.Name,
              Surname = input.Surname,
              EmailAddress = input.EmailAddress,
              Password = input.Password
          };
  
          Insert(user);
          var result = GetUserCount();
  
          // result is zero..  
      }
  
      private int GetUserCount() {
          var command = Connection.CreateCommand();
          command.CommandText = "SELECT COUNT(*) FROM Users WITH (NOLOCK)";
          //command.Transaction = transaction;
          command.Transaction = GetActiveTransaction();
          command.CommandType = CommandType.Text;
          EnsureConnectionOpen();
          var output = Convert.ToInt32(command.ExecuteScalar());
          return output;
      }
  
      private void EnsureConnectionOpen() {
          if (Connection.State != ConnectionState.Open) {
              Connection.Open();
          }
      }
  
      private DbTransaction GetActiveTransaction() {
          return (DbTransaction)_transactionProvider.GetActiveTransaction(new ActiveTransactionProviderArgs { { "ContextType", typeof(WTMDbContext) }, { "MultiTenancySide", MultiTenancySide } });
      }
  }
  

  Save Cancel
• 

  0

  aaron created 5 years ago

  Support Team

  Try:

  GetDbContext().Database.ExecuteSqlCommand(
          "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");
  

  Save Cancel
• 

  0

  chekairi created 5 years ago

  Thanks but no success with this command.

  Issue resolved. I just called below line after insert.

  await UnitOfWorkManager.Current.SaveChangesAsync();

  Save Cancel