EF5: How to alter the default Delete function to implement my own

Question

Well, here is my situation:

We have tables that we dont want ever to delete the data. There is a column which is called isDeleted that is supposed to be updated instead instead of deleting it.

I want to develop the accompanying web app with EF5 but i have a problem there. How do i implement that restriction?

I could use stored procedures to delete and select but i was hoping for a way to use the standard functions in EF, just changing how they work. Which way is less arduous and what options i have to achieve what i want, since i´m guessing i´m not the first person to ask for this?

I shudder to suggest this, but have you considered a trigger? You could do an `instead of delete`. — Joe Enos, Sep 24 '13 at 15:10
Yeah, but the next select will still get the data..what i want is all select calls to ignore those "deleted" lines — sergio, Sep 24 '13 at 15:11
@Sergio you are talking about two different things now, soft deleting the data and filtering out record that have been `deleted`. — SOfanatic, Sep 24 '13 at 15:13
I'm adding on to the suggestions that I don't like, but you could wrap a view around the table which only returns "non-deleted" records, and make that your entity. You would need triggers on the view at that point. — Joe Enos, Sep 24 '13 at 15:14
@SOfanatic Well..arent those related? i want to soft delete yes, but i dont want that soft deleted data to be visible ordinarily on the app right? i want to soft delete it for security and traceability but i dont want to always have to exclude records having IsDeleted=true before using the data... i would think this is the regular use of soft deleting? — sergio, Sep 24 '13 at 15:16
I guess a better question would be, how could i overload the linq select to apply whatever filter i want before returning the data? — sergio, Sep 24 '13 at 15:18
@Sergio for soft delete look into this: http://blog.jorgef.net/2010/12/ef-soft-delete.html and for filtering out soft deleted records look into this: http://www.agile-code.com/blog/entity-framework-code-first-applying-global-filters/ — SOfanatic, Sep 24 '13 at 15:32
I personally hate this form of "Soft Delete". The blog post by Jorge Fioranelli only solves one of a whole host of problems. You can try using a View with `Instead of Delete` Trigger. The upside is that you don't have to shotgun your code with `.Where(x => x.Deleted == false)` — Aron, Sep 24 '13 at 15:45
I posted a fairly complete solution over here. http://stackoverflow.com/a/18985828/150342 — Colin, Sep 25 '13 at 01:24
@Colin, please make your comment into an answer so i can accept it — sergio, Sep 26 '13 at 06:43

Sergey Berezovskiy · Accepted Answer · 2013-09-24T16:28:01.203

You can override SaveChanges method of your DbContext. E.g. you want to forbid deleting products. You can save entities with IsDeleted flag set to true instead of deleting them:

public override int SaveChanges()
{
    var deletedPersonEntries = ChangeTracker.Entries<Person>()
                                    .Where(e => e.State == EntityState.Deleted);

    foreach (var e in deletedPersonEntries)
    {
        e.State = EntityState.Unchanged;
        e.Entity.IsDeleted = true;
    }

    return base.SaveChanges();
}

Another option - raise exception if someone tries to delete product:

if (deltedProductEntries.Any())
    throw new Exception("You should not delete products!");

You also can simply set entities state to unchanged, but I don't think its very good solution.

For your third option, I'd go with `e.State = EntityState.Unchanged; e.Entity.IsDeleted = true;` so that only `IsDeleted` is updated, not everything else as well. — , Sep 24 '13 at 16:29

score 1 · Answer 2 · edited May 23 '17 at 11:50

You can do it like this:

In OnModelCreating add an IsDeleted discriminator to every entity that can be soft deleted
Override SaveChanges and find all the entries to be deleted
Run SQL on these entries to set the IsDeleted discriminator then set their state to "detached"
Change any unique indexes to ignore any soft deleted records

You can find working code at this answer: How to soft delete using Entity Framework Code First

You could also use stored procedures instead of the code used in steps 2 and 3 - and I've been looking at how EF6 generates the stored procedures for you. You add code that looks like this:

modelBuilder.Entity<AdministrativeArea>().MapToStoredProcedures();

and that results in a migration that includes this:

        CreateStoredProcedure(
            "dbo.AdministrativeArea_Delete",
            p => new
                {
                    ID = p.Int(),
                },
            body:
                @"DELETE [dbo].[AdministrativeAreas]
                  WHERE ([ID] = @ID)"
        );

Now it's a case of altering the sql in the migration from a delete to an update. It wouldn't be too arduous to do it with text/replace but wouldn't it be cool if we could change the template used to generate the CreateStoredProcedure calls?....

score 0 · Answer 3 · answered Sep 24 '13 at 15:50

CREATE VIEW Foo
AS 
SELECT
    f.Id,
    f.Bar,
    f.Baz,
    f.Qux
FROM Foo_Table
Where f.Deleted = 0;

CREATE TRIGGER Foo_Delete
ON Foo
Instead of Delete
AS 
BEGIN
    Update
        Foo_Table f
    SET
        f.Deleted = 1
    WHERE
        f.Id IN (SELECT Id from Deleted)
END;

EF5: How to alter the default Delete function to implement my own

3 Answers3

Linked