为什么在更改SqlParameter顺序时，Entity Framework会引发异常？ [英] Why does Entity Framework throw an exception when changing SqlParameter order?

问题描述

我使用实体框架4.3代码首先调用存储过程，我调用存储过程的方式是这样的：

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();

它在SqlServer上执行，我得到结果。

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)
};

我收到如下错误：

 Error converting data type nvarchar to int

存储过程为像这样：

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?

========= ===我找到一个解决方法，它的工作完美============

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();

        }
    }

推荐答案

这不是因为参数对象中的参数顺序 - 这是因为在您的第二个代码片段中，您明确地将 @Code 值作为第一个参数传递给SP正在期待一个会员INT 值。

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();

...您传递0165210662660001作为第一个参数，转换为INT失败。

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

您的参数对象中的参数顺序与EF（ADO.NET实际）将无关将这些参数映射到查询字符串中的 @parametername 值。因此，新的SqlParameter（Code，0165210662660001）将映射到查询中的 @Code 位置 - 第二个代码剪切的int实际上是SP所预期的会员值的位置。

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.

但是，您也可以使用命名参数执行SP，在这种情况下，您可以按以下顺序将参数传递给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();

你看到我没有按照他们定义的顺序将参数传递给SP SP]，但是因为它们被命名，我不需要关心。

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.

对于传递参数的不同方法，请参阅：Answer 为一些很好的例子。

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

这篇关于为什么在更改SqlParameter顺序时，Entity Framework会引发异常？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！