调用存储过程的实体框架需要未提供的参数

I am calling my SP via Entity Framework like this :

我通过如下实体框架调用SP:

NextSuperGroup supergroup = entities.Database.SqlQuery<NextSuperGroup>(
      "super_group @user, @orderbyUnique",
      new SqlParameter("@user", userName),
      new SqlParameter("@orderbyUnique", true)).First();

And getting the error

和错误

Procedure or function 'super_group' expects parameter '@orderbyUnique', which was not supplied.

过程或函数'super_group'期望参数'@orderbyUnique'，这是不提供的。

As you can see above I am supplying it.

正如你看到的，我正在提供它。

Here's the stored procedure:

存储过程:

ALTER PROCEDURE [dbo].[super_group]
     @user nvarchar(30)
    ,@stepLockDelay varchar(10) = '00:00:00'
    ,@orderbyUnique bit
AS

Any ideas why I am getting this error ?

我为什么会犯这个错误?

5 个解决方案

#1

It probably should complain about the @user parameter as well if this is the case, but anyway - try to supply the parameter without the @ prefix:

如果是这样的话，它可能也会抱怨@user参数，但是无论如何——尽量不使用@前缀来提供参数:

NextSuperGroup supergroup = entities.Database.SqlQuery<NextSuperGroup>(
    "super_group @user, @orderbyUnique",
    new SqlParameter("user", userName),
    new SqlParameter("orderbyUnique", true)
).First();

#2

The issue is the SQL that EF is producing for NULL values must not be compatible with our actual Sql Server. I'm using EntityFramework 6, but I don't think the implementation has changed since 4.3.

问题是EF为NULL值生成的SQL必须与实际的SQL服务器不兼容。我正在使用EntityFramework 6，但是我认为自从4.3以后，实现没有改变。

When I turned on tracing I get the following output after executing similar code to yours above:

当我打开跟踪时，在执行了与上面类似的代码后，我得到如下输出:

exec sp_executesql N'super_group',N'@userName nvarchar(4000)',@userName=default

The issue lies in the "default" value passed instead of "NULL" and the error we see comes from SQL server. If you want a quick fix and don't need named parameters you can just use this:

问题在于传递的“默认”值而不是“NULL”，我们看到的错误来自SQL server。如果你想要快速修复而不需要命名参数，你可以使用以下方法:

NextSuperGroup supergroup = entities.Database.SqlQuery<NextSuperGroup>(
   "super_group",
    userName).First();

Which produces something like this and works nicely:

它产生了这样的东西，并且工作得很好:

exec sp_executesql N'super_group',N'@p0 nvarchar(4000)',@p0=NULL

For named parameters, you need to use the sql parameter and set the value or SqlValue property explicitly to DBNull.Value (Crazy I know). Something like this:

对于命名参数，您需要使用sql参数并将值或SqlValue属性显式地设置为DBNull。我知道的值(疯狂)。是这样的:

var parameter = new SqlParameter("userName", SqlDbType.VarChar);
parameter.SqlValue = username.SqlNullIfEmpty(); // Implemented with an extension method

Hope that helps.

希望有帮助。

#3

I my case I fixed the issue by explicitly adding the type to parameter I was passing in which was defined in the stored procedure as a bit

在我的例子中，我通过显式地向传递的参数添加类型来解决这个问题，该参数在存储过程中被定义为位

        var paramExcludeExported = new SqlParameter
        {
            ParameterName = "ExcludeExported",
            SqlDbType = SqlDbType.Bit,
            Value = 0
        };

#4

Parameter not passed issue when multiple parameter passed

当多个参数通过时，参数未被传递

I was missing 'SPACE' after the first parameter's 'COMMA'

在第一个参数的'逗号'后面我缺少'空格'

var parameter = new List<object>();
var param = new SqlParameter("@CategoryID", CategoryID);
parameter.Add(param);
param = new SqlParameter("@SubCategoryID", SubCategoryID);
parameter.Add(param);

List<tbl_Product> QueryResult = db.Database.SqlQuery<tbl_Product>("EXEC SP_GetProducts @CategoryID, @SubCategoryID ", parameter.ToArray()).ToList();

#5

In our case, it was a matter of ensuring that the first letter of parameter was not a number, but a letter.

在我们的例子中，我们要确保参数的第一个字母不是一个数字，而是一个字母。

Changed 6cb to p6cb and the problem went away.

把6cb换成p6cb，问题就解决了。

#1