Base solution for your next web application
Open Closed

Use EF Repository and Store Procedure in same transaction #6358


User avatar
0
chekairi created

Hi,

How to get the entity change values into store procedure with nolock query.

I just want to read uncommitted data in store procedure but that change i m doing with ef repository.

Thanks


4 Answer(s)
  • User Avatar
    0
    ismcagdas created
    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

  • User Avatar
    0
    chekairi created

    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&lt;WTMDbContext&gt; 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 } });
        }
    }
    
  • User Avatar
    0
    aaron created
    Support Team

    Try:

    GetDbContext().Database.ExecuteSqlCommand(
            "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");
    
  • User Avatar
    0
    chekairi created

    Thanks but no success with this command.

    Issue resolved. I just called below line after insert.

    await UnitOfWorkManager.Current.SaveChangesAsync();