c# - EF 5 Conditional Mapping

Question

I'm using EF 5 Database first approach in my MVC application. all of my tables uses a Field called Deleted which is a boolean field to mark a record is deleted.

I'm trying to get rid of the requirement of having to check Deleted == false every time I query my database. The very straightforward way of doing this is to use a conditional mapping in the edmx file where EF always return data that are not deleted. That's all good.

But the problem of doing this condition mapping is that, when I want to allow the user to delete some record for e.g Address from their address book I don't have access to Delete field from EF as I used it in the conditional mapping and therefore I have to look for another option to allow user to delete a record.

The way I thought is to create a stored proc that handle the delete query and call it when I want to delete the record.

Is there a better way of doing this? Is it possible to make the Delete field accessible even it is used in the conditional mapping?

Answer 1

I have a working solution for Soft Delete in Entity Framework Code First that may help.

The key is that you add a discriminator to every model that you want to be able to soft delete. In code first that is done like this:

modelBuilder.Entity<Foo>().Map(m => m.Requires("IsDeleted").HasValue(false));

This makes it invisible to the context and therefore you have to do the deletes using sql.

If this is the equivalent of your "conditional mapping" in Database First then one way to modify the sql is to override SaveChanges and run sql from there:

public override int SaveChanges()
{
   foreach (var entry in ChangeTracker.Entries()
             .Where(p => p.State == EntityState.Deleted 
             && p.Entity is ModelBase))//I do have a base class for entities with a single 
                                       //"ID" property - all my entities derive from this, 
                                       //but you could use ISoftDelete here
    SoftDelete(entry);

    return base.SaveChanges();
}

private void SoftDelete(DbEntityEntry entry)
{
    var e = entry.Entity as ModelBase;
    string tableName = GetTableName(e.GetType());
    Database.ExecuteSqlCommand(
             String.Format("UPDATE {0} SET IsDeleted = 1 WHERE ID = @id", tableName)
             , new SqlParameter("id", e.ID));

    //Marking it Unchanged prevents the hard delete
    //entry.State = EntityState.Unchanged;
    //So does setting it to Detached:
    //And that is what EF does when it deletes an item
    //http://msdn.microsoft.com/en-us/data/jj592676.aspx
    entry.State = EntityState.Detached;
}

Method used to Get Table Name explained here

That is the way I used to do it. Probably irrelevant to your Database First approach in EF5, but I have now moved to doing it in stored procedures. EF6 Code First generates CreateStoredProcedure calls in Migration files. I replace these with this.CreateDeleteProcedure("dbo.Foo_Delete", "[dbo].[Foos]"); - which is a call to my own extension method:

public static class MigrationExtensions
{
    internal static string DeleteSqlFormat
    {
        //I also hard delete anything deleted more than a day ago in the same table
        get { return "DELETE FROM {0} WHERE IsDeleted = 1 AND DATEADD(DAY, 1, DeletedAt) < GETUTCDATE(); UPDATE {0} SET IsDeleted = 1, DeletedAt = GETUTCDATE() WHERE ID = @ID;"; }
    }

    internal static void CreateDeleteProcedure(this DbMigration migration, string procName, string tableName)
    {
        migration.CreateStoredProcedure(
                        procName,
                        p => new
                        {
                            ID = p.Int(),
                        },
                        body:

                            string.Format(MigrationExtensions.DeleteSqlFormat, tableName)

                    );
    }

}