MVC Core 3 FromSqlRaw参数无法按预期工作 [英] MVC Core 3 FromSqlRaw parameters not working as expected

问题描述

是否还有其他人遇到FromSqlRaw参数无法正常工作的问题?有一个陷阱要注意.

Has anyone else run into the issue of FromSqlRaw parameters not working as expected? There is a Gotcha to be aware of.

使用最新的MVC Core 3 DbContext，我添加了一个存储过程，但是无法获得FromSqlRaw调用来正确评估参数.

With the latest MVC Core 3 DbContext I added a stored procedure but could not get the FromSqlRaw call to correctly evaluate parameters.

    SqlParameter[] parameters = {
        new SqlParameter("DateFrom", dateFrom),
        new SqlParameter("DateTo", dateTo),
        new SqlParameter("Sort", sort),
        new SqlParameter("Aggregation", aggregation)
            };


return await sp_Visits.FromSqlRaw("EXECUTE dbo.sp_Visits @DateFrom, @DateTo, @Aggregation, @Sort", parameters).ToListAsync();

alter PROCEDURE dbo.sp_Visits 
     @DateFrom      date
    ,@DateTo        date
    ,@Sort          nvarchar(50)
    ,@Aggregation   nvarchar(20)
    AS

推荐答案

我发现以下方法可以正常工作:

I found that the following worked OK:

return await sp_Visits.FromSqlRaw("EXECUTE dbo.sp_Visits @DateFrom=@DateFrom, @DateTo=@DateTo, @Aggregation=@Aggregation, @Sort=@Sort", parameters).ToListAsync();

然后，我注意到参数数组定义顺序与SQL EXECUTE参数顺序不同.似乎创建命名参数并不意味着名称将与FromSqlRaw匹配，而是似乎按顺序使用这些参数.多年来，我已经习惯了使用C#数据库代码来匹配名称，以至于在该代码无法使用之前我一直没有想过.

Then I noticed that the parameter array definition order is different from the SQL EXECUTE parameter order. It appears that creating named parameters does not imply the names will be matched by FromSqlRaw but instead appears to use the parameters as ordered. I've been so used to C# Database code matching up the names over the years that I didn't give it a thought until this code did not work.

匹配参数顺序后，原始代码有效

After matching the order of parameters, the original code works

SqlParameter[] parameters = {
            new SqlParameter("DateFrom", dateFrom),
            new SqlParameter("DateTo", dateTo),
            new SqlParameter("Aggregation", aggregation),
            new SqlParameter("Sort", sort)
                };

这篇关于MVC Core 3 FromSqlRaw参数无法按预期工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！