How to use DbContext.Database.SqlQuery<TElement>(sql, params) with stored procedure? EF Code First CTP5

Question

I have a stored procedure that has three parameters and I've been trying to use the following to return the results:

context.Database.SqlQuery<myEntityType>("mySpName", param1, param2, param3);

At first I tried using SqlParameter objects as the params but this didn't work and threw a SqlException with the following message:

Procedure or function 'mySpName' expects parameter '@param1', which was not supplied.

So my question is how you can use this method with a stored procedure that expects parameters?

Thanks.

Answer 1

You should supply the SqlParameter instances in the following way:

context.Database.SqlQuery<myEntityType>(

    "mySpName @param1, @param2, @param3",

    new SqlParameter("param1", param1),

    new SqlParameter("param2", param2),

    new SqlParameter("param3", param3));