如何使实体框架存储过程异步? [英] How to make entity framework stored procedure asynchronous?

问题描述

我有一个使用linq表达式的函数(ReturnStatementDetailsForSubRepAsync)，我可以使用诸如.ToListAsync()之类的内置函数轻松地使它们异步.现在，因为此LINQ函数是异步的，所以我必须使父函数异步.

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.

父函数如下:

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看起来像这样:

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());
    }
}

现在我知道StoredPrcedureAsyncTest执行IO工作，因此我应该使其异步.那么，实现存储过程调用的方式会导致我的方法完全异步，因为当前没有内置的实体框架解决方案使存储过程调用异步?

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?

推荐答案

您在DbContext中的DbSet代表数据库中的表. DbContext知道表之间的关系以及如何将LINQ查询转换为数据库可以理解的查询. DbContext的任务是隐藏数据库的内部.每当您想与数据库进行通信时，都可以使用DbContext.

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.

因此，DbContext是放置存储过程的好地方.由于DbContext还创建了模型(在DbContext.OnModelCreating中)，因此它也是添加功能来创建存储过程的好地方.

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.

您的DbContext用户可能期望以下功能:

Users of your DbContext might expect the following functionality:

• 使用参数调用存储过程
• 使用参数async调用存储过程(您的问题)
• 是否存在存储过程?
• 在创建模型时创建或更改存储过程

• 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

您的DbContext将使用DbContext.Database.ExecuteSqlCommand执行存储过程.该函数具有异步等效项:

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.ExecuteSqlComment(sqlCommandMyStoredProcedure, functionParameters); 
    }

     public async Task CallMyStoredProcedure(MyParams myParams)
    {
        object[] functionParameters = this.CreateFunctionParams(myParams);
        await this.Database.ExecuteSqlCommentAsync(
            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:

检查存储过程是否已经存在

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();        
}

创建存储过程:

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());
    }
}

最终OnModelCreating:

finally OnModelCreating:

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

    // TODO: add fluent API
}

这篇关于如何使实体框架存储过程异步?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！