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.

Please take a look at the [question][1]. [1]: http://stackoverflow.com/questions/8538899/ — Sergey Vyacheslavovich Brunov, Jan 18 '12 at 05:35
Have you considered RemoveRange in EF6? I'd like to know how much it is "optimised" and what sql it runs. http://msdn.microsoft.com/en-us/library/system.data.entity.dbset.removerange(v=vs.113).aspx — Colin, Oct 26 '13 at 07:06
Getting table name from metadata - http://stackoverflow.com/a/18964974/150342 — Colin, Oct 26 '13 at 07:11

score 8 · Accepted Answer · edited Jun 07 '14 at 09:28

8

Install Entity Framework Extended Library (PM> Install-Package EntityFramework.Extended)
Import EntityFramework.Extensions in your code

Delete records specified by inner query

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

Deletes all records inside Orders with userID = 1

edited Jun 07 '14 at 09:28

andyp

6,229
3
38
55

answered Oct 26 '13 at 04:25

Otto Kanellis

3,629
1
23
24

That's so NOT in the spirit of EntityFramework being limited to a specific provider (SQL-Server). – springy76 May 03 '16 at 16:17

Matija Grcic · Answer 2 · 2015-08-12T23:19:26.257

5

One way to have a batch delete is setting ON CASCADE DELETE on foreign keys. You need to set CASCADE on relation in designer and Set ON CASCADE DELETE on relation in database.

To delete Products navigation property from Order EF will do (number of Products properties)+1 statements to database.

I prefer to do it:

var order = context.Orders.Include(p=>p.Products).Where(o=>o.Order_ID == 11076);

foreach(Product product in order.Products.ToList())
{
  context.Entry(product).State = EntityState.Deleted;
}
context.Entry(order ).State = EntityState.Deleted;
context.SaveChanges();

Hope it helps.

edited Aug 12 '15 at 23:19

answered Mar 05 '12 at 17:26

Matija Grcic

12,963
6
62
90

3

Upon moving for the next item in enumeration, it comes out with error "Item modified from list, cant proceed for enumeration further. – DOM Nov 30 '12 at 06:26
I dont know why @DOM says it doesn't work. It works perfectly fine provided you dont forgot to call ToList() method. – NoobDeveloper Jan 17 '15 at 20:08

Deleting multiple records with Entity Framework using a single LINQ query

2 Answers2

Linked