Posts Tagged ‘Entity Framework’

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);

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

Categories: C#, Entity Framework Tags: , ,

Entity Framework 4–Load metadata exception

What could be more simple?

I’ve created Console project (same for Test projects) to check my DAL created with EntityFramework 4. Everything looked Located right, everything compile, and yet, I keep get the following message:

“The specified named connection is either not found in the configuration, not intended to be used with the EntityClient provider, or not valid.”

So I open Google to find out what it is. I found some great posts, talk about a case like mine – when the edmx model is not the executed one. Some said copy the config file, some said edit the connection string and tell to EF where to find the Meatadata,
But somehow – still nothing worked for me.

After a short investigation, I discovered that when the model is called the first time, all the metadata uploaded to memory, according to the metadata section in the connection string:

provider connection string=&quot;
data source=.\sql2008;
initial catalog=AdventureWorks;
integrated security=True;

My solution actually contains three projects: one containing the model, second containing the Entities created by using the SelfTracking template, and the third – the console project I ran.
In fact, only because I called only classes from the second and third projects –the DLL that contains the metadata files as a resource not yet been loaded to memory, so the metadata didn’t found.
When ObjectContext constructor called, he actually scans all loaded files for metadata resource, and if he cant find it, he throws the exception above.

All you need do is force the loading of the metadata DLL, like calling Assembly.LoadFile () or something similar, or by simply declare a class from that DLL, and it will load automatically.

Categories: C#, Entity Framework Tags: , ,
%d bloggers like this: