未返回ExecuteStoreQuery输出参数

Question

我有一些实体框架代码来运行一个返回参数的存储过程。每次我运行代码时，参数返回为null。有谁知道这是什么原因造成的吗？

谢谢

代码：

SqlParameter Business = new SqlParameter("Business", Search.Term);
                SqlParameter Location = new SqlParameter("Location", Search.Location);
                SqlParameter PageNumber = new SqlParameter("PageNumber", Search.CurrentPage);
                SqlParameter RecordsPerPage = new SqlParameter("RecordsPerPage", Search.RecordsPerPage);                  


                var TotalRecords = new SqlParameter
                {
                    ParameterName = "TotalRecords",
                    Value = 0,
                    Direction = ParameterDirection.Output                        
                };



                var List = db.ExecuteStoreQuery<ENT_SearchBusinessResult>("exec usp_BusinessUser_Search @Business,@Location,@PageNumber,@RecordsPerPage,@TotalRecords out", Business, Location, PageNumber, RecordsPerPage, TotalRecords); 

我使用了Sql profiler，发现它正在执行以下操作：

declare @p7 int
set @p7=53
exec sp_executesql N'exec usp_BusinessUser_Search    @Business,
@Location,@PageNumber,@RecordsPerPage,
@TotalRecords out',
N'@Business  nvarchar(14),@Location nvarchar(14),
@PageNumber int,
@RecordsPerPage int,@TotalRecords int output',
@Business=N'Food 
and Drink',@Location=N'United   Kingdom',@PageNumber=1,@RecordsPerPage=10,
@TotalRecords=@p7 output
select @p7

这很好，如果您运行此查询，但没有返回到我的代码，它会显示返回值:(

Answer 1

This article说，在尝试访问out参数的值之前，您需要读取所有结果。您的代码还不够完整，无法判断您是否正在执行此操作(如果不是，请尝试使用ToList()。)

Answer 2

这不一定是真的。下面是我使用输出参数的工作示例：

                            private IEnumerable<AppealsGridViewModel> GetDataNew(string sortString, int pageNumber, int pageSize)
                    {
                        var totalRowCount = new SqlParameter
                        {
                            ParameterName = "@TotalRowCount",
                            SqlDbType = SqlDbType.Int,
                            Direction = ParameterDirection.Output
                        };

                        var totalPropertyCount = new SqlParameter
                        {
                            ParameterName = "@TotalPropertyCount",
                            SqlDbType = SqlDbType.Int,
                            Direction = ParameterDirection.Output
                        };

                        var paramList = new object[] {
                            new SqlParameter {ParameterName = "@CompanyId", SqlDbType = SqlDbType.Int, Value = 5}
                            , new SqlParameter {ParameterName = "@AccountId", SqlDbType = SqlDbType.Int, Value = 0}
                            , new SqlParameter {ParameterName = "@IsServiceCenterUser", SqlDbType = SqlDbType.Bit, Value = 0}
                            , new SqlParameter {ParameterName = "@PageNumber", SqlDbType = SqlDbType.Int, Value = (pageNumber + 1)}
                            , new SqlParameter {ParameterName = "@PageSize", SqlDbType = SqlDbType.Int, Value = pageSize}
                            , new SqlParameter {ParameterName = "@SortOrder", SqlDbType = SqlDbType.VarChar, Value = sortString}
                            , totalRowCount
                            , totalPropertyCount
                        };

                        var s = _dataContext.ExecuteStoreQuery<AppealsGridViewModel>("exec atAppealsSearch @CompanyId, @AccountId, @IsServiceCenterUser, @PageNumber, @PageSize, @SortOrder, @TotalRowCount out, @TotalPropertyCount out", paramList).ToList();

                        var appealCount = totalRowCount.Value;
                        var propertyCount = totalPropertyCount.Value;

                        return s;
                    }

            ALTER PROCEDURE [dbo].[atAppealsSearch]
                @CompanyId INT
                , @AccountId INT = 0
                , @IsServiceCenterUser BIT = 0
                , @PageNumber INT = 1
                , @PageSize INT = 25
                , @SortOrder VARCHAR(MAX)
                , @TotalRowCount int OUT
                , @TotalPropertyCount int OUT
                AS


            SELECT @TotalRowCount = 33124
            SELECT @TotalPropertyCount = 555

            EXEC SP_EXECUTESQL @Query