Entity Framework 5.0, first steps and impressions

I’m mostly a NHibernate user, but EF code first approach is intriguing, especially in situation where the customer prefer to use only Microsoft Stack libraries, thus EF 5.0 is a good approach to write quickly an Efficient data access module in really few clicks. Enabling EF5 on your own project is as simple as adding a reference with Nuget, go to package manager and type

install-package EntityFramework

and the game is done. Now you can start writing a simple class that will represent a table in the database.

public class Customer
    public Int32 Id { get; set; }

    public String Name { get; set; }

    public String Address { get; set; }

Now you need a class that inherits from DbContext to access the database and to expose all the classes you want to be saved/loaded from your database.

public class SampleContext : DbContext
    public DbSet<Customer> Customers { get; set; }

Now you should add a connection string to Sql server database you want to use, this is really simple because by convention the context you created looks for a connection string in configuration file named exactly as the class name, in this example SampleContext

    <add name="SampleContext" 
         connectionString="Database=Samples;Server=(local);Integrated Security=SSPI" 
         providerName="System.Data.SqlClient" />

Actually I’ve no database called Samples in my local server so I wanted to create one with the correct structure to save my simple Customer class, but I can create automatically a database with the right schema to contain my data thanks to EF database-migrations. To enable migrations you should simple type Enable-Migrations on package manager console

PM> Enable-Migrations

Checking if the context targets an existing database…

Code First Migrations enabled for project FirstSteps.

Now you should have another folder called Migrations with a Configuration.cs files in your project, you can edit this file changing the value of AutomaticMigrationEnabled from false to true to enable automatic migrations. Now you can issue Update-Database command in your package manager console.

PM> update-database

Specify the ‘-Verbose’ flag to view the SQL statements being applied to the target database.

No pending code-based migrations.

Applying automatic migration: 201209150928099_AutomaticMigration.

Running Seed method.

Et voilà you have your database generated and ready to use. As you can see from Figure 1, the convention used is to create a table with the very same name of the DbSet properties exposed by your data context and a column for each property of the class.


Figure 1: Newly generated database with database-migration of EF5

Everything is created by convention over configuration, as an example if you have a property called Id, corresponding table column will be the primary key for the table, and if the value is an integer automatically the column is generated with Identity Set to true.


Figure 2: Id column was generated with Identity specification equal to true

Now you can access database simply creating an instance of SampleContext (do not forget to dispose it when you finished using it) and thanks to LINQ provider you can issue query on it, because all DbSet properties you expose are IQueryable so you can use to simply access all Customers with a simple Foreach.

 using (var context = new SampleContext())
      Console.WriteLine("LISTING CUSTOMERS");
      foreach (var customer in context.Customers)
           Console.WriteLine("Id: {0}\t{1} ({2})", customer.Id, customer.Name, customer.Address);

With really few lines of code you have created entity classes, corresponding database and can write/load data on it.


Entity Framework error 3002 on views when model updated

One of the most annoying problem with the first version of entity Framework is that you have barely no option other than go with “Database First approach”, and when you start mapping database views your life started to become difficult.

The first obvious problem is that the designer, during the import phase from database, try to detect in the view any not-null column and make it part of the primary key of the entity. If you decide to live with it (and having entities with lot of field used as primary keys), you probably will incur in the error 3002 when you update a view and then update the model from database.

The problem happens if you change the definition of the view, and a column that was not null, became nullable by the modification. What it happens is usually that the designer still mark the corresponding property of the entity as primary key a situation that is not admitted if the field in the database is NULL. If this is your situation you can usually fix it going to “mapping details” of the view that raise the error.


Figure 1: A view with error, the StartDate becomes nullable in the view, but the entity still uses it at key

The problem is depicted in Figure 1, you can see in the left that StartDate is not a key of the database model, because I changed the view and now the StartDate admit NULL value, but in the right part, the model still uses that property as a key.

The fix is easy, just select the StartDate property in the model, then in properties windows set to false the EntityKey property, set the Nullable property to true and the error should go away.

Gian Maria.

Entity Framework 4.1–Collection Relations

Part 1: First Steps
Part 2: Managing Relations

Relations between objects are not limited to an object that keep a reference to another object (as seen in part 2), but I can have an object that contains a list of other objects. This is the classic RDBMS rough equivalent of the One-To-Many relation. I decided that my Warrior can carry a certain number of items, so I defined an Item class and a property on Warrior class to hold reference to a list of Items.

   1: private IList<Item> _items;

   2: public IList<Item> Items

   3: {

   4:     get { return _items ?? (_items = new List<Item>()); }

   5:     set { _items = value; }

   6: }

This is a standard implementation of a property that permits me to establish that a Warrior can have a list of Items. In the Getter part I use lazy initialization, so I can simply add an IList<Item> from external code, or I can simply let the object auto-initialize a standard list for me. This is the code that actually adds some item to a Warrior.

   1: Warrior Caramon;

   2: using (var db = new BattlefieldContext())

   3: {

   4:     Caramon = new Warrior { Name = "Caramon", ExperiencePoints = 342553 };

   5:     Caramon.ActiveWeapon = new Weapon() { Damage = 10, Size = 3 };

   6:     Caramon.Items.Add(new Item() { Name = "Ring of invisibility" });

   7:     Caramon.Items.Add(new Item() { Name = "Health Potion" });

   8:     db.Warriors.Add(Caramon);

   9:     db.SaveChanges();

  10: }

Again I want to point out that I did not make any other changes to the BattlefieldContext or to something related to persistence, I just defined the Item class and the Items property on the Warrior class, but I’m able to save everything to the database without writing any other line of code. Here is the query generated by EF.


Figure 1: Generated query to insert a warrior with two items.

This is the Database Schema that EF created to persist my objects.


Figure 2: The database schema to persist Warrior and Items

As you can see, EF created the Warrior_id column on the Items table to be able to keep the relation between items and warriors.

I want to strongly point out that this is nor DDD nor Domain Modeling, I’m simply using EF4.1 like a Super Dataset, to avoid writing CRUD. My primary reason for this little tutorial is moving people from HandWritten SQL code or from old style Dataset to something more flexible and more object oriented.



Entity Framework, a super dataset?

Entity Framework is quite a good product, but in my opinion still misses some point to be called an ORM. I must admit that I never used the 4.1 Code first in real project, but there are some stuff that still does not convince me when using EF.

When I decide to use EF (and not NH)

Sometimes I need to access small legacy databases to do a small amount of operations and I must admit that adding a EF Model from Visual Studio is a matter of seconds and the LINQ provider of EF is quite good so it is a valuable alternative to create a quick DAL. I have a simple database (four tables), I created an EF model, and then issue this query.


Figure 1: A very simple piece of code that query database with EF

This is a very super simple piece of code, in the first line I grab an instance of Term from the database (actually I grab the first one, in real code I have a complex query that returns a specific term). Then I want to grab all instance of Verbs that are related to this term, so I issue the second query, what will be the result?

This image exceptionally capture my face the first time I ran this query.


Figure 2: Strange error in the query.

I’m really disappointed from this exception for many reasons. First of all I do not know why it does not work, because this is a perfectly and reasonable query to issue.

Fix the query to make it works

To verify that the query is indeed ok lets modify it in this way.


Figure 3: Modified version of the query, now it works.

This version works simply changing the order of the Where and ToList operators. It now works because it loads the entire Verbs table in memory and then filters out with LINQ to Object. This clearly makes me sick, because this creates a big memory pressure. The second issue with the above error is the error message, that is really obscure. Unable to create a constraint value of type xxxxxxxx. If you read with caution you could eventually understand that you cannot put a condition based on equality of objects, but only primitive type.

This error could have a good reason to exists: if the Term entity overrides the Equality method, it could be impossible to create an equivalent query in SQL to mimic the same behavior by the EF LINQ provider. Suppose that the Term overrides the Equal operator comparing two entities by checking for equality of the Value and Type properties, how could the EF provider understand this behavior to issue an equivalent query to the database? I can agree with this reason, but only if the error would be something like “Could not issue a comparison between two entities in a EF query because it is impossible to mimic the semantic of equals operator.. bla bla bla”.

A real solution, but you need to make your hands dirty

But working with objects in DDD or Domain Model or even with Active Record pattern, the programmer in 99.99% of the situation, translate the condition V.Term == term with: “gives me all verbs that are connected with term”. This because we are used to the fact that a reference from Verb entity to Term entity is part of the object graph and this can easily be translated with a comparison between identity operators. Given that, you can rewrite the above query in this way.


Figure 3: The right way to issue the EF query

Now the query runs fine, but you can have two completely different opinion about it.

The first opinion is negative, because I need to understand the name of the property/properties used as id by the ORM. I know that by convention you can use a property named Id for every entity, but sometimes, especially with legacy database you can have entities with composite key or identity property named differently. LINQ is great to express query in the object model, but in this situation I need to be aware of details of the ORM Mapping to issue the right query and this is bad.

The second type of opinion is positive, because this query does not suffer from mismatch between the LINQ to entities counterpart when someone redefines the equals operator for the entity.


I tend to agree with the dislike part, because I want my ORM to permit me to express query without being aware of the details. So I tend to consider EF not a real ORM but something like a SuperDataset, it is quick to use with database first approach, it permits you to create a DAL quickly, but it forces you to know the detail of the database, like a dataset. Clearly EF has really a lot of interesting feature respect to a dataset, but it still miss something to be considered an ORM.



Mapping private properties with EF 4.1 RC and Fluent mapping

EF 4.1 is now in RC phase and as a NHibernate user I’m curious to check the fluent API to map entities to database. One of the feature that I and Andrea miss most is the possibility to map private properties with fluent interface. It seems strange to map private properties at once, but it can be useful in DDD. Suppose you have these simple classes.


Figure 1: A really simple domain

We have two things to notice, the first is that the Category class has a private property called PrivateDetails, and the other is that the Products collection is protected, and you can add products from the outside thanks to the AddProduct() method.

   1: public class Category

   2: {

   3:     public Int32 CategoryId { get; set; }

   4:     public string Name { get; set; }

   5:     private String PrivateDetails { get; set; }


   7:     private ICollection<Product> _products;

   8:     protected virtual ICollection<Product> Products

   9:     {

  10:         get { return _products ?? (_products = new HashSet<Product>()); }

  11:         set { _products = value; }

  12:     }


  14:     public void AddProduct(Product p)

  15:     {

  16:         Products.Add(p);

  17:     }


  19:     public void SetDetails(String details)

  20:     {

  21:         PrivateDetails = details;

  22:     }

  23: }

The idea behind this is that you should access only the AGGREGATE roots, not manipulating directly the collection of Products, this forces the user of the class to use specific methods. Now a problem arise, how we can map this class with EF 4.1 fluent interface? The problem is generated from the Fluent interface, that permits only to specify properties with Lambda.


Figure 2: The HasMany() method accepts an Expression

As you can see if I specify that CategoryId is mapped to an Identity database column with the instruction

Property(c => c.CategoryId)

this technique is known as static reflection and is really useful in such scenarios, but … now I could not use the HasMany() methods to map a protected property.


Figure 3: How can I map a protected property if I could not use in a lambda?

This problem derives only from the Fluent Interface, because EF is internally capable to map private members of objects, then we can use a little trick. I want to be able to write code like this

   1: this.HasMany<Category, Product>("Products");

This would solve all our problems, because with this statement I’m asking EF to map a collectino called Products. Fortunately writing such an extension method is quite simple, it is just a bunch of Expressions

   1: public static ManyNavigationPropertyConfiguration<T, U> HasMany<T, U>(

   2:    this EntityTypeConfiguration<T> mapper,

   3:    String propertyName) 

   4:     where T : class 

   5:     where U : class

   6: {

   7:     Type type = typeof(T);

   8:     ParameterExpression arg = Expression.Parameter(type, "x");

   9:     Expression expr = arg;


  11:     PropertyInfo pi = type.GetProperty(propertyName,

  12:         BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance);

  13:     expr = Expression.Property(expr, pi);


  15:     LambdaExpression lambda = Expression.Lambda(expr, arg);


  17:     Expression<Func<T, ICollection<U>>> expression = 

  18:         (Expression<Func<T, ICollection<U>>>)lambda;

  19:     return mapper.HasMany(expression);


  21: }

This code seems complex but it is rather simple. It creates a parameter expression of the same type of the object, then grab a reference to the property from its name with reflection and with the PropertyInfo creates an Expression.Property. This expression (created in line 13) is the equivalent of c => c.Products lambda and it can be passed to the Expression.Lambda to create an Expression<Func<T, ICollection<U>>> object, expected from the HasMany() method.

With the same technique I can write an extension method that maps a private string property.

   1: public static StringPropertyConfiguration PropertyStr<T>(

   2:     this EntityTypeConfiguration<T> mapper,

   3:     String propertyName) where T : class

   4: {

   5:     Type type = typeof(T);

   6:     ParameterExpression arg = Expression.Parameter(type, "x");

   7:     Expression expr = arg;


   9:     PropertyInfo pi = type.GetProperty(propertyName, 

  10:         BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance);

  11:     expr = Expression.Property(expr, pi);


  13:     LambdaExpression lambda = Expression.Lambda(expr, arg);


  15:     Expression<Func<T, String>> expression = (Expression<Func<T, string>>) lambda;

  16:     return mapper.Property(expression);

  17: }

Thanks to those two methods now I’m able to write this mapping for the category class.

   1: public class CategoryMapping : EntityTypeConfiguration<Category>

   2: {

   3:     public CategoryMapping()

   4:     {

   5:         Property(c => c.CategoryId).HasDatabaseGeneratedOption(

   6:             DatabaseGeneratedOption.Identity);


   8:         this.PropertyStr("PrivateDetails").HasColumnName("Details");


  10:         this.HasMany<Category, Product>("Products");

  11:         ToTable("Category");

  12:     }

  13: }

As you can see I’m able to map the PrivateDetails property and I can choose column name, and the Products property with no problem. Now I can use my model

   1: var food = new Category { Name = "Foods" };

   2: food.SetDetails("Details");

   3: db.Categories.Add(food);

   4: Product p = new Product() {Name = "Beer"};

   5: food.AddProduct(p);

   6: int recordsAffected = db.SaveChanges();

As you can ses I’m able to add product without the need to directly access the collection, and I can set a private property through a method (not so useful technique, but just to show that mapping private properties works). Running the sample I got


Figure 4: The fact that three entities were saved confirmed me that the mapping of the protected collection works.

I can verify that everything is ok thanks to EFProfiler.


Figure 5: Thanks to EFProf I can verify that products are correctly saved and linked to the category object

EF 4.1 Code first finally gives to EF the direction towards a real ORM, usable in DDD scenario.


Code is here.