DbContext.Database.SqlQuery <T> (query, parameters) with a value of 0 sent as "default" to the database
I have a code like this:
const string query = "select count(*) from query with parameters"
+ "@param1, @param2..."
(Obvioulsy is pseudocode). When I run the request like this:
ctx.Database.SqlQuery<int>(query,
new SqlParameter("param1", idAsociacion),
new SqlParameter("param2", 0));
I am getting an exception stating that I have not provided a value for param2.
If I use SQl Server Profiles, I see that the generated request 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 also get an error that is required by param2 and is not provided (I don't know the exact error in English because I have a localized SQL Server).
Why does EF convert the value 0
to default
?
source to share
The problem was silly.
When I place my mouse over this constructor call in VS:
new SqlParameter("param2", 0)
I see that the overload is SqlParameter
not called like this:
SqlParameter(string, object)
but this one
SqlParameter(string, SqlDbType)
So, a simple solution is to call the constructor passing the value to the object, for example:
new SqlParameter("param2", (object)0)
so the correct overload is called and it works as expected.
source to share