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

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 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

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"