如何使用DbContext.Database.SqlQuery<TElement>(sql，params)来调用存储过程？EF Code First CTP5

Question

我有一个存储过程，它有三个参数，我一直试图使用以下代码返回结果：

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

起初我尝试使用SqlParameter作为参数但这不起作用，抛出了一个包含以下信息的SqlException：

存储过程或函数'mySpName'需要参数'@param1'，但未提供。

因此我的问题是如何在需要参数的存储过程中使用此方法？

谢谢。

- electricsheep

---

你使用的是哪个版本的SQL Server？我在使用兼容模式（90）下的2008代码时遇到了问题，但当我在2005上运行它时，它会出现语法错误。 - Gats

@Gats - 我之前也遇到了SQL 2005的同样问题。在存储过程名称前添加"EXEC"。我在这里发布了这个信息以供日后参考：https://dev59.com/y1jUa4cB1Zd3GeqPRGc4 - Dan Mork

Answer 1

您应按以下方式提供SqlParameter实例：

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

- Devart

---

你如何让这个方法适用于可空类型？我尝试使用可空的十进制数，但是当十进制数为空时，会出现缺少参数的错误。然而，@DanMork提到的下面的方法却可以正常工作。 - Paul Johnson

传递 DbNull.Value 而不是 null 值可以解决问题吗？ - Alireza

你也可以使用“@p#”语法，避免使用SqlParameter，如在上下文中使用context.Database.SqlQuery<myEntityType>（“mySpName @p0, @p1, @p2”，param1，param2，param3）。来源：http://msdn.microsoft.com/en-US/data/jj592907。 - Marco

如果您正在使用 DateTime 参数，则不仅需要指定名称和值，还需要指定参数类型。例如：dbContext.Database.SqlQuery<Invoice>("spGetInvoices @dateFrom, @dateTo", new SqlParameter { ParameterName = "dateFrom", SqlDbType = SqlDbType.DateTime, Value = startDate }, new SqlParameter { ParameterName = "dateTo", SqlDbType = SqlDbType.DateTime, Value = endDate }); 另一个重要的事项是尊重参数的顺序。 - Francisco Goldenstein

@singhswat，您能否澄清一下您的问题？如果有测试代码将不胜感激。 - Devart

Answer 2

此外，您可以将"sql"参数作为格式说明符:

context.Database.SqlQuery<MyEntityType>("mySpName @param1 = {0}", param1)

- Dan Mork

---

这个语法让我有点担心。它是否容易受到 SQL 注入攻击？我猜 EF 在运行 "EXEC mySpName @Param1 ="，如果发送 "x' GO [恶意脚本]"，那么可能会引起一些问题？ - Tom Halladay

@TomHalladay，没有SQL注入的风险 - 这种方法仍然会根据参数的类型引用和转义参数，与@样式参数相同。因此，对于字符串参数，您将在语句中使用不带引号的"SELECT * FROM Users WHERE email={0}"。 - Ross McNab

@TomHalladay 它实际上存在SQL注入问题（如果您的参数中有一些单引号也会出现问题）。 @OnurTOPAL 您可以使用myparam ?? "NULL"来处理空值吗？ - gilles emmanuel

如果您需要为具有可选参数的存储过程指定参数，则此答案非常有用。不起作用的示例：ProcName @optionalParam1 = @opVal1，@optionalParam2 = @opVal2。有效的示例：ProcName @optionalParam1 = {0}，@optionalParam2 = {1}。 - Garrison Neely

@GarrisonNeely，感谢您的反馈。我很高兴这对您有所帮助。 - Dan Mork

Answer 3

这个解决方案仅适用于SQL Server 2005

你们是救星，但正如@Dan Mork所说，你需要在混合中添加EXEC。让我困惑的是：

• 存储过程名称前面要加上“EXEC”
• 参数之间要用逗号隔开
• 在参数定义中去掉“@”（不确定是否需要这一部分）。

:

context.Database.SqlQuery<EntityType>(
    "EXEC ProcName @param1, @param2", 
    new SqlParameter("param1", param1), 
    new SqlParameter("param2", param2)
);

- Tom Halladay

---

两个得票较高的回答都没有包括 "exec"，但我可以确认如果我省略它，就会出现异常。 - Jordan Gray

谢谢，我一直在遇到一个错误，加了EXEC之后错误消失了。奇怪的是，如果我使用context.Database.SqlQuery<EntityType>("ProcName '" + param1 + "','" + param2 + "'"); 这个语句，它可以工作，但是如果我添加参数，直到我加上EXEC关键字才能正常工作。 - Solmead

请注意：我不需要使用 exec 关键字。另外，去掉参数前面的 @ 符号会更好，因为它总是让我感到困惑。 - Nathan Koop

+1，我错过了EXEC并且一直收到SqlExceptions消息：'procName'附近的语法不正确。 - A. Murray

@Ziggler 你用的是2005还是更新的版本？EXEC关键字主要对我们这些使用2005版本的人造成了问题。 - Tom Halladay

Answer 4

return context.Database.SqlQuery<myEntityType>("mySpName {0}, {1}, {2}",
new object[] { param1, param2, param3 });

//或

using(var context = new MyDataContext())
{
return context.Database.SqlQuery<myEntityType>("mySpName {0}, {1}, {2}",
new object[] { param1, param2, param3 }).ToList();
}

//或者

using(var context = new MyDataContext())
{
object[] parameters =  { param1, param2, param3 };

return context.Database.SqlQuery<myEntityType>("mySpName {0}, {1}, {2}",
parameters).ToList();
}

//或者

using(var context = new MyDataContext())
{  
return context.Database.SqlQuery<myEntityType>("mySpName {0}, {1}, {2}",
param1, param2, param3).ToList();
}

- Thulasiram

---

对我来说，它在Assembly EntityFramework.dll，v4.4.0.0上运行良好。 - Thulasiram

如果你使用了 using(var context = new MyDataContext())，那么 .ToList() 是必须的。 - Thulasiram

我花了相当长的时间才发现，.ToList() 是获取正确结果集所必需的。 - Halim

Answer 5

大多数答案很脆弱，因为它们依赖于存储过程参数的顺序。更好的方法是命名存储过程参数并为它们提供参数化值。

要在调用存储过程时使用命名参数，而不必担心参数的顺序

使用ExecuteStoreQuery和ExecuteStoreCommand的SQL Server命名参数

描述了最佳方法。比Dan Mork在这里的回答更好。

• 不依赖于字符串连接，并且不依赖于在存储过程中定义的参数的顺序。

例如：

var cmdText = "[DoStuff] @Name = @name_param, @Age = @age_param";
var sqlParams = new[]{
   new SqlParameter("name_param", "Josh"),
   new SqlParameter("age_param", 45)
};

context.Database.SqlQuery<myEntityType>(cmdText, sqlParams)

- Don Cheadle

---

似乎"params"是保留关键字，所以我认为你不能那样使用它。否则这对我很有帮助，谢谢！ - ooXei1sh

@ooXei1sh - 已修复，使用 sqlParams 变量。 - Don Cheadle

你可以在前面加上@来使用保留字，但是你确实不应该这样做。 - StingyJack

谢谢，其他答案需要参数按相同的顺序排列。 - mayank.karki

Answer 6

db.Database.SqlQuery<myEntityType>("exec GetNewSeqOfFoodServing @p0,@p1,@p2 ", foods_WEIGHT.NDB_No, HLP.CuntryID, HLP.ClientID).Single()

或者

db.Database.SqlQuery<myEntityType>(
    "exec GetNewSeqOfFoodServing @param1, @param2", 
    new SqlParameter("param1", param1), 
    new SqlParameter("param2", param2)
);

或者

var cmdText = "exec [DoStuff] @Name = @name_param, @Age = @age_param";
var @params = new[]{
   new SqlParameter("name_param", "Josh"),
   new SqlParameter("age_param", 45)
};

db.Database.SqlQuery<myEntityType>(cmdText, @params)

或者

db.Database.SqlQuery<myEntityType>("mySpName {0}, {1}, {2}",
new object[] { param1, param2, param3 }).ToList();

- Hossein Hajizadeh

Answer 7

我使用这种方法：

var results = this.Database.SqlQuery<yourEntity>("EXEC [ent].[GetNextExportJob] {0}", ProcessorID);

我喜欢它，因为我只需要输入Guids和Datetimes，SqlQuery就可以为我执行所有的格式化操作。

- Malcolm O'Hare

Answer 8

我使用EF 6.x做了这样的事情：

using(var db = new ProFormDbContext())
            {
                var Action = 1; 
                var xNTID = "A239333";

                var userPlan = db.Database.SqlQuery<UserPlan>(
                "AD.usp_UserPlanInfo @Action, @NTID", //, @HPID",
                new SqlParameter("Action", Action),
                new SqlParameter("NTID", xNTID)).ToList();


            }

不要重复使用sqlparameter，有些人会因此而烧毁他们的变量。

var Action = new SqlParameter("@Action", 1);  // Don't do this, as it is set below already.

- Tom Stickel

Answer 9

@Tom Halladay的答案是正确的，需要注意检查空值并在参数为空时发送DbNullable，否则会出现异常，例如： 参数化查询'...'期望提供参数'@parameterName'，但未提供。 类似这样的方法对我很有帮助。

public static object GetDBNullOrValue<T>(this T val)
{
    bool isDbNull = true;
    Type t = typeof(T);

    if (Nullable.GetUnderlyingType(t) != null)
        isDbNull = EqualityComparer<T>.Default.Equals(default(T), val);
    else if (t.IsValueType)
        isDbNull = false;
    else
        isDbNull = val == null;

    return isDbNull ? DBNull.Value : (object) val;
}

（此方法的功劳归于https://stackoverflow.com/users/284240/tim-schmelter）

然后像这样使用：

new SqlParameter("@parameterName", parameter.GetValueOrDbNull())

或者另一个更简单但不是通用的解决方案是：

new SqlParameter("@parameterName", parameter??(object)DBNull.Value)

- emanuel.virca

Answer 10

当我使用调用存储过程的方式，传递两个输入参数并使用SELECT语句返回3个值时，遇到了相同的错误信息。在EF Code First方法中，我通过以下方式解决了这个问题。

 SqlParameter @TableName = new SqlParameter()
        {
            ParameterName = "@TableName",
            DbType = DbType.String,
            Value = "Trans"
        };

SqlParameter @FieldName = new SqlParameter()
        {
            ParameterName = "@FieldName",
            DbType = DbType.String,
            Value = "HLTransNbr"
        };


object[] parameters = new object[] { @TableName, @FieldName };

List<Sample> x = this.Database.SqlQuery<Sample>("EXEC usp_NextNumberBOGetMulti @TableName, @FieldName", parameters).ToList();


public class Sample
{
    public string TableName { get; set; }
    public string FieldName { get; set; }
    public int NextNum { get; set; }
}

更新：看起来在 SQL SERVER 2005 中缺少 EXEC 关键字会创建问题。所以为了让它适用于所有的 SQL SERVER 版本，我更新了我的答案并在下面一行添加了EXEC

 List<Sample> x = this.Database.SqlQuery<Sample>(" EXEC usp_NextNumberBOGetMulti @TableName, @FieldName", param).ToList();

- Ziggler

---

请查看下面的链接。无需使用exec https://msdn.microsoft.com/zh-cn/data/jj592907.aspx - Ziggler