删除多条记录与实体框架使用单个LINQ查询 [英] Deleting multiple records with Entity Framework using a single LINQ query

问题描述

我试图执行删除使用LINQ将生成一个查询

下面就是我正在做的：

<预类=郎-CS prettyprint-覆盖> // NorthwintEntities是一个ADO.NET Entitity数据模型
变种northwindEntities =新NorthwindEntities（）;
northwindEntities.Order_Details.Delete（O => o.Order_ID == 11076）;


下面是我的扩展：

<预类=郎-CS prettyprint-覆盖> 公共静态类EntityExtensions
{
私有静态正则表达式rxTableName =新的正则表达式（@^ FROM\s +（LT;表> \ [^ \] * \（\.\ [[^ \] * \]）{0,2}）\s + AS\s +（小于？别名> \\ \\ [[^ \] * \]），RegexOptions.Multiline）;

公共静态无效删除< T>（该对象集< T>实体，表达式来; Func键< T，BOOL>>表达式）其中T：EntityObject
{
变种selectQuery = entity.Where（表达式）。选择（X =大于1）;

串selectQueryString =（（的ObjectQuery）selectQuery）.ToTraceString（）;

串deleteQueryString = ConvertSqlSelectToDelete（selectQueryString）;

entity.Context.ExecuteStoreCommand（deleteQueryString）;
}

私人静态字符串ConvertSqlSelectToDelete（字符串selectQuery）
{
如果（selectQuery.IndexOf（加入）-1）〜
{
抛出新的异常（与连接查询不支持：+ selectQuery）;
}

匹配匹配= rxTableName.Match（selectQuery）;
如果（！match.Success）
{
抛出新的异常（无法转换SELECT：+ selectQuery）;
}

串deleteQuery =删除\r\\\
+ selectQuery.Substring（match.Index）;
deleteQuery = deleteQuery.Replace（match.Groups [别名]值+，。）;
deleteQuery = deleteQuery.Replace（AS+ match.Groups [别名]值，）;

返回deleteQuery;
}
}


这工作，但我有一些意见。

• 我不是在这里使用正则表达式的忠实粉丝，但它是我得到的表名的唯一方法。 （。entity.EntitySet.Name并不总是返回正确的名称[订单详细信息]是一个例子）


• 完成之后，我发现这个的http://msmvps.com/blogs/matthieu/archive/2010/05/21/bulk-delete -v3.aspx 但不能让它反正工作。不停地从上下文被空。


• 得到一个NotImplementedException与加入的似乎不工作，删除。我与SQL Server Compact 3.5测试，也许它是一个限制

所以我的问题是：是否有一个简单的方法来做到这一点？如果是这样，那是什么？

任何帮助都将不胜感激。

解决方案

1. 安装实体框架的扩展库
   （PM>安装封装EntityFramework.Extended）




2. 导入EntityFramework.Extensions在你的代码




3. 删除通过内部查询

   
   
   指定的记录

     context.Orders.Where（O = GT; o.User_ID == 1）.Delete（）; 
     

删除里面加上用户ID = 1的订单的所有记录

I'm trying to perform a DELETE using LINQ that will generate a single query.

Here's how I'm doing it:

// NorthwintEntities is an ADO.NET Entitity Data Model
var northwindEntities = new NorthwindEntities();
northwindEntities.Order_Details.Delete(o => o.Order_ID == 11076);

Here's my Extension:

public static class EntityExtensions
{
    private static Regex rxTableName = new Regex(@"^FROM\s+(?<table>\[[^\]]*\](\.\[[^\]]*\]){0,2})\s+AS\s+(?<alias>\[[^\]]*\])", RegexOptions.Multiline);

    public static void Delete<T>(this ObjectSet<T> entity, Expression<Func<T, bool>> expression) where T : EntityObject
    {
        var selectQuery = entity.Where(expression).Select(x => 1);

        string selectQueryString = ((ObjectQuery)selectQuery).ToTraceString();

        string deleteQueryString = ConvertSqlSelectToDelete(selectQueryString);

        entity.Context.ExecuteStoreCommand(deleteQueryString);
    }

    private static string ConvertSqlSelectToDelete(string selectQuery)
    {
        if (selectQuery.IndexOf(" JOIN ") > -1)
        {
            throw new Exception("Query with JOIN is not supported: " + selectQuery);
        }

        Match match = rxTableName.Match(selectQuery);
        if (!match.Success)
        {
            throw new Exception("Unable to convert SELECT: " + selectQuery);
        }

        string deleteQuery = "DELETE \r\n" + selectQuery.Substring(match.Index);
        deleteQuery = deleteQuery.Replace(match.Groups["alias"].Value + ".", "");
        deleteQuery = deleteQuery.Replace("AS " + match.Groups["alias"].Value, "");

        return deleteQuery;
    }
}

This works, but I have a few comments.

• I'm not a big fan of using Regex here, but it was the only way for me to get the table name. (entity.EntitySet.Name wouldn't always return the correct name. [Order Details] is a an example).
• After completing this, I found this http://msmvps.com/blogs/matthieu/archive/2010/05/21/bulk-delete-v3.aspx but couldn't get it to work anyway. Kept getting a NotImplementedException from the context being null.
• Delete with join's doesn't seem to work. I'm testing with SQL Server Compact 3.5, maybe it's a limitation of that.

So my questions is: Is there an easier way to do this? If so, what is it?

Any help at all will be appreciated.

解决方案

1. Install Entity Framework Extended Library (PM> Install-Package EntityFramework.Extended)

2. Import EntityFramework.Extensions in your code

3. Delete records specified by inner query

   context.Orders.Where(o=>o.User_ID == 1).Delete();
   

Deletes all records inside Orders with userID = 1

这篇关于删除多条记录与实体框架使用单个LINQ查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！