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?

I'm in the same situation. I've tried replacing long for long? in the generated code, so I could at least set the null myself, but it didn't work. The error msg was: "The changes to the database were committed successfully, but an error occurred while updating the object context. The ObjectContext might be in an inconsistent state. Inner exception message: The key-value pairs that define an EntityKey cannot be null or empty.\r\nParameter name: record". It seems theres no way to bypass this, sqlite way of handling autoincrement is kinda wierd. — Pablote, Jun 03 '09 at 02:49
I gave up on SQLite in combination with EF. There is a post about this very issue on the forums at the site for System.Data.Sqlite but there as been no movement or response for some time. I moved to SQL Compact, which presents its own issues. You can't do "server-generated keys" with EF/SQLCompact. So, for now I do standard Connection/Command-style ADO for inserts and I'm using EF for the LINQ sugar. — Dave Swersky, Jun 03 '09 at 16:55
This sucks. I guess the same 'trick' could be used in sqlite. I've tested autoincrement with CE and linq to sql and it seems to work. Haven't tried it with sqlite, not sure if it's even possible. — Pablote, Jun 04 '09 at 16:03
Here's a list of limitations with EF/CE: http://technet.microsoft.com/en-us/library/cc835494.aspx — Pablote, Jun 04 '09 at 16:10
Yeah after thinking about it I could have stayed with SQLite using the same technique... oh well. — Dave Swersky, Jun 04 '09 at 18:34

Pablote · Accepted Answer · 2009-06-04T23:53:34.257

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.

I saw that in some postings, but don't you have to explicitly enter a NULL in the INSERT statement for the primary key? "INSERT INTO Persona VALUES('Pablo')" doesn't work, you have to do "INSERT INTO Persona VALUES(NULL,'Pablo')" right? — Dave Swersky, Jun 04 '09 at 22:53
I've responded on the post, so the code could get nicely formatted. — Pablote, Jun 04 '09 at 23:54

score 13 · Answer 2 · answered Jun 19 '10 at 21:36

13

Hooray... I've found the solution!

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

answered Jun 19 '10 at 21:36

user371251

131
1
2

3

I just did your first step, then deleted and created my EF model. That's it. Actually the StoreGeneratedPattern now got set to "Identity". It's working now. – OneWorld Jan 14 '13 at 16:13
1

I also made it fly by setting the `StoreGeneratedPattern` to "Identity" – Christoph Jun 10 '15 at 18:51

score 5 · Answer 3 · edited Jun 10 '12 at 17:13

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.

edited Jun 10 '12 at 17:13

Bo Persson

90,663
31
146
203

answered Jun 10 '12 at 11:32

Mohammed Fayed

51
1
3

score 3 · Answer 4 · 2009-07-07T15:55:25.427

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

score 1 · Answer 5 · answered Nov 12 '10 at 20:20

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.

score 0 · Answer 6 · answered Jul 24 '17 at 02:16

0

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.

answered Jul 24 '17 at 02:16

CodingYoshi

25,467
4
62
64

SQLite with Entity Framework

6 Answers6

Linked