Entity Framework calling stored procedure expects parameter which was not supplied

c# entity-framework sql sql-server stored-procedures

Accepted Answer

If so, it should likely also complain about the @user parameter, but in any case, try to supply the parameter without the @ prefix:

NextSuperGroup supergroup = entities.Database.SqlQuery<NextSuperGroup>(
    "super_group @user, @orderbyUnique",
    new SqlParameter("user", userName),
    new SqlParameter("orderbyUnique", true)
).First();
14
2/25/2014 12:45:36 PM

Popular Answer

The problem is that the SQL that EF generates for NULL values must not work with our real SQL Server. However, I don't believe the implementation has changed since 4.3 even though I'm using EntityFramework 6.

Using similar code to yours above, I ran the following output when tracing was enabled:

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

The SQL server error we observe is caused by the "default" value being given rather than "NULL." You can just use this if you need a quick workaround and don't need named parameters:

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

which results in something similar and functions well:

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

You must utilize the sql parameter and explicitly set the value or SqlValue attribute to DBNull for named parameters. Value (Crazy I know). Possibly like this:

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

Hope that was useful.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow