EF4: Bug in StoreGeneratedPattern SSDL

Recently at work, I’ve had the pleasure of working on a new project written in ASP.NET MVC2 under .NET 4.0.  Our team has decided to use Entity Framework 4 for data access, and for the most part, we’ve been very pleased with this decision.  One of the things I tend to make use of pretty regularly is default values for columns in SQL server.  In doing this, I’ve encountered a pretty nasty bug in EF4.  Here’s the deal:

I have several tables in my application where I add some basic auditing columns, such as CreateDate, CreatedBy, LastUpdatedDate, and LastUpdatedBy.  These columns are pretty self explanatory, and I prefer to use the SQL GETDATE() function as the default value for the date columns.  This way I don’t have to worry about setting these dates when a new row is inserted, because SQL will handle it for me.

The first thing you have to do is tell EF4 that these columns are computed by SQL server.  The way you do this is by opening the Entity Data Model (EDM) and finding the matching columns in each entity.  Once you select a column, check out the Properties pane for the column.  You should see something like this:

Notice that I’ve highlighted the StoreGeneratedPattern property in this image.  This property tells EF4 how to handle the default value of this column.  More specifically, toggling this property to Computed will tell EF4 that you want the database to handle initializing this property for you.  If you don’t set this properly, EF4 will attempt to insert the default value for whatever data type the column is.  In this case, in .NET 4.0, the DateTime default is DateTime.MinValue, which is “1/1/1 00:00:00.000.”  If you try to insert this into a regular SQL datetime field, you’ll generate an exception because SQL’s regular datetime type cannot handle a date that far in the past.

One would think that simply setting StoreGeneratedPattern to Computed would fix this problem (and it actually should), however there’s a nice bug in the EDM designer that prevents this from working as intended.  Basically, EF4 EDM’s are broken into a few areas.  You have the SSDL, which is how the EDM describes your database objects and their relationships (think storage models).  Then there is the CSDL which is how your entities themselves are defined (think classes).  Finally there’s the mapping layer which acts as the man in between the SSDL and CSDL.  It’s in charge of mapping the storage models in the SSDL with the entity models in the CSDL.

So what about this bug?  Well, the problem is that when you change the StoreGeneratedPattern property in the EDM designer, only the CSDL is updated.  The SSDL is left in the dark about this change, and because of that, you will continue to experience the out of range exceptions generated above.  There is a way, however, to fix this problem if you aren’t afraid of cracking open an text editor.

First you need to open your *.edmx file in any text editor.  I prefer to use one with XML syntax highlighting just because it makes things easier to find, and it helps me avoid typos.  Right at the top of the file, you should see the SSDL defined.  First you will see the main tables defined, followed by any relationships.  Finally you will start to see your tables defined as storage models like this:

<EntityType Name="tblAnswerDefinitions">
  <Key>
    <PropertyRef Name="AnswerDefinitionID" />
  </Key>
  <Property Name="AnswerDefinitionID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
  <Property Name="QuestionID" Type="int" Nullable="false" />
  <Property Name="AnswerTypeID" Type="int" Nullable="false" />
  <Property Name="Value" Type="varchar" Nullable="false" MaxLength="50" />
  <Property Name="Points" Type="int" Nullable="false" />
  <Property Name="IsDeleted" Type="bit" Nullable="false" />
  <Property Name="CreatedBy" Type="int" Nullable="false" />
  <Property Name="CreatedDate" Type="datetime" Nullable="false" />
  <Property Name="LastUpdatedBy" Type="int" Nullable="false" />
  <Property Name="LastUpdatedDate" Type="datetime" Nullable="false" />
</EntityType>

Notice that the CreatedDate and LastUpdatedDate columns don’t have the StoreGeneratedPattern property set, even though we’ve defined them in the EDM designer!  The fix is to simply change the lines and add the property.  When you’re done, it will look like this:

<EntityType Name="tblAnswerDefinitions">
  <Key>
    <PropertyRef Name="AnswerDefinitionID" />
  </Key>
  <Property Name="AnswerDefinitionID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
  <Property Name="QuestionID" Type="int" Nullable="false" />
  <Property Name="AnswerTypeID" Type="int" Nullable="false" />
  <Property Name="Value" Type="varchar" Nullable="false" MaxLength="50" />
  <Property Name="Points" Type="int" Nullable="false" />
  <Property Name="IsDeleted" Type="bit" Nullable="false" />
  <Property Name="CreatedBy" Type="int" Nullable="false" />
  <Property Name="CreatedDate" Type="datetime" Nullable="false" StoreGeneratedPattern="Computed" />
  <Property Name="LastUpdatedBy" Type="int" Nullable="false" />
  <Property Name="LastUpdatedDate" Type="datetime" Nullable="false" StoreGeneratedPattern="Computed" />
</EntityType>

The final step is to save the EDMX file, and then open it in Visual Studio.  Make a minor change (like moving the position of one of the entities in the design view), and then save it.  Once the file is saved, the code generator will re-run, and the problem should be solved.  One other pretty annoying thing I’ve noticed is that if you ever have to set another field’s StoreGeneratedPattern property, you actually have to repeat this entire process for all fields, because the designer will wipe out your manual changes.  There is a Microsoft Connect ticket open on this issue, but it has been closed as fixed.  It’s most definitely not fixed, so it could use your attention!  Cheers!