How to make entity framework stored procedure asynchronous?

Question

I have a function (ReturnStatementDetailsForSubRepAsync) which uses a linq expression which I can easily make asynchronous using an built function such as .ToListAsync(). Now because this LINQ function is asynchronous I must make the parent function asynchronous.

The parent function looks like this:

public async Task<IEnumerable<StatementDetail>> ReturnStatementDetailsAsync(string cid, string userName, int statementNo)
{
    var statementDetails = new List<StatementDetail>;
    if (HttpContext.Current.User.IsInRole(UserLevel.Subrep.GetDescription()) || HttpContext.Current.User.IsInRole(UserLevel.SubRepMaster.GetDescription()))
    {
        var subRepStmtDetails = await ReturnStatementDetailsForSubRepAsync(cid, userName, statementNo); //Linq query with ToListAsync()
        foreach (var item in subRepStmtDetails)
        {
            statementDetails.Add(new SubRepStatementDetailItem(item));
        }
    }
    else
    {
        var regionalStmtDetails = await Task.Run(() => StoredPrcedureAsyncTest(cid, statementNo); //Entity framework stored procedure call
        foreach (var item in regionalStmtDetails)
        {
            statementDetails.Add(new RegionalStatementDetailItem(item));
        }
    }

    return statementDetails;
}

StoredPrcedureAsyncTest looks like this:

public async Task<IEnumerable<SelectStatementTransno_Result>> StoredPrcedureAsyncTest(string cid, int statementNo)
{
    using (var dbContext = new WebDataEntities())
    {
        return await Task.Run(() => dbContext.SelectStatementTransno(cid, statementNo, null).ToList());
    }
}

Now I know that StoredPrcedureAsyncTest performs IO work so I should make it asynchronous. So will the way which I have implemented the stored procedure call cause my method to be fully asynchronous as there currently isn't an in-built entity framework solution to making the stored procedure call asynchronous?

Answer 1

Your DbSets in your DbContext represent the tables in your database. The DbContext knows abut the relation between the tables and how to convert your LINQ queries into a query that your database understands. It is the task of the DbContext to hide the internals of your database. Whenever you want to communicate with your database you use your DbContext.

Therefore your DbContext is a good place to put your stored procedures in. As your DbContext also Creates your model (in DbContext.OnModelCreating), it is also a good place to add the functionality to create the stored procedure.

Users of your DbContext might expect the following functionality:

• Call stored procedure with parameters
• Call stored procedure with parameters async (your question)
• Does stored procedure exist?
• Create or alter stored procedure when model created

Your DbContext will execute the stored procedure using DbContext.Database.ExecuteSqlCommand. This function has an async equivalent: DbContext.Database.ExecuteSqlAsync

class MyDbContext : DbContext
{
    // TODO: add DbSets

    #region stored procedure
    public void CallMyStoredProcedure(MyParams myParams)
    {
        object[] functionParameters = this.CreateFunctionParams(myParams);
        this.Database.ExecuteSqlCommand(sqlCommandMyStoredProcedure, functionParameters); 
    }

     public async Task CallMyStoredProcedure(MyParams myParams)
    {
        object[] functionParameters = this.CreateFunctionParams(myParams);
        await this.Database.ExecuteSqlCommandAsync(
            sqlCommandMyStoredProcedure,
            functionParameters)
        .ConfigureAwait(false);; 
    }

    // TODO: add more functions
    #endregion stored procedures
} 

These functions use several other functions:

// name of the stored procedure, names of the parameters:
private const string myStoredProcedureName = "InsertPoint";
private const string paramProductName = "ProductName";
private const string paramCount = "Count";

// SQL command to execute stored procedure with the parameters
private const string SqlCmndMyStoredProcedure = @"Exec "
    + myStoredProcedureName
    + @" @ParamProductName, @ParamCount";

private object[] CreateFunctionParams(MyParams myParams)
{
     return newObject[]
     {
         new SqlParameter(paramProductName, myParams.ProductName),
         new SqlParameter(paramCount, myParams.Count),
     };
}

To make the collection complete: add a method that checks if the stored procedure exists and one that creates the stored procedure:

Check if stored procedure already exists

public bool MyStoredProcedureExists()
{
     return this.StoredProcedureExists(myStoredProcedureName);
}

public bool StoredProcedureExists(string procedureName)
{
    object[] functionParameters = new object[]
    {
        new SqlParameter(@"procedurename", procedureName),
    };

    string query = @"select [name] from sys.procedures where name= @procedurename";

    return this.Database.SqlQuery<string>(query, functionParameters)
        .ToList()
        .Where(item => item == procedureName)
        .Any();        
}

Create Stored Procedure:

public void CreateMyStoredProcedure(bool forceCreate)
{
    // do not create if already exists, except if forceCreate:
    bool storedProcedureExists = this.MyStoredProcedureExists;

    if (!storedProcedureExists || forceCreate)
    {   // create the stored procedure:
        var x = new StringBuilder();

        // decide whether to create or Alter
        if (!storedProcedureExists)
        {
            x.Append(@"CREATE");
        }
        else
        {
            x.Append(@"ALTER");
        }

        // procedure  name:
        x.Append(@" PROCEDURE ");
        X.AppendLine(myStoredProcedureName);

        // parameters:
        x.AppendLine(@"@ProductName NVARCHAR(80),"
        X.AppendLine(@"@Count int")

        // procedure code:
        x.AppendLine(@"AS")
        X.AppendLine(@"BEGIN")
        ... // TODO: add procedure code
        x.AppendLine(@"END");

        this.Database.ExecuteSqlComment(x.ToString());
    }
}

finally OnModelCreating:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    this.CreateMyStoredProcedure(false); // don't force if already exists;

    // TODO: add fluent API
}