Tip 20 – How to deal with Fixed Length Keys

  • [アーティクル]

This is 20th post in my ongoing series of Entity Framework Tips.

Fixed Length Field Padding:

If you have an fixed length column in the database, for example something like NCHAR(10) when you do an insert, padding happens automatically. So for example if you insert ‘12345’ you get 5 spaces automatically appended, to create a string that is 10 characters long.

Most of the time this automatic padding doesn’t matter. But if you use one of these Columns as your Primary Key with the Entity Framework you can run into trouble with identity resolution.

So what is Identity Resolution?

Identity Resolution is an important feature that most ORMs support. It insures that you only ever have one object in memory for each ‘Entity’ in the database.

So if already have an object in the ObjectContext that represents a particular ‘Entity’ and you issue another query for that ‘Entity’ the query should return the same object again, rather than creating another object.

This is important because it stops you getting into situations where you make multiple contradictory changes to the same ‘Entity’ via different objects.

So how can things go wrong?

Things are highly unlikely to go wrong for you, but it is possible. Here’s how…

In the Entity Framework we do ‘Identity Resolution’ based on the value of the EntityKey (aka the Primary Key) in the CLR and we don’t do any automatic padding for you, unlike the database.

What this means is if you write this code where the ProductCode is the primary key of the Product entity and is NCHAR(10):

Product p1= new Product
{
ProductCode = "SG500",
Description = "7200rpm 500 GB HDD"
};
ctx.AddToProducts(p1);
ctx.SaveChanges();

The EntityKey in memory is ‘SG500’. But in the database you get ‘SG500 ’.

Now if you write a query like this:

Product p2 = ctx.Products.First(p => p.ProductCode == "SG500");

Because of SQLServer query semantics this will match the ‘SG500 ’ record in the database, and the resulting ‘Entity’ will have an EntityKey of ‘SG500 ’.

If this query is run against the same ObjectContext identity resolution should kick in and return p1 instead of creating a completely new object, i.e. this shouldn’t fail:

Debug.Assert(Debug.ReferenceEquals(p1, p2));

Unfortunately this isn’t the case because p1 has an EntityKey of ‘SG500’ and p2 has an EntityKey of ‘SG500 ’, which when compared using CLR semantics are clearly not identical. So Identity Resolution Fails, and you end up with different objects.

By this point you may be starting to see how unlikely you are to run into this scenario. It only really matters if you create and later query and modify the same entity via the same context, if you do that it probably means you have a long running ObjectContext, which is not what we generally recommend.

Nevertheless the point of this whole discussion is that while unlikely things might go badly wrong, and it is better to be forewarned.

Guaranteeing you avoid Identity Resolution Problems:

The solution to all this is remarkably simple. When you create your entity for the first time (or attach it) simply pad the key yourself. i.e. something like this:

Product p1= new Product
{
ProductCode = "SG500 ",
Description = "7200rpm 500 GB HDD"
};

You might even want to create a little utility method to do the padding for you:

ProductCode = Pad(“SG500”,10);

If you do this the Entity Frameworks Identity Resolution code will work just fine.

I highly recommend being defensive like this if you use fixed length Primary Keys with the Entity Framework.

Comments

  • Anonymous
    May 21, 2009
    Hi Alex,are there plans for supporting automatic padding depending on the settings of the database within EF4.0 (Pad()-function like you proposed to do manually)?Woud be greatThanks, Martin
  • Anonymous
    May 21, 2009
    Martin,It is not currently in our plans for EF 4.0, not because we don't think it would be nice, but rather because we think other things are more in demand.Our general rule, is to focus on things for which there isn't an easy work around.Does that make sense?Alex
  • Anonymous
    July 13, 2009
    Hi,you don't have to write the "Pad" function yourself. The String class has already PadRight (as well as PadLeft) method [http://msdn.microsoft.com/en-us/library/36f2hz3a.aspx].
  • Anonymous
    July 13, 2009
    Thanks Jiri!
  • Anonymous
    August 20, 2010
    What if we just set the StoreGeneratedPettern for the key to Computed? Does this fix the problem?
  • Anonymous
    August 25, 2010
    @AlirezaNo that doesn't solve the problem - because it changes the semantics - i.e. I want to specify the key myself not have the database do it.Alex