SQLite with Entity Framework

Question

I'm having a problem with primary keys in Entity Framework when using SQLite. SQLite wants an explicit NULL in the VALUES list on an autoincrementing primary key column. I haven't actually looked at the generated SQL in the EF Context, but I believe it's going with the usual SQL Server convention of providing no value for the autoincrementing column.

According to the site for the ADO.NET SQLite provider, EF is fully supported but I'm finding no help there. Is there a way to force EF to explicitly insert a NULL for the primary key value?

Answer 1

It seems EF provider for SQLite does not pick up the column as identity (autoincrement).

For database first, right click the column in EDMX designer and set the StoreGeneratedPattern to Identity.

Answer 2

I've had the same problem with EF and SQLite. Try checking the second post: http://sqlite.phxsoftware.com/forums/p/1418/6162.aspx

The cause for my problem was that the autoincrement was added to the database itself, but the entity model was not properly refreshed. So after the refresh, my field looked something like this:

<Property Name="ID" Type="integer" Nullable="false" StoreGeneratedPattern="Identity" />

(StoreGeneratedPattern="Identity" was added)

Before the refresh (with the old model), I just tried setting the id property to 0, which worked as well :)

Answer 3

From the book, "The Definitive Guide to SQLite" I read the following under primary key constraints:

"In reality, however, this column will simply be an alias for ROWID. They will all refer to the same value."

I believe that is the reason for needing to set AUTOINCREMENT in the column definition.

Answer 4

Well I've finally made this work :D. You need to set the id column as autoincrement, this way it does work with EF. Dont ask me why this isnt mentioned in the question about auto-increment in sqlite faq. This is an example:

create table Persona ( PersonaID integer primary key autoincrement, Nombre text)

Also, I didn't found a way to set this from within visual studio, I had to do it from the command line tool.

UPDATE: The following code works fine.

PruebaDBEntities data = new PruebaDBEntities();

        foreach (int num in Enumerable.Range(1, 1000))
        {
            Persona p = new Persona() { Nombre = "Persona " + num, Edad = num };

            data.AddToPersona(p);

            data.SaveChanges();

            Console.WriteLine(p.PersonaID);
        }

The PersonaID wasn't set, and after the save operation it had the value asigned by sqlite.

Answer 5

You must set autoincrement to True. You can make this directly from VS by clicking ( Manage Indexes and Keys ) icon from the tool bar while you are in design table window, then update your Model.

Answer 6

Hooray... I've found the solution!

1. Declare the ID column as INTEGER PRIMARY KEY AUTOINCREMENT in your create table statement. INTEGER PRIMARY KEY won't work!
2. Update your Entity Framework Model in Visual Studio, set the ID column property StoreGeneratedPattern to "Computed"