如何使entiy框架的存储过程成为异步的?[英] How to make entiy framework stored procedure asynchronous?

本文是小编为大家收集整理的关于如何使entiy框架的存储过程成为异步的?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

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

父函数如下所示:

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 工作,所以我应该让它异步.那么我实现存储过程调用的方式是否会导致我的方法完全异步,因为目前没有内置实体框架解决方案来使存储过程调用异步?

推荐答案

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

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

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

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

您的 DbContext 将使用 DbContext.Database.ExecuteSqlCommand 执行存储过程.此函数具有异步等效项: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
} 

这些函数使用其他几个函数:

// 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),
     };
}

要使集合完整:添加一个检查存储过程是否存在的方法和一个创建存储过程的方法:

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

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:

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

    // TODO: add fluent API
}

本文地址:https://www.itbaoku.cn/post/1556800.html

问题描述

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?

推荐答案

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.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:

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
}