October 2010 – The Public Void

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:

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.

While working on my blog engine project to learn ASP.NET MVC and Entity Framework, I encountered a common scenario. I’m using SQLite on the back-end, and I needed a unique constraint across two columns. As demonstrated in this stackoverflow question, you can specify a unique table constraint immediately after the column definitions when using the CREATE TABLE command. However, for the purpose of rapid prototyping, I prefer using SQLite.NET and the graphical Server Explorer interface in Visual Studio rather than explicit DDL to define my tables.

The problem is that the GUI-based designer is still in development and doesn’t support unique table constraints. One solution is to create a unique index instead. Right-click one of the row headers in the table designer and select Indexes/Keys…

In the Index Editor, add a new index. Under the Misc category, set the Unique property to True, and under the Source category, list the columns you want in the index, separated by commas.

This works because, as stated in the SQLite documentation:

INTEGER PRIMARY KEY columns aside, both UNIQUE and PRIMARY KEY constraints are implemented by creating an index in the database (in the same way as a “CREATE UNIQUE INDEX” statement would).

Using the Index Editor to create an index with Unique = True generates a “CREATE UNIQUE INDEX” statement behind the scenes, which can be verified by using the Generate Change Script… feature.

In summary, if you’re thinking about using Server Explorer to create a multi-column unique constraint in SQLite, just go with a unique index, since that’s how it’s implemented anyway.

Month: October 2010

Entity Framework StoreGeneratedPattern Bug Persists

Multi-Column Unique Constraints in SQLite.NET