如何解决错误必须声明标量变量@var"用EF调用存储过程

Question

我想使用实体框架执行存储过程并返回模型类型的结果列表:

我在DB中的存储过程是:

CREATE procedure [dbo].myProc
(
    @myParam1 int,
    @myParam2 nvarchar(max)   
)
as
BEGIN
    SELECT myCol1,myCol2 FROM
    MyTable Where x = @myParam1 And y = @myParam2
END

My Model返回的结果类型是:

public class MyModel
{
    public int? myCol1 { get; set; }
    public string myCol2 { get; set; }
}

执行过程的方法是:

public List<MyModel> MyProc(int? p1, string p2)
{
    var myParam1Parameter = new SqlParameter("@myParam1", SqlDbType.Int).Value = p1.HasValue ? (object)p1.Value : DBNull.Value;
    var myparam2Parameter = new SqlParameter("@myparam2", SqlDbType.NVarChar, -1).Value = (object)p2 ?? DBNull.Value;
    return
            _context.Database.SqlQuery<MyModel>(
                "Exec [dbo].[myProc] @myParam1, @myParam2 ",
                myParam1Parameter, myparam2Parameter).ToList<MyModel>();
}

当运行程序并执行我的方法时抛出如下所示的异常:

类型为'System.Data.SqlClient.SqlException'的未处理异常发生在EntityFramework.SqlServer.dll

Additional information: Must declare the scalar variable "@myParam1".

Answer 1

我发现了我的问题，下面应该用来定义参数:

var myParam1Parameter = new SqlParameter("@myParam1", SqlDbType.Int) 
{
    Value = p1.HasValue ? (object)p1.Value : DBNull.Value
};
var myparam2Parameter = new SqlParameter("@myparam2", SqlDbType.NVarChar,1)
{
    Value = (object)p2 ?? DBNull.Value
} 
return _context.Database.SqlQuery<MyModel>(
            "[dbo].[myProc] @myParam1, @myParam2 ",
            myParam1Parameter, myparam2Parameter).ToList<MyModel>();

Answer 2

试一试:

_context.Database.SqlQuery<MyModel>
("[dbo].[myProc] @myParam1, @myParam2 "
,fromDateParameter, toDateParameter).ToList();

(删除"执行"one_answers"ToList"从你的查询)