none
Bulk deletes in EF 5? RRS feed

  • Question

  • We've been using EF 4.0 in a project for the past 2 years and have run into the lack of bulk delete support more than a couple of times. I've seen some of the documented ways to work around the limitation. To be clear the limitation I am talking about is that in order to delete an object EF must first bring it into memory. I want a way to issue a delete command with a criteria (without putting raw SQL in my code and without creating sprocs).

    So what I'm wondering is if EF 5 (or anything after EF 4.0) brings anything to the table with regards to this issue? I've been looking and have seen no mention of the topic in any EF 4.x/5.0 feature discussions.

    Is anyone intimately familiar with this topic willing to offer some insight?

    Wednesday, October 10, 2012 11:39 PM

Answers

  • Hi,

    There are a couple of batching features that are on EFs radar, though none of them are being actively worked on at the moment.

    What I think you're after is something like what is described here: http://entityframework.codeplex.com/workitem/52

    There is some work being done by a member of the community to implement batching of EF commands. Which might help some of the pain. But isn't really what you are after I think.

    As to when this feature will be implemented, it isn't on the EF 6 roadmap and there are no roadmaps for releases after that yet. Mostly because we don't know what our priorities will be when we finish EF 6. Having said that. If you are particularly interested in this feature and want to look at contributing it to the EF codebase then the EF team would work with you to get it implemented, possibly even into the EF 6 release. If that is something you want to look at then post a discussion thread on the codeplex site about it and the dev team will talk to you about it. There have been a few contributions from the community that have been pulled and will be in the EF 6 release. Otherwise upvoting it is probably the best way to get it implemented sooner.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    • Marked as answer by Curious George Friday, October 19, 2012 12:32 AM
    Friday, October 19, 2012 12:09 AM
    Moderator

All replies

  • Hi Curious George,

    Welcome to MSDN Forum.

    As far as I know, EF5 still doesn't have bulk delete feature, please refer to the new features in EF5.

    What’s New in EF5

    EF 5 includes a number of new features and bug fixes to the EF4.3 release. Most of the new features are only available in applications targeting .NET 4.5, see the Compatibility section for more details.

    • Enum support allows you to have enum properties in your entity classes.
    • Spatial data types can now be exposed in your model using the DbGeography and DbGeometry types.
    • The Performance enhancements that we recently blogged about.
    • Code First will now detect if you have LocalDb or SQL Express available for creating new databases. Visual Studio 2012 includes LocalDb, whereas Visual Studio 2010 includes SQL Express.
    • Code First will add tables to existing database if the target database doesn’t contain any of the tables from the model.

    The EF Designer in Visual Studio 2012 also has some new features:

    • DbContext code generation for new models means that any new models created using the EF Designer will generate a derived DbContext and POCO classes by default. You can always revert to ObjectContext code generation if needed. Existing models will not automatically change to DbContext code generation.
    • Multiple-diagrams per model allows you to have several diagrams that visualize subsections of your overall model. Shapes on the design surface can also have coloring applied.
    • Table-Valued functions in an existing database can now be added to your model.
    • Batch import of stored procedures allows multiple stored procedures to be added to the model during model creation.

    This feature has already in Entity Framework Feature Suggestions, You can vote up the feature, Microsoft will seriously consider your suggestion. : )

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, October 11, 2012 1:49 AM
    Moderator
  • Thanks Allen. Like I mentioned, I have been perusing the feature lists of the various EF releases since 4.0 and had seen no mention of it. I was hoping someone intimately familiar with EF would chime in and speak to whether this is on the roadmap, or maybe snuck into a release but didn't warrant mention in feature lists.
    Thursday, October 11, 2012 2:42 PM
  • Hi,

    There are a couple of batching features that are on EFs radar, though none of them are being actively worked on at the moment.

    What I think you're after is something like what is described here: http://entityframework.codeplex.com/workitem/52

    There is some work being done by a member of the community to implement batching of EF commands. Which might help some of the pain. But isn't really what you are after I think.

    As to when this feature will be implemented, it isn't on the EF 6 roadmap and there are no roadmaps for releases after that yet. Mostly because we don't know what our priorities will be when we finish EF 6. Having said that. If you are particularly interested in this feature and want to look at contributing it to the EF codebase then the EF team would work with you to get it implemented, possibly even into the EF 6 release. If that is something you want to look at then post a discussion thread on the codeplex site about it and the dev team will talk to you about it. There have been a few contributions from the community that have been pulled and will be in the EF 6 release. Otherwise upvoting it is probably the best way to get it implemented sooner.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    • Marked as answer by Curious George Friday, October 19, 2012 12:32 AM
    Friday, October 19, 2012 12:09 AM
    Moderator
  • Yeah the feature mentioned in the link to codeplex does sound like what I am after. I had not seen that nor did I know there was such a voting option over at codeplex. I've upvoted it.

    I have to say I am surprised this isn't on the roadmap and that you don't hear this complaint more often. I'm trying to envision any serious use of EF not needing such a capability. Maybe everyone just resorts to using sprocs or the other work-arounds?

    Friday, October 19, 2012 12:32 AM
  • It's possible that everyone just lets EF do it's thing and send multiple delete commands. From code, conceptually, you can do a bulk delete by removing everything from a collection. So if you are not having performance problems then it probably doesn't matter how EF handles it.

    I guess it really depends on what problems you have been seeing with deletes, do you mind elaborating a bit? I can take the discussion to the team, or at least add it to the issue on codeplex, so that your particular scenario is taken into account when implementation happens.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Friday, October 19, 2012 12:46 AM
    Moderator
  • Yeah perf is the issue for us generally. We have some relations where there are thousands of children to one parent, and if we need to act on all children we end up seeing timeouts and the like. And even if we can work around the timeouts the perf is still just silly compared to what a single sql statement could do.

    A simple example in our model is that we have projects, where each project can contain an unbounded number of children. We have a feature that allows the user to delete a project, which then of course needs to delete all children first. Reparenting all children is another example.

    I appreciate the engagement Glenn, even though I am disappointed in the answer.

    Friday, October 19, 2012 1:00 AM
  • No problems thanks for the extra scenario. I'll make sure we use a scenario like this in our testing if we look at implementing set based delete. Though it would have been anyway I think.

    I suspect that part of the reason that set based operations do not get as much attention is that people have focused much more on batch operations. http://entityframework.codeplex.com/workitem/53


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Friday, October 19, 2012 5:08 PM
    Moderator
  • ZZZ Projects offers 2 kinds of bulk operations via the Entity Framework Extensions Library. They increase drastically your performance over the SaveChanges method from Entity Framework.

    BulkSaveChanges

    The first way is via our main feature, the BulkSaveChanges method which literally replaces the SaveChanges method. You can expect to save your entities a minimum of 10-15 times faster with thousands of entities. This method supports all kinds of associations and entity types (TPC, TPH, and TPT).

    using (var ctx = new DbContextEntities())
    {
    	// ... Make any changes to your DbContext ...
    	ctx.BulkSaveChanges();
    }

    Bulk Operations

    The second ways is via Bulk Operations methods (BulkDelete, BulkInsert, BulkUpdate and BulkMerge) which increases even more the performance and allows customization for many settings like what's the primary key to use.

    using (var ctx = new DbContextEntities())
    {
    	// ... Make any changes to your DbContext ...
    
    	// Use Entity Framework Setting
    	ctx.BulkUpdate(list);
    
    	// OR customize your settings 
    	ctx.BulkUpdate(list, operation => operation.ColumnPrimaryKeyExpression = column => column.ID);
    }
    Monday, December 1, 2014 1:05 AM
  • To be clear the limitation I am talking about is that in order to delete an object EF must first bring it into memory

    You can do this easily with EF 5 and versions below 6, by creating a stub object.

    http://blogs.msdn.com/b/alexj/archive/2009/03/27/tip-9-deleting-an-object-without-retrieving-it.aspx

    You can update the object too without having to retrieve the object, by using the same method to delete it without retrieving it.

    Monday, December 1, 2014 1:36 AM