如何使用EF Core從存儲過程中獲取返回值和輸出值?

asp.net-core asp.net-mvc c# entity-framework entity-framework-core

ALTER PROCEDURE [dbo].[SearchMovies]
    --@Year     int = null,
    @CategoryIds varchar(50) = null,
    @Keywords nvarchar(4000) = null,
    @PageIndex int = 1, 
    @PageSize int = 2147483644,
    @TotalRecords int = null OUTPUT
As ...

EF存儲庫:

 public class EFRepository<T> : IRepository<T> where T : BaseEntity
{
    private readonly ApplicationDbContext _ctx;
    private  DbSet<T> entities;
    string errorMessage = string.Empty;

    public EFRepository(ApplicationDbContext context)
    {
        this._ctx = context;
        entities = context.Set<T>();
    }     
   ...

    public IQueryable<T> ExecuteStoredProcedureList(string commandText, params object[] parameters)
    {          
        _ctx.Database.ExecuteSqlCommand(commandText, parameters);
        return entities.FromSql(commandText, parameters);
    }
}

我稱之為:

var pCategoryIds = new SqlParameter()
            {
                ParameterName = "@CategoryIds",
                Value = commaSeparatedCategoryIds,
                DbType = DbType.String
            };
var pKeywords = new SqlParameter()
            {
                ParameterName = "@Keywords",
                DbType = DbType.String,
                Value = name
            };
var pPageIndex = new SqlParameter()
            {
                ParameterName = "@PageIndex",
                DbType = DbType.Int32,
                Value = pageIndex
            };
var pPageSize = new SqlParameter()
            {
                ParameterName = "@PageSize",
                DbType = DbType.Int32,
                Value = pageSize
            };

var pTotalRecords = new SqlParameter();
pTotalRecords.ParameterName = "@TotalRecords";
pTotalRecords.Direction = ParameterDirection.Output;
pTotalRecords.DbType = DbType.Int32;

var query1 = _ctx.Database.ExecuteSqlCommand("dbo.[SearchMovies] " +
                "@CategoryIds, @Keywords, @PageIndex, @PageSize, @TotalRecords OUTPUT", 
                pCategoryIds, pKeywords, pPageIndex, pPageSize, pTotalRecords);

var query2 = _ctx.Set<MovieItem>.FromSql("dbo.[SearchMovies] " +
                    "@CategoryIds, @Keywords, @PageIndex, @PageSize, @TotalRecords OUTPUT",
                    pCategoryIds, pKeywords, pPageIndex, pPageSize, pTotalRecords);

query1確實獲得輸出pTotalRecords ,但沒有返回值,第二個query2獲取返回值但沒有輸出參數。

在EF 6中,我們曾經使用SqlQuery在一個命令中執行這兩個操作,如何在EF核心中執行相同的操作?

更新

暫時,我運行2個查詢,一個用於獲取輸出參數,另一個用於結果集。

 public IQueryable<T> ExecuteStoredProcedureList(string commandText, params object[] parameters)
    {          
        _ctx.Database.ExecuteSqlCommand(commandText, parameters);
        return entities.FromSql(commandText, parameters);
    }

熱門答案

我做了這樣的事情: -

- 我的存儲過程:

CREATE PROCEDURE p1
AS
     BEGIN            
        RETURN 29
     END
GO

C#代碼

SqlParameter[] @params = 
{
   new SqlParameter("@returnVal", SqlDbType.Int) {Direction = ParameterDirection.Output}
 };   


_stagingContext.Database.ExecuteSqlCommand("exec @returnVal=" + storedProcName, @params);

var result = @params[0].Value; //result is 29 

希望這可以幫助



Related

許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow