CODEWITHSUNDEEP
entity-frameworkentity-framework-6
Daniel Devo
Daniel Devo

Reputation: 41

Entity Framework buggy when invoking stored procedure via Database.SqlQuery

When invoking a stored procedure with a parameter of type int and value 0, Entity Framework sends a null value to the server. 

var requiredIntParameter = new SqlParameter("RequiredInt", 0);
var tableParameter = new SqlParameter("@Table", System.Data.SqlDbType.Structured);
... table creation ....
List<ReturnType> result = DBContext.Database.SqlQuery<ReturnType>("EXEC NAMEOFSPROC "
           + "@RequiredInt, "
           + "@Table, "
           requiredIntParameter ,
           tableParameter,
           );

Entity Framework log:

Opened connection at 1/10/2018 4:42:09 PM +01:00
EXEC NAMEOFSPROC @RequiredInt, @Table
-- RequiredInt: 'null' (Type = Int64, IsNullable = false)
-- @Table: '' (Type = Object, IsNullable = false)
-- Executing at 1/10/2018 4:42:09 PM +01:00
-- Completed in 40 ms with result: SqlDataReader

Resulting exception:

The parameterized query '(@RequiredInt int' expects the parameter @RequiredInt'), which was not supplied.

Note: As Sproc has a table parameter,

((IObjectContextAdapter)this).ObjectContext.ExecuteFunction

is not an option for me!

Using:

Upvotes: 0

Views: 152

Answers (1)

Daniel Devo
Daniel Devo

Reputation: 41

There is a Workaround:

var requiredIntParameter = new SqlParameter("RequiredInt", typeof(int)); 
requiredIntParameter.Value = 0;

Now log is 

EXECSPROC @RequiredInt, @Table
-- RequiredInt: '0' (Type = Int32, IsNullable = false)
-- @Table: '' (Type = Object, IsNullable = false)
-- Executing at 1/10/2018 4:42:09 PM +01:00

-- Completed in 40 ms with result: SqlDataReader

And everything is fine!

Upvotes: 1

Related Questions