EF6 Call stored procs using SqlQuery/ExecuteSqlCommand without regard for parameter order?

Question

I am using EF 6 SqlQuery/ExecuteSqlCommand to call stored procedures like so:

var parameters = new List<SqlParameter>()
{
    new SqlParameter("@ID", model.ID),
    new SqlParameter("@Email", model.Email),
    new SqlParameter("@Phone", model.Phone)
};

using (var context = new MyContext())
{
    context.Database.ExecuteSqlCommand("sp_MySproc_U @ID, @Email, @Phone", parameters.ToArray());
}

This works great, until I realized that my Email values were being saved to my Phone field and vice versa. Why? Because the order of the parameters in my stored procedure is @ID, @Phone, @Email, which obviously doesn't match the order of the parameters in my code. Is there a simple way in the SqlQuery/ExecuteSqlCommand syntax to specifically name the parameters and match them with values. My goal is to call my stored procs without being concerned with the order of the parameters in code matching the order in the stored procedure.

Someone in this post suggested the following,

db.Database.SqlQuery<resultClass>("mySpName PageNumber=@PageNumber,Code=@Code,PageSize=@PageSize", parameters.ToArray()).ToList();

but this returns and "Invalid character '='" error. Thanks for your help.

Answer 1

According to the SQL Server stored procedure call syntax, the named parameters must be preceeded by at sign @, i.e

@parameter=value

or

@parameter=@variable

So you could use

context.Database.ExecuteSqlCommand(
    "sp_MySproc_U @ID=@ID, @Email=@Email, @Phone\@Phone",
     parameters.ToArray());

or more generically

context.Database.ExecuteSqlCommand(
    "sp_MySproc_U " + string.Join(", ", parameters.Select(p => p.Name + "=" + p.Name)), 
    parameters.ToArray());