DbContext.Database.SqlQuery<T>(query, parameters) parameter with value 0 sent as "default" to the database

Question

I have a code like this:

const string query = "select count(*) from query with parameters"
     + "@param1, @param2..."

(Obvioulsy is pseudocode). When I run the query like this:

ctx.Database.SqlQuery<int>(query,
   new SqlParameter("param1", idAsociacion),
   new SqlParameter("param2", 0));

I get an exception stating that I didn't provide a value for param2.

If I use SQl Server Profiles I see that the generated query looks like this:

exec sp_executesql N'The query', @param1=N'P', @param2=default

If I try to run it directly in SQL Server I do also get the error that param2 is required and not provided (I don't know the exact error in English, because I have a localized SQL Server).

Why is EF converting the value 0 into default?

Answer 1

The problem was a silly one.

When I put the mouse pointer over this constructor invocation in VS:

new SqlParameter("param2", 0)

I see that the SqlParameter overload being invoked is not this one:

SqlParameter(string, object) 

but this one

SqlParameter(string, SqlDbType)

So, the simple solution is to invoke the constructor casting the value to object, like this:

new SqlParameter("param2", (object)0)

so the correct overload is invoked, and it works as expected.