C# 使用实体框架执行自定义 sql？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10898344/
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
execute custom sql with entity framework?
提问by Luis Valencia
I need to execute a customquery which wil be saved somewhere in the database and I need it to return in a datatable, or dataset and bind it to a gridview which will have autogenerate columns to true.
我需要执行一个自定义查询，该查询将保存在数据库中的某个位置，我需要它返回到数据表或数据集中，并将其绑定到一个网格视图，该网格视图会将自动生成的列设为 true。
All my Data access layer works perfect with entity framework, but for some specific scenario I need to do this and I wonder if I should combine ado.net with entity framework, or if EF can do it somehow
我所有的数据访问层与实体框架完美配合，但对于某些特定场景，我需要这样做，我想知道是否应该将 ado.net 与实体框架结合起来，或者 EF 是否可以以某种方式做到这一点
采纳答案by villecoder
If your goal is to return ADO.NET structures (DataTable or DataSet), then just use classic ADO.NET. You'll find it easier than trying to bind data to an Entity set and then populating a DataTable or DataSet yourself.
如果您的目标是返回 ADO.NET 结构（DataTable 或 DataSet），那么只需使用经典的 ADO.NET。您会发现这比尝试将数据绑定到实体集然后自己填充 DataTable 或 DataSet 更容易。
However, if you're really and truly interested running a custom query through EntityFramework, take a look at ExecuteQuery. It allows you to execute a SQL Query and maps the result back to entities in your model. It would then be an exercise on your part to take the IEnumerable result and map it to a DataTable or DataSet. Hence my original answer of "just do it with good ol' fashioned ADO.NET methods."
但是，如果您真的非常有兴趣通过 EntityFramework 运行自定义查询，请查看ExecuteQuery。它允许您执行 SQL 查询并将结果映射回模型中的实体。然后，您需要练习获取 IEnumerable 结果并将其映射到 DataTable 或 DataSet。因此，我最初的回答是“用好的老式 ADO.NET 方法来做”。
回答by Md. Nazrul Islam
For Entity Framework 5use
对于实体框架 5使用
context.Database.SqlQuery
上下文.数据库.SqlQuery
And For Entity Framework 4use the following code
并且对于实体框架 4使用以下代码
context.ExecuteStoreQuery
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;
}
For 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 + "' )";
context.Database.SqlQuery<PanelSerialList>(panelSerialByLocationAndStockQuery).ToList();
}
回答by Peru
Here's another dimension and easier approach. Get SQL Connection using your Entity Framework Context:
这是另一个维度和更简单的方法。使用您的实体框架上下文获取 SQL 连接：
var connection = (System.Data.SqlClient.SqlConnection) _db.Database.Connection;
if (connection != null && connection.State == ConnectionState.Closed)
{
connection.Open();
}
var dt = new DataTable();
using (var com = new System.Data.SqlClient.SqlDataAdapter("Select * from Table", connection))
{
com.Fill(dt);
}
And we can use DataAdapteror any other classic method to execute queries using the EF connection.
我们可以使用DataAdapter或任何其他经典方法来使用 EF 连接执行查询。
This will be very useful when we do something dynamically and when we can't map to a Entity. We can get things in a DataTable for example.
当我们动态执行某些操作并且无法映射到实体时，这将非常有用。例如，我们可以在 DataTable 中获取内容。
The above Syntax is for EF 5.0.
以上语法适用于EF 5.0。
回答by Alezis
I use EF6 and one day I needed way to execute dynamic SQL string and get DataTable. Firstly I merely cast DbContext.Database.Connectionto SqlConnectionand did whole job. It worked for tests, but application were broken, because Glimpse, that we use, injects self implementation of DbConnectionwith type Glimpse.Ado.AlternateType.GlimpseDbConnection. I need approach that works independently of what DbConnection is. Finally I end up with following code:
我使用 EF6，有一天我需要一种方法来执行动态 SQL 字符串并获取 DataTable。首先，我只是投DbContext.Database.Connection来SqlConnection，做整体的工作。它适用于测试，但应用程序已损坏，因为我们使用的 Glimpse 注入了DbConnectionwith 类型的自我实现Glimpse.Ado.AlternateType.GlimpseDbConnection。我需要独立于 DbConnection 工作的方法。最后我得到以下代码：
public class SqlDataProvider : ISqlDataProvider
{
private readonly DbContext _context;
public SqlDataProvider(DbContext context)
{
_context = context;
}
public DataTable GetDataTable(string sqlQuery)
{
try
{
DbProviderFactory factory = DbProviderFactories.GetFactory(_context.Database.Connection);
using (var cmd = factory.CreateCommand())
{
cmd.CommandText = sqlQuery;
cmd.CommandType = CommandType.Text;
cmd.Connection = _context.Database.Connection;
using (var adapter = factory.CreateDataAdapter())
{
adapter.SelectCommand = cmd;
var tb = new DataTable();
adapter.Fill(tb);
return tb;
}
}
}
catch (Exception ex)
{
throw new SqlExecutionException(string.Format("Error occurred during SQL query execution {0}", sqlQuery), ex);
}
}
And this works for any case: for tests where DbContext.Database.Connectionis SqlConnectionand for Glimpse.Ado.AlternateType.GlimpseDbConnection
这适用于任何情况：用于测试 where DbContext.Database.ConnectionisSqlConnection和 forGlimpse.Ado.AlternateType.GlimpseDbConnection
