EF code先删除批次的IQueryable＆LT; T＆GT;？ [英] EF Code First Delete Batch From IQueryable<T>?
问题描述
我知道这是可能的LINQ到SQL和我见过的点点滴滴，导致我相信这是可能的EF。是否有一个扩展，有可以做这样的事情：
VAR peopleQuery = Context.People.Where（P =＆GT; p.Name ==吉姆）;
peopleQuery.DeleteBatch（）;
其中， DeleteBatch 刚拿起拆开peopleQuery并创建一个SQL语句删除所有相应的记录，然后直接执行，而不是标志认证的所有这些实体为删除查询，有它做他们逐个。我想我发现类似的东西在下面的code，但它会立即失败，因为实例不能强制转换为对象集。有谁知道如何解决这个问题了EF code首先工作？或者知道有这个正在做的一个例子任何地方？
公共静态的IQueryable＆LT; T＆GT; DeleteBatch＆LT; T＆GT;（这IQueryable的＆LT; T＆GT;实例），其中T：类
{
对象集＆LT; T＆GT;查询=实例作为对象集＆LT; T＆GT ;;
ObjectContext的背景下= query.Context;
字符串sqlClause = GetClause＆LT; T＆GT;（实例）;
context.ExecuteStoreCommand（删除{0}，sqlClause）;
返回实例;
}
公共静态字符串GetClause＆LT; T＆GT;（这IQueryable的＆LT; T＆GT;条款）其中T：类
{
串片断=FROM [DBO] [。;
字符串SQL =（（的ObjectQuery＆LT; T＆GT;）条款）.ToTraceString（）;
字符串sqlFirstPart = sql.Substring（sql.IndexOf（片段））;
sqlFirstPart = sqlFirstPart.Replace（AS [Extent1]，）;
sqlFirstPart = sqlFirstPart.Replace（[Extent1]。，）;
返回sqlFirstPart;
}
实体框架不支持批量操作。我喜欢怎样的code可以解决问题，但即使它正是你想要的方式（但ObjectContext的API），这是一个错误的解决方案。
为什么是错误的解决办法？
它仅在某些情况下。任何先进的地图解决方案，实体被映射到多个表（实体分割，TPT的继承），这肯定是不行的。我几乎可以肯定，你可以找到另一种情况下，它不会工作，由于查询的复杂性。
它使环境和数据库不一致。这是对数据库执行任何SQL的一个问题，但在这种情况下，SQL是隐藏的，并使用你的code另一个程序员能错过呢。如果你删除它是加载到上下文实例，同时任何记录，该实体将不会被标记为删除，并从上下文菜单中删除（除非您添加code到你的 DeleteBatch 方法 - 这将是特别复杂，如果删除的记录实际上映射到多个实体（表拆分））
最重要的问题是EF生成的SQL查询和你正在做的这个查询假设的修改。你期望的EF将命名查询中使用的第一个表Extent1 。是的，它真的使用这个名字，但现在它是内部的EF执行。它可以在EF任何小的更新变化。周围的任何API的内部构建定制逻辑被认为是不好的做法。
因此，您已经有对SQL级查询工作，这样你就可以直接调用SQL查询作为@mreyeros表明，避免在此解决方案的风险。你将不得不处理表和列的真实姓名，但是这是你可以控制（你的映射可以定义它们）。
如果你不考虑这些风险，显著你可以做小的改动code，使之在的DbContext API工作：
公共静态类DbContextExtensions
{
公共静态无效DeleteBatch＆LT; T＆GT;（此的DbContext背景下，IQueryable的＆LT; T＆GT;查询），其中T：类
{
字符串sqlClause = GetClause＆LT; T＆GT;（查询）;
context.Database.ExecuteSqlCommand（的String.Format（删除{0}，sqlClause））;
}
私人静态字符串GetClause＆LT; T＆GT;（IQueryable的＆LT; T＆GT;条款）其中T：类
{
串片断=FROM [DBO] [。;
字符串SQL = clause.ToString（）;
字符串sqlFirstPart = sql.Substring（sql.IndexOf（片段））;
sqlFirstPart = sqlFirstPart.Replace（AS [Extent1]，）;
sqlFirstPart = sqlFirstPart.Replace（[Extent1]。，）;
返回sqlFirstPart;
}
}
现在你将调用批量删除是这样的：
context.DeleteBatch（context.People.Where（P =＆GT; p.Name ==吉姆））;
I know this is possible in LINQ-to-SQL, and I've seen bits and pieces that lead me to believe it's possible in EF. Is there an extension out there that can do something like this:
var peopleQuery = Context.People.Where(p => p.Name == "Jim");
peopleQuery.DeleteBatch();
Where DeleteBatch just picks apart the peopleQuery and creates a single SQL statement to delete all the appropriate records, then executes the query directly instead of marking all those entities for deletion and having it do them one by one. I thought I found something like that in the code below, but it fails immediately because instance can't be casted to ObjectSet. Does anyone know how to fix this up to work with EF Code First? Or know of anywhere that has an example of this being done?
public static IQueryable<T> DeleteBatch<T>(this IQueryable<T> instance) where T : class
{
ObjectSet<T> query = instance as ObjectSet<T>;
ObjectContext context = query.Context;
string sqlClause = GetClause<T>(instance);
context.ExecuteStoreCommand("DELETE {0}", sqlClause);
return instance;
}
public static string GetClause<T>(this IQueryable<T> clause) where T : class
{
string snippet = "FROM [dbo].[";
string sql = ((ObjectQuery<T>)clause).ToTraceString();
string sqlFirstPart = sql.Substring(sql.IndexOf(snippet));
sqlFirstPart = sqlFirstPart.Replace("AS [Extent1]", "");
sqlFirstPart = sqlFirstPart.Replace("[Extent1].", "");
return sqlFirstPart;
}
Entity framework doesn't support batch operations. I like the way how the code solves the problem but even it does exactly what you want (but for ObjectContext API) it is a wrong solution.
Why is it wrong solution?
It works only in some cases. It will definitely not work in any advanced mapping solution where entity is mapped to multiple tables (entity splitting, TPT inheritance). I almost sure that you can find another situations where it will not work due to complexity of the query.
It keeps context and database inconsistent. This is a problem of any SQL executed against DB but in this case the SQL is hidden and another programmer using your code can miss it. If you delete any record which is in the same time loaded to the context instance, the entity will not be marked as deleted and removed from context (unless you add that code to your DeleteBatch method - this will be especially complicated if deleted record actually maps to multiple entities (table splitting)).
The most important problem is modification of EF generated SQL query and assumptions you are doing on that query. You are expecting that EF will name the first table used in the query as Extent1. Yes it really uses that name now but it is internal EF implementation. It can change in any minor update of EF. Building custom logic around internals of any API is considered as a bad practice.
As a result you already have to work with query on SQL level so you can call the SQL query directly as @mreyeros showed and avoid risks in this solution. You will have to deal with real names of tables and columns but that is something you can control (your mapping can define them).
If you don't consider these risks as significant you can make small changes to the code to make it work in DbContext API:
public static class DbContextExtensions
{
public static void DeleteBatch<T>(this DbContext context, IQueryable<T> query) where T : class
{
string sqlClause = GetClause<T>(query);
context.Database.ExecuteSqlCommand(String.Format("DELETE {0}", sqlClause));
}
private static string GetClause<T>(IQueryable<T> clause) where T : class
{
string snippet = "FROM [dbo].[";
string sql = clause.ToString();
string sqlFirstPart = sql.Substring(sql.IndexOf(snippet));
sqlFirstPart = sqlFirstPart.Replace("AS [Extent1]", "");
sqlFirstPart = sqlFirstPart.Replace("[Extent1].", "");
return sqlFirstPart;
}
}
Now you will call batch delete this way:
context.DeleteBatch(context.People.Where(p => p.Name == "Jim"));
这篇关于EF code先删除批次的IQueryable＆LT; T＆GT;？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
