Home > C#, Entity Framework > Handling logical delete with Entity Framework 4

Handling logical delete with Entity Framework 4

Many systems use logical delete in DB, instead of physical delete. For example, when the end user want to delete some records from the DB (not directly, of course), the Data access layer should only set the record’s Deleted column to true, and not use the SQL Delete statement. This behavior is very common used, and very convenient for restoring the data later in case of mistakes. But, of course, it has it drawbacks. In this article, I want to focus on two of them, specifically when using Entity Framework 4:

1. Filtering every query with ‘Deleted = false’ can be very exhausting job. When using Include(), filtering on the second and below levels can force us sometime create more than one query.

2. As I said, sometimes only part of the tables use the logical delete, and others keeps with the physical. We would like to have a generic solution for that, without making changes all the time. For example, if tomorrow we will want to add a logical delete for some table, of course we’re not going to change each query this table appears in.

So, what we got?

I’ve created some simple model, describing Schools DB. As you can see in the diagram, the School & Class entity has logical delete support (by ‘Deleted’ column), and the Student entity needs to be physically deleted when calling delete:

image

First, we will add condition  to the School & Class entities, forcing the model only bring us the undeleted rows (our assumption is, that we really won’t need any deleted rows in our code), like this:

image

We should also delete the ‘Deleted’ column from the model, in order to avoid exceptions. Meaning of this is, that from now on, we will not see the ‘Deleted’ column in our code. Only undeleted rows will be retrieved from DB, and no developer can set (or filter by) this column. So, how we goanna delete exists column?

For this purpose we need to add some code. lets create a partial class for our context, and override the SaveChanges() method. This method is been called each time the model saving changes, so we can catch all deleted items here, and decide whether to actually delete it or not:

var deleted = this.ObjectStateManager.GetObjectStateEntries(EntityState.Deleted);

Next, we will look for each item if he has ‘Deleted’ column in the DB, and execute direct  Update statement. For doing that, we will need to investigate the SSDL (Store model). Remember that Entity framework works with 3 models:

1. CSDL – the conceptual model, describing what you’re going to see in your code.

2. SSDL – the store model, describing your DB.

3. C-S mapping – the mapping model, describing the connections between the two above.

Because we already delete the ‘Deleted’ column from the CSDL, we should ask the SSDL for this column, like this:

var storeItem =  this.MetadataWorkspace.GetItems<EntityType>(DataSpace.SSpace).Where(et => et.Name == deletedItem.EntitySet.Name).FirstOrDefault();

if (storeItem != null)
{
//find the delete property
var deleteProperty = storeItem.Properties.Where(ep => ep.Name == "Deleted").FirstOrDefault();

For simplicity, I supposed that EntitySet.Name and the store name should be equal. Of course, if it’s not, just explore the mapping model to find the correct name. Notice that there is some trick when working with SSDL metadata. Entity framework first load the CSDL, and the SSDL will not be loaded till he needs to create a query for the first time. In order to force the SSDL to load, we will make a fake call to the ToTraceString() method before we will investigate the SSDL. This should be called only one per process:

private void EnforceSsdl()
{
(this.Classes as ObjectQuery).ToTraceString();
}

Next, we will get the entity key name and value, in order to execute direct update on the DB. This code is only for the simple case where you have only 1 key. For complex cases, simply change this:

const string deleteSql = "Update {0} Set Deleted=1 Where {1}={2}";

var keyName = storeItem.KeyMembers.First().Name;
var keyValue = deletedItem.EntityKey.EntityKeyValues.First().Value;
var sql = string.Format(deleteSql, storeItem.Name, keyName, keyValue);
this.ExecuteStoreCommand(sql);

Finally, don’t forget to change this item state back to unchanged, to ensure that EF will not delete it as part of the transaction. Close the method with returning the base.SaveChanges() and it’s done. you can find the Full source code link below.

Source Code Here

Advertisements
Categories: C#, Entity Framework Tags: , ,
  1. Brikesh Kumar
    25/01/2012 at 19:44

    I am new to EF. I need to implement the soft delete feature for all the entities. I followed what you described in follwoing way
    1. Created an interface ISoftDelete, all my POCO classes implement this interface.
    2. Overrided the SaveChange method in the context class

    public override int SaveChanges(SaveOptions options)
    {
    var deletedEntities = _getDeletedEntities();
    _softDelete(deletedEntities);
    return base.SaveChanges(options);
    }

    //public override

    private void _softDelete(List deletedEntities)
    {
    deletedEntities.ForEach(e =>
    {
    ObjectStateManager.ChangeObjectState(e, System.Data.EntityState.Modified);
    e.IsDeleted = true;
    });
    }

    private List _getDeletedEntities()
    {
    return ObjectStateManager
    .GetObjectStateEntries(System.Data.EntityState.Deleted)
    .Select(entry => entry.Entity)
    .OfType()
    .ToList();
    }
    3. Now when I modify the model to bring only the undeleted records, as showed in the diagram above, I get the following error.
    Error 1 Error 3004: Problem in mapping fragments starting at line 650:No mapping specified for properties EntitySQLQuery.IsDeleted in Set EntitySQLQueries.
    An Entity with Key (PK) will not round-trip when

    Q: How will overiding the SaveChanges will let EF know that it needs to insert IsDeleted = true in the DB table’s column?
    Q: Why am I getting that error when adding a condition for load?

    EF sucks 😦

  2. 26/01/2012 at 08:15

    Hi Brikesh,

    When using property as a Condition in the model, you cannot have this property at the CSDL level, you must delete the mapping for this specific entity. Thats why you get your first exception.
    Hence, when implementing the soft delete you cannot use entity.IsDeleted = true, because the IsDeleted property is not part of the model, it’s only part of the SSDL (the storage level).
    In order to deal with that issue I need to create delete sql statement and run it directly against the DB. Please refer to step 3, there you can see how to investigate the SSDL in order to get the table name, and use it as part of the delete statement: delete from [TableName] where [pkColumn] = [pkValue].

    Please contact me for any other questions you have.

    Regards,
    Asaf.

  3. Brikesh Kumar
    26/01/2012 at 17:41

    I gave up soft delete, doing a physicaql delete instead. For such a simple task if it has to be done like this then I would say, EF design is flawed.

  4. Brikesh Kumar
    26/01/2012 at 17:42

    Thanks for your response Asaf

  5. Bellamatte
    25/04/2012 at 18:01

    Quote: “For simplicity, I supposed that EntitySet.Name and the store name should be equal. Of course, if it’s not, just explore the mapping model to find the correct name.”

    How can I find it programmatically directly from inside the method SaveChanges?

  6. Debra Paponette
    23/01/2014 at 21:29

    hey, i am new to entity framework as well. Can soft delete be done for Dynamic Data Entity Framework 4? i tried to follow your example but it crashes when i run it “cannot evaluate expression because a thread is in a stack overflow state”. i really need come help

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: