Entity Framework StoreGeneratedPattern Bug Persists

I’m using System.Data.SQLite v1.0.66.0 with Entity Framework 4.0 and Visual Studio 2010.  I designed some tables in SQLite and generated an Entity Data Model from those tables.  One of them (we’ll call it Person for the sake of argument) has an autogenerated integer primary key.  I manually set the StoreGeneratedPattern property to Identity in the designer, but when I started creating new rows in the database I encountered a problem.

I create new Person entities with a dummy PersonID of -1.  The first row inserts fine, but instead of generating a new ID it uses the dummy value.  That means when I insert the second row, it fails:

Abort due to constraint violation
PRIMARY KEY must be unique

I did a little Googling and realized I’m not the only person experiencing this:

• EF4: Bug in StoreGeneratedPattern SSDL | geek#
• edmx designer properties does not set StoreGeneratedPattern=”Computed” in SSDL
• StoreGeneratedPattern in Entitiy framework – Stack Overflow

It seems there is a bug in which the StoragePattern property is not updated in the SSDL when you change it in the designer, even though the CSDL is.

The impact of this is that you have to open the .edmx file in the XML editor and manually add the StorageGeneratedPattern property to the definition for your entity in the StorageModels section:

<EntityType Name="Person">
    <Key>
        <PropertyRef Name="PersonID" />
    </Key>
    <Property Name="PersonID" Type="integer" Nullable="false" StoreGeneratedPattern="Identity" />
    <Property Name="Name" Type="nvarchar" Nullable="false" MaxLength="50" />
    <Property Name="Age" Type="integer" Nullable="false" />
</EntityType>

Even worse, you’ll have to do this for every identity column every time you update the model from the database because the property gets clobbered.  The workaround does work, technically, but it’s a major nuisance during development when things change often.

Identity columns are not uncommon, which means that this issue should have been uncovered early during testing.  Why didn’t Microsoft fix it right away?  According to some claims, it’s been around since EF 1.0.  In a couple of weeks I’m starting a new job where they use NHibernate.  It will be interesting to compare it to EF 4.0 and see if I run into the same kind of bugs.  Perhaps it will also help to wean me off graphical designers altogether.