Using Entity Framework as Data Access Layer in n-Tier applications

I have been using Entity framework as DAL for a CSLA for Silverlight based application.  I have player a role in uncovering a number of issues that make it very hard to use in these circumstances.  Here is my (surely incomplete) list.

1. Foreign keys handling.  The actual IDs that are used as foreign keys are not built as properties when you generate a model from a database.  Instead you get navigation properties.  For example, you have User and UserTypes table.  Users table has UserTypeID in it.  When you build a mode for this, you will have UserTypes property in Users entity, but not access to UserTypeID.  As a result, when you are rehydrating a users object in n-Tier environment, you have to have an instance of UserTypes entity to attach a user to.  Your options are to retrieve it from database (inefficient) or write a helper class that creates one on the fly.  To do so, you create an instance of UserTypes entity, then set entity key on it, then attach it to context.  To do all this, you will need to know ID in advance.  You can do this by using UserTypesReference property in Users entity – it will contain UserTypesID when you retrieve an instance of Users from DB.  You can store it in a property of the user object (CSLA for example) that you will use in UI.

2.  Another foreign key annoyance is an instance when you have multiple columns from one table relating to another table.  For example, Users table has columns PrimaryType and SecondaryType, both relating to UseTypes table.  As a result you will get navigation properties UserTypes1 and UserTypes2 in Users entity.  This is pretty much useless because as you write code against that, you will need to constantly have your model open to figure out what ID you need to use.  On top of that, if you use .Include(“UserTypes”) syntax to get the user with both primary and secondary columns, only one of the UserTypes properties will be populated with an instance of UserTypes if you have different IDs in property and secondary columns.  What do you do then?  Your only option is to use UserTypes1(2)Reference property to either get an ID or retrieve an instance of UserTypes table

3.  Third foreign key issue is concurrency handling.  Typically, for all properties you can indicate if a column is to participate in concurrency handling.  For some reason, you cannot do the same for navigation properties – they always participate in concurrency handling if they are changing.  As a result, you have to always keep track of old and new value for each navigation property,  You would set its old value before attaching it to the context, then set it to the new value.  If you capture the SQL query that is sent to the DB when the values are not the same, you will find navigation properties are part of the where clause / concurrency checking.  Sad but true.

4.  Re-using context for Oracle and SQL server (or any multiple databases).  Currently Microsoft ships entity framework only with SQL Server provider.  So, you are stuck buying some third party Oracle provider and hope that the model it generates will be the same as SQL server model, so that you only need to change connection string when switching databases.  There is a promise we might get there one day, but today is not that day.

Leave a Reply

Your email address will not be published. Required fields are marked *