Using Entity Framework and a single LINQ query, delete numerous records

c# entity-framework linq

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.

1
11
1/18/2012 7:05:45 AM

Accepted Answer

  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

8
6/7/2014 9:28:21 AM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow