Sergey Barskiy

Here is a scenario – I have two tables – Users and Phones.  Users table has two columns that relate to Phones table – PrimaryPhone and SecondaryPhone.  If you build an EF model with these two tables, you will end up with Users entity with two navigation properties that relate to Phones entity – Phones and Phones1.  Yes, I hope this excellent :-( naming convention will be fixed in EF 2.0.  Our goal – retrieve the data for both phones with each user.  If you do the following:

var query = (from oneUser in Users.Include(“Phones”)

you will end up with only secondary (or primary) navigation property in Users object populated.  So, either Users.Phones or Users.Phones1 will have data, the other one will be null.  Important thing to realize when working with Entity Framework is that “.Includes”  statement works off navigation property names, not table names.  So, if you would like to populate both primary and secondary phone, you need to use the following query:

var query = (from oneUser in Users.Include(“Phones”)/Include(“Phones1”)

--Sergey

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.

-- Sergey

I am very tired, but Silverlight application our team has been working on for 6+ months is now officially live!  We saw that someone used it over the weekend!

More reflections on the project are to come in the next few weeks.

--Sergey

My talk at SQL Saturday just ended. My topic was CLR integration - Integrating CLR assemblies to support custom image creation and encompassing complex business logic in SQL server/SSRS.  I posted all the code and slides on Sky Drive. You can scroll down to that section or just follow this link: http://cid-2383900f69b808e0.skydrive.live.com/self.aspx/SQL%20Saturday/SQLSaturday.zip

As always it was a pleasure to talk in front of my fellow developers.

--Sergey.

I will be speaking at the upcoming SQL Saturday in Atlanta on April 25, 2009. 

What is SQL Saturday?  It's a one day free training event for SQL Server professionals organized by a local user group. It takes a lot of work and a lot of volunteers, but it's also a lot of fun!  It is held at Microsoft's offices in Alpharetta, GA.

My topic is CLR integration - Integrating CLR assemblies to support custom image creation and encompassing complex business logic in SQL server/SSRS.

Here is the link to the event: http://www.sqlsaturday.com/eventhome.aspx?eventid=17

--Sergey