C# 如何将参数传递给实体框架中的 DbSet.SqlQuery 方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10553436/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to pass parameters to DbSet.SqlQuery Method in Entity Framework
提问by Flea
I am trying to execute a RAW SQL statement in Entity Framework which takes some parameters. The method I am using is from DbSet.SqlQuery
我试图在 Entity Framework 中执行一个带有一些参数的 RAW SQL 语句。我使用的方法来自DbSet.SqlQuery
I am confused on how to construct the params object array: params object[] parameters
我对如何构造 params 对象数组感到困惑：params object[] parameters
This is my code block here:
这是我的代码块：
public ActionResult APILocation(string lat, string lng)
{
string SQL = "select * from (select Distance = ((ACOS(SIN(@lat * PI() / 180) * SIN(lat * PI() / 180) + COS(@lat * PI() / 180) * COS(lat * PI() / 180) * COS((@lng - Long) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) from dbo.Stores) t where Distance < 10 order by Distance asc";
ObjectParameter latParam = new ObjectParameter("lat", lat);
ObjectParameter lngParam = new ObjectParameter("lng", lng);
object[] parameters = new object[] { latParam, lngParam };
var stores = db.Stores.SqlQuery(SQL, parameters);
return Json(stores, JsonRequestBehavior.AllowGet);
}
I tried creating ObjectParameter and putting it into an object array but that did not work. Can someone provide an example on how I should construct the params object[] parameters
我尝试创建 ObjectParameter 并将其放入对象数组中，但这不起作用。有人可以提供一个关于我应该如何构造 params object[] 参数的例子吗
Thanks! Flea
谢谢！跳蚤
采纳答案by Flea
I ended up cleaning up my method's parameters so they wouldn't be the same as my database columns which was not very clear. The ObjectParameter does not support the @ symbol, so that didn't work. I ended up with the following solution:
我最终清理了我的方法参数，这样它们就不会与我不太清楚的数据库列相同。ObjectParameter 不支持 @ 符号，因此不起作用。我最终得到了以下解决方案：
public ActionResult APILocation(string latitude, string longitude)
{
string SQL = "select * from (select *, Distance = ((ACOS(SIN({0} * PI() / 180) * SIN(lat * PI() / 180) + COS({0} * PI() / 180) * COS(lat * PI() / 180) * COS(({1} - long) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) from dbo.Parish) t where Distance < 10 order by Distance asc";
SqlParameter latParam = new SqlParameter("lat", latitude);
SqlParameter lngParam = new SqlParameter("long", longitude);
object[] parameters = new object[] { latParam , lngParam };
var stores = db.Store.SqlQuery(SQL, parameters);
return Json(stores, JsonRequestBehavior.AllowGet);
}
I also had to select * in my sub-select because it was trying to map to my entity which it couldn't since I was just returning one column and not everything. This solution proved to work for me!
我还必须在我的子选择中选择 * ，因为它试图映射到我的实体，因为我只是返回一列而不是所有内容，因此无法映射到我的实体。这个解决方案被证明对我有用！
Ultimately, the whole object[] parameters can be done like this:
最终，整个 object[] 参数可以这样完成：
SqlParameter latParam = new SqlParameter("latitude", latitude);
SqlParameter lngParam = new SqlParameter("longitude", longitude);
object[] parameters = new object[] { latitude, longitude };
Thanks hwcverwe for your help.
感谢 hwcverwe 的帮助。
Flea
跳蚤
回答by user1210708
The above answers are correct but since the signature for SqlQuery is SqlQuery(sql:String, params object[] parameters), you can simplify the code and give it a more natural feel by using:
上面的答案是正确的，但由于 SqlQuery 的签名是 SqlQuery(sql:String, params object[] parameters)，您可以简化代码并使用以下方法使其感觉更自然：
context.SqlQuery(sql, latParam, lngPara);
This would be perfectly legal without having to array it first.
这将是完全合法的，而不必先对其进行排列。
