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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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.

Matija Grcic

Related Questions

LINQ query or GetObjectKey to get single Entity Framework entities?

I prefer the latter because it is explicitly clear what it is you want. By using EntityKey (and this is something that the ADO.NET team doesn't seem to understand), we have to work around the structure imposed on us by Entity Framework. By using the query language in the way you did in the second example, we're telling all of the rest of the deve...
asp.net c# entity-framework linq
asked by Rob

Entity Framework - Checking for single records before utilizing them the right manner

Use ...SingleOrDefault... if you expect 0 or 1, or ...FirstOrDefault... if you just need the first record out of potentially many, but can cope with 0. Both will return the default value for the type (usually null) if there are no results....By the way, queries like this are generally more readable (IMO) ...without... using a query expression, so y...
.net c# entity-framework
asked by Jon Skeet

A single Linq-to-Entities query may use many SQL aggregation functions.

I don't have your DB, but this (using a "default" EDMX model of Northwind.mdb -- no changes after running the new model wizard) runs as one query in LINQPad:...var one = from c in Customers where c.PostalCode == "12209" select new { Id = c.Orders.Max(o => o.OrderID), Country = c.Orders.Min(...
.net entity-framework linq-to-entities
asked by Craig Stuntz

Delete a single record from Entity Framework?

You can use ...SingleOrDefault... to get a single object matching your criteria, and then pass that to the ...Remove... method of your EF table....var itemToRemove = Context.Employ.SingleOrDefault(x => x.id == 1); //returns a single item. if (itemToRemove != null) { Context.Employ.Remove(itemToRemove); Context.SaveChanges(); }
c# entity-framework sql-server
asked by Mansfield

How to Delete multiple records in Linq to Entity?

You can do the following, which is technically still a loop....obj.tblA.Where(x => x.fid == i).ToList().ForEach(obj.tblA.DeleteObject); obj.SaveChanges(); ...The alternative is calling the SQL directly.
asp.net c# entity-framework linq sql
asked by Marco

How can I use Entity Framework 6 and my repository to delete multiple records?

You can create another delete method in your generic repository class, see below:... public virtual void Delete(Expression<Func<T, bool>> predicate) { IQueryable<T> query = DbSet.Where(predicate).AsQueryable(); foreach (T obj in query) { DbSet.Remove(obj); } } ...Then you can use it like below,...
asp.net-mvc entity-framework entity-framework-6
asked by Lin

The right way to insert multiple records to a table using LINQ to Entities

Simply move the instantiation of the new Product inside the loop. Your code as it is written will add a single instance multiple times which does not produce what you are after...you need a separate instance of each product...the Add method does not make a copy, it attaches the object to the context and marks it for insertion....foreach (int price...
ado.net c# entity-framework linq-to-entities
asked by terryt

How to delete multiple records with Entity Framework ASP.Net MVC 5?

context.Projects.Where(p => p.ProjectId == projectId) .ToList().ForEach(p => context.Projects.Remove(p)); context.SaveChanges(); ...Taken from this ...very similar post... (which should probably be marked as duplicate).
asp.net-mvc-5 c# entity-framework
asked by Jesse Carter

Entity Framework, Load multiple entities by primary key in a single query while still using caching

Not implicitly....Where()... will always query the database. To explicit query the cache you need to use ....Local...: ...dbContext.Entity.Local.Where(......This will not hit the database but it will only return entities that have been cached/loaded before....Good to know: to view the queries define a Log function like this: ...dbContext.Database.L...
.net c# entity-framework entity-framework-6
asked by Chris

Searching for multiple strings using single database query with entity framework and LINQ

Alternative solution: use the EF 6.2 ...Like...:....Where(x => stringsToSearchFor.Any(s => DbFunctions.Like(x.name, $"%{s}%"))) ...Here's the documentation for ...DbFunctions.Like....
c# entity-framework entity-framework-6 linq linq-to-sql
asked by V0ldek

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

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