是否可以使用实体框架运行本机 sql？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/915329/
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
Is it possible to run native sql with entity framework?
提问by user80855
I am trying to search an XML field within a table, This is not supported with EF.
我正在尝试搜索表中的 XML 字段，EF 不支持此操作。
Without using pure Ado.net is possible to have native SQL support with EF?
不使用纯 Ado.net 是否可以通过 EF 获得本机 SQL 支持？
回答by Justin Grant
For .NET Framework version 4 and above: use ObjectContext.ExecuteStoreCommand()if your query returns no results, and use ObjectContext.ExecuteStoreQueryif your query returns results.
对于 .NET Framework 版本 4 及更高版本：ObjectContext.ExecuteStoreCommand()如果您的查询没有返回结果，则使用，ObjectContext.ExecuteStoreQuery如果您的查询返回结果，则使用。
For previous .NET Framework versions, here's a sample illustrating what to do. Replace ExecuteNonQuery() as needed if your query returns results.
对于以前的 .NET Framework 版本，这里有一个示例说明了要做什么。如果您的查询返回结果，请根据需要替换 ExecuteNonQuery()。
static void ExecuteSql(ObjectContext c, string sql)
{
var entityConnection = (System.Data.EntityClient.EntityConnection)c.Connection;
DbConnection conn = entityConnection.StoreConnection;
ConnectionState initialState = conn.State;
try
{
if (initialState != ConnectionState.Open)
conn.Open(); // open connection if not already open
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
finally
{
if (initialState != ConnectionState.Open)
conn.Close(); // only close connection if not initially open
}
}
回答by Leniel Maccaferri
Using Entity Framework 5.0you can use ExecuteSqlCommandto execute multi-line/multi-command pure SQLstatements. This way you won't need to provide any backing object to store the returned value since the method returns an int (the result returned by the database after executing the command).
使用Entity Framework 5.0您可以ExecuteSqlCommand用来执行多行/多命令纯SQL语句。这样你就不需要提供任何支持对象来存储返回的值，因为该方法返回一个 int （执行命令后数据库返回的结果）。
Sample:
样本：
context.Database.ExecuteSqlCommand(@
"-- Script Date: 10/1/2012 3:34 PM - Generated by ExportSqlCe version 3.5.2.18
SET IDENTITY_INSERT [Students] ON;
INSERT INTO [Students] ([StudentId],[FirstName],[LastName],[BirthDate],[Address],[Neighborhood],[City],[State],[Phone],[MobilePhone],[Email],[Enrollment],[Gender],[Status]) VALUES (12,N'First Name',N'SecondName',{ts '1988-03-02 00:00:00.000'},N'RUA 19 A, 60',N'MORADA DO VALE',N'BARRA DO PIRAí',N'Rio de Janeiro',N'3346-7125',NULL,NULL,{ts '2011-06-04 21:25:26.000'},2,1);
INSERT INTO [Students] ([StudentId],[FirstName],[LastName],[BirthDate],[Address],[Neighborhood],[City],[State],[Phone],[MobilePhone],[Email],[Enrollment],[Gender],[Status]) VALUES (13,N'FirstName',N'LastName',{ts '1976-04-12 00:00:00.000'},N'RUA 201, 2231',N'RECANTO FELIZ',N'BARRA DO PIRAí',N'Rio de Janeiro',N'3341-6892',NULL,NULL,{ts '2011-06-04 21:38:38.000'},2,1);
");
For more on this, take a look here: Entity Framework Code First: Executing SQL files on database creation
有关更多信息，请查看此处：实体框架代码优先：在创建数据库时执行 SQL 文件
回答by Md. Nazrul Islam
For Entity Framework 5use context.Database.SqlQuery.
对于实体框架 5，请使用context.Database.SqlQuery.
And for Entity Framework 4use context.ExecuteStoreQuerythe following code:
对于实体框架 4，请使用 context.ExecuteStoreQuery以下代码：
public string BuyerSequenceNumberMax(int buyerId)
{
string sequenceMaxQuery = "SELECT TOP(1) btitosal.BuyerSequenceNumber FROM BuyerTakenItemToSale btitosal " +
"WHERE btitosal.BuyerID = " + buyerId +
"ORDER BY CONVERT(INT,SUBSTRING(btitosal.BuyerSequenceNumber,7, LEN(btitosal.BuyerSequenceNumber))) DESC";
var sequenceQueryResult = context.Database.SqlQuery<string>(sequenceMaxQuery).FirstOrDefault();
string buyerSequenceNumber = string.Empty;
if (sequenceQueryResult != null)
{
buyerSequenceNumber = sequenceQueryResult.ToString();
}
return buyerSequenceNumber;
}
To return a List use the following code:
要返回列表，请使用以下代码：
public List<PanelSerialList> PanelSerialByLocationAndStock(string locationCode, byte storeLocation, string itemCategory, string itemCapacity, byte agreementType, string packageCode)
{
string panelSerialByLocationAndStockQuery = "SELECT isws.ItemSerialNo, im.ItemModel " +
"FROM Inv_ItemMaster im " +
"INNER JOIN " +
"Inv_ItemStockWithSerialNoByLocation isws " +
" ON im.ItemCode = isws.ItemCode " +
" WHERE isws.LocationCode = '" + locationCode + "' AND " +
" isws.StoreLocation = " + storeLocation + " AND " +
" isws.IsAvailableInStore = 1 AND " +
" im.ItemCapacity = '" + itemCapacity + "' AND " +
" isws.ItemSerialNo NOT IN ( " +
" Select sp.PanelSerialNo From Special_SpecialPackagePriceForResale sp " +
" Where sp.PackageCode = '" + packageCode + "' )";
return context.Database.SqlQuery<PanelSerialList>(panelSerialByLocationAndStockQuery).ToList();
}
回答by Andrii Nemchenko
Since .NET 4 you can use ExecuteStoreQuerymethod:
从 .NET 4 开始，您可以使用ExecuteStoreQuery方法：
var list = myDBEntities.ExecuteStoreQuery<MyClass>(MyClass.sql);
where myDBEntities is inherited from ObjectContext.
其中 myDBEntities 继承自 ObjectContext。
class MyClass
{
/* You can change query to more complicated, e.g. with joins */
public const string sql = @"select [MyTable].[MyField] from [MyTable]";
public string MyField { get; set; }
}
Notice that MyTable is real table name, not EF class.
请注意，MyTable 是真实的表名，而不是 EF 类。
回答by ELTEE
Keep it simple
把事情简单化
using (var context = new MyDBEntities())
{
var m = context.ExecuteStoreQuery<MyDataObject>("Select * from Person", string.Empty);
//Do anything you wonna do with
MessageBox.Show(m.Count().ToString());
}
回答by Nirosh
public class RaptorRepository<T>
where T : class
{
public RaptorRepository()
: this(new RaptorCoreEntities())
{
}
public RaptorRepository(ObjectContext repositoryContext)
{
_repositoryContext = repositoryContext ?? new RaptorCoreEntities();
_objectSet = repositoryContext.CreateObjectSet<T>();
}
private ObjectContext _repositoryContext;
private ObjectSet<T> _objectSet;
public ObjectSet<T> ObjectSet
{
get
{
return _objectSet;
}
}
public void DeleteAll()
{
_repositoryContext
.ExecuteStoreCommand("DELETE " + _objectSet.EntitySet.ElementType.Name);
}
}
回答by bbsimonbb
So what do we say about all this in 2017? 80k consultations suggests that running a SQL request in EF is something a lot of folk want to do. But why? For what benefit?
那么，我们对 2017 年的这一切有什么看法呢？80k 咨询表明在 EF 中运行 SQL 请求是很多人想做的事情。但为什么？为了什么利益？
Justin, a guru with 20 times my reputation, in the accepted answer gives us a static method that looks line for line like the equivalent ADO code. Be sure to copy it well because there are a few subtleties to not get wrong. And you're obliged to concatenate your query with your runtime parameters since there's no provision for proper parameters. So all users of this method will be constructing their SQL with string methods (fragile, untestable, sql injection), and none of them will be unit testing.
贾斯汀是一位声誉比我高 20 倍的大师，他在接受的答案中为我们提供了一个静态方法，该方法看起来像等效的 ADO 代码。一定要好好复制它，因为有一些微妙之处不会出错。并且您必须将您的查询与您的运行时参数连接起来，因为没有提供适当的参数。因此，此方法的所有用户都将使用字符串方法（脆弱、不可测试、sql 注入）构建他们的 SQL，并且它们都不会进行单元测试。
The other answers have the same faults, only moreso. SQL buried in double quotes. SQL injection opportunities liberally scattered around. Esteemed peers, this is absolutely savage behaviour. If this was C# being generated, there would be a flame war. We don't even accept generating HTML this way, but somehow its OK for SQL. I know that query parameters were not the subject of the question, but we copy and reuse what we see, and the answers here are both models and testaments to what folk are doing.
其他答案有相同的错误，只是更多。SQL 埋在双引号中。SQL 注入机会随处可见。尊敬的同行，这绝对是野蛮的行为。如果这是生成的 C#，将会有一场火焰War。我们甚至不接受以这种方式生成 HTML，但不知何故它对 SQL 来说是可以的。我知道查询参数不是问题的主题，但我们复制并重用我们看到的内容，这里的答案既是人们所做的事情的模型和证明。
Has EF melted our brains? EF doesn't want you to use SQL, so why use EF to do SQL.
EF 融化了我们的大脑吗？EF不想你用SQL，那为什么要用EF来做SQL呢？
Wanting to use SQL to talk to a relational DB is a healthy, normal impulse in adults. QueryFirstshows how this could be done intelligently, your sql in .sql file, validated as you type, with intellisense for tables and columns. The C# wrapper is generated by the tool, so your queries become discoverable in code, with intellisense for your inputs and results. End to end strong typing, without ever having to worry about a type. No need to ever remember a column name, or its index. And there are numerous other benefits... The temptation to concatenate is removed. The possibility of mishandling your connections also. All your queries and the code that accesses them are continuously integration-tested against your dev DB. Schema changes in your DB pop up as compile errors in your app. We even generate a self test method in the wrapper, so you can testnew versions of your app against existing production databases, rather than waiting for the phone to ring. Anyone still need convincing?
想要使用 SQL 与关系数据库交谈是成年人的一种健康、正常的冲动。QueryFirst展示了如何智能地完成此操作，您在 .sql 文件中的 sql，在您键入时进行验证，并为表和列提供智能感知。C# 包装器由该工具生成，因此您的查询可以在代码中被发现，并为您的输入和结果提供智能感知。端到端强类型，无需担心类型。无需记住列名或其索引。并且还有许多其他好处...消除了连接的诱惑. 也可能错误处理您的连接。您的所有查询和访问它们的代码都会针对您的开发数据库进行持续集成测试。数据库中的架构更改会作为应用程序中的编译错误弹出。我们甚至在包装器中生成了一个自检方法，因此您可以针对现有的生产数据库测试应用程序的新版本，而不是等待电话响起。还有人需要说服吗？
Disclaimer: I wrote QueryFirst :-)
免责声明：我写了 QueryFirst :-)
