Sergey Barskiy's Blog

In the EF version 1.0, there was a problem when one would try to update multiple contexts as part of a single transaction.  For example, I can use Transaction Scope, the update one context, then second context.  Here is sample code to illustrate:

using (TransactionScope scope = new TransactionScope())
{
    try
    {
        using (RolodexEntities context = new RolodexEntities())
        {
            context.Connection.Open();

            Company company = new Company();
            company.CompanyName = "Test 1";
            company.DateAdded = DateTime.Today;
            context.AddToCompanies(company);
            context.SaveChanges();

            using (RolodexEntities context2 = new RolodexEntities(context.Connection as EntityConnection))
            {
                Company company2 = new Company();
                company2.CompanyName = "Test 1";
                company2.DateAdded = DateTime.Today;
                context2.AddToCompanies(company2);
                context2.SaveChanges();
            }
        }

        scope.Complete();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

This code in version 1.0 would throw an exception when second context’s constructor was called.  The exception was quite obscure and was referring to the fact that the connection was already open.  This was actually correct, but that was the whole reason I was passing in the connection.  I was trying to make sure that my transaction would not get promoted to Distributed Transactions Coordinator.  The transaction would always get promoted as soon as there are multiple SQL Server connections used within using scope/.end using scope code block.  So, in version 1.0 MSDTC would be required in the code above.

This issue still exists in EF 4.0.  You can vote to fix the issue here : https://connect.microsoft.com/data/feedback/details/533240/cannot-share-a-store-connection-between-multiple-contexts-in-order-to-avoid-dtc-promotion

Here is the code that confirms the problem:

using (TransactionScope scope = new TransactionScope())
{
    try
    {
        using (RolodexEntities context = new RolodexEntities())
        {
            context.Connection.Open();

            Company company = new Company();
            company.CompanyName = "Test 11";
            company.DateAdded = DateTime.Today;
            context.AddToCompanies(company);
            context.SaveChanges();

            RolodexEntities1 contextTemp = new RolodexEntities1();
            MetadataWorkspace space = (contextTemp.Connection as EntityConnection).GetMetadataWorkspace();
            EntityConnection connection = new EntityConnection(space, (context.Connection as EntityConnection).StoreConnection);

            using (RolodexEntities1 context2 = new RolodexEntities1(connection))
            {
                User user = new User();
                user.UserName = "test";
                user.Role = "User";
                context2.AddToUsers(user);
                context2.SaveChanges();
            }
        }

        scope.Complete();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Please feel free to ask any questions on this subject.

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

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.