Why does Entity Framework throw an exception when changing SqlParameter order?

Question

Im using entity framework 4.3 code first for calling stored procedure the way i call the stored procedure is like this:

var parameters = new[]
{
    new SqlParameter("member", 1),
    **new SqlParameter("Code","0165210662660001"),**
    new SqlParameter("PageSize", 1),
    new SqlParameter("PageNumber",1)
};

var result  = context.Database.SqlQuery<resultClass>(
    "mySpName @member, @Code, @PageSize,@PageNumber" parameters).ToList();

It gets executed on the SqlServer and I get the result.

But if I change the order of the paramaters like this:

var result  = context.Database.SqlQuery<resultClass>("mySpName @Code,  @member,@PageSize,@PageNumber" parameters).ToList();

var parameters = new[]
{
    **new SqlParameter("Code","0165210662660001"),**
    new SqlParameter("Member", 1),
    new SqlParameter("PageSize", 1),
    new SqlParameter("PageNumber",1)
};

I got an error like this :

 Error converting data type nvarchar to int

The stored procedure is like this :

ALTER PROCEDURE [c].[mySpName]
    @Member INT ,
    @Code VARCHAR (50) ,
    @PageSize INT ,
    @PageNumber INT
 AS 

Why do i get this order? Is it important to keep parameters order? What can i do so that I can call a stored procedure without being concerned about parameters order?

============ i find a workaround and it works perfectly ============

public class blahContext<T>
{
    int i = 0;
    public  IEnumerable<T> ExecuteStoreQuery(string SPname, SqlParameter[] parameters)
    {

        using (var context = new CADAContext())
        {


            string para = string.Join(", ", (from p in parameters
                                             where !"NULL".Equals(p.Value)
                                             select string.Concat(new object[] { "@", p.ParameterName, "={", this.i++, "}" })).ToList<string>());

            object[] x = (from p in parameters
                          where !"NULL".Equals(p.Value)
                          select p.Value).ToArray<object>();

            return context.Database.SqlQuery<T>(SPname + " " + para, x).ToList();

        }
    }

Answer 1

It's not because of the parameter order in your parameters object - it's because in your second code snippet you're explicitly passing the @Code value as the first parameter when the SP is expecting a Member INT value.

var result  = context.Database.SqlQuery<resultClass>("mySpName @Code,  @member,@PageSize,@PageNumber" parameters).ToList();

...you're passing in "0165210662660001" as the first parameter and the conversion to INT is failing.

The order of your parameters in your parameters object is irrelevant as EF (ADO.NET actually) will map those parameters to the @parametername values in your query string. So the new SqlParameter("Code","0165210662660001") will be mapped into the @Code position in your query - which int the second code snipped is actually the position for the Member value as expected by the SP.

However... you can execute a SP using named parameters as well and in that case you can pass the parameters to the SP in any order as below:

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

You see that I'm not passing the params to the SP in the order they were defined [by the SP] but because they're named I don't have to care.

For different ways of passing params see: This Answer for some good examples.