Deleting multiple records with Entity Framework using a single LINQ query

Question

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.

Answer 1

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