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]: stackoverflow.com/questions/8538899 — Sergey Vyacheslavovich Brunov
– Sergey Vyacheslavovich Brunov, Commented Jan 18, 2012 at 5:35
Have you considered RemoveRange in EF6? I'd like to know how much it is "optimised" and what sql it runs. msdn.microsoft.com/en-us/library/… — Colin
– Colin, Commented Oct 26, 2013 at 7:06
Getting table name from metadata - stackoverflow.com/a/18964974/150342 — Colin
– Colin, Commented Oct 26, 2013 at 7:11

andyp · Accepted Answer · 2014-06-07 09:28:21Z

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 7, 2014 at 9:28

andyp

6,2993 gold badges41 silver badges55 bronze badges

answered Oct 26, 2013 at 4:25

Otto Kanellis

3,7281 gold badge26 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

springy76 Over a year ago

That's so NOT in the spirit of EntityFramework being limited to a specific provider (SQL-Server).

Matija Grcic · Accepted Answer · 2015-08-12 23:19:26Z

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, 2015 at 23:19

answered Mar 5, 2012 at 17:26

Matija Grcic

13.5k8 gold badges65 silver badges91 bronze badges

2 Comments

DOM Over a year ago

Upon moving for the next item in enumeration, it comes out with error "Item modified from list, cant proceed for enumeration further.

NoobDeveloper Over a year ago

I dont know why @DOM says it doesn't work. It works perfectly fine provided you dont forgot to call ToList() method.

Collectives™ on Stack Overflow

Deleting multiple records with Entity Framework using a single LINQ query

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related