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:

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:

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