c# How to call a stored procedure using code first

Overview: This code snippet demonstrates how to use the BeginTransaction to update a table preventing another user from changing values in the critical path while the method is running. The transaction is committed and the change occurrs in the table. However, we don't want to commit the context changes from the context because commits should be done at the unit of work level. Therefore, NextNumber data manipulation should be moved to a stored procedure with transaction table locking. SQLQuery and sqlparameter allows you to call a store procedure within a unit of work critical path.


public async Task<NextNumber> GetNextNumber(string nextNumberName)
      public async Task<NextNumber> GetNextNumber(string nextNumberName)
            Entities _dbEntities = (Entities)_dbContext;
            NextNumber nextNumber = null;
            long? currentNextNumberValue = 0;
            using (DbContextTransaction transaction = _dbEntities.Database.BeginTransaction())
                //Lock the table during this transaction
                nextNumber = await (from e in _dbEntities.NextNumbers
                                               where e.NextNumberName == nextNumberName
                                               select e).FirstOrDefaultAsync<NextNumber>();

                currentNextNumberValue = nextNumber.NextNumberValue;
                nextNumber.NextNumberValue += 1;
                _dbEntities.Entry(nextNumber).State = EntityState.Modified;
                nextNumber.NextNumberValue = currentNextNumberValue??0;

            return nextNumber;

Stored Procedure usp_GetNextNumber

alter PROCEDURE [dbo].[usp_GetNextNumber]( @NextNumberName as varchar(20))
begin transaction
select * into #temp from NextNumber where NextNumberName = @NextNumberName
update NextNumber set nextNumberValue+=1 where NextNumberName=@NextNumberName;
commit transaction
select * from #temp

New GetNextNumber

call a sql server stored procedures using code first

public async Task<NextNumber> GetNextNumber(string nextNumberName)

    SqlParameter param1 = new SqlParameter("@NextNumberName", nextNumberName);
    NextNumber nextNumber = await _dbContext.Database.SqlQuery<NextNumber>("usp_GetNextNumber @NextNumberName", param1).SingleAsync();