System.Data.SQLite

Summary: It seems the PK value may not be advancing on EF .SaveChanges()

I'm using Data.SQLLite (just downloaded today (5/Feb/14)) with VisStudio 2012 &
An Entity Framework (EF) 4 model.

I have a table in my SQLite DB, also created today, with SQlite V3.80.3


CREATE TABLE IF NOT EXISTS Widget
(
  Id            INTEGER         NOT NULL    PRIMARY KEY,
  DateCreated	DATETIME	NOT NULL    DEFAULT( DATETIME() ),
  Code          TEXT(   5 ) 	NULL,
  Fullname      TEXT( 100 )	NOT NULL
)

& hence an EF Table called 'Widget' & EntitySet called 'Widgets'

I declare a new one, & initialise it:

       var widget =  new Widget();
       widget.Fullname    = 'Some name';
       widget.Code        = 'W1';                
       widget.DateCreated = DateTime.Now;
       _entities.AddToWidgets( widget );

if I inspect widget with QuickWatch, Id is currently 0, as expected.

When I run:

      _entities.SaveChanges();

it throws with:

  An error occurred while updating the entries. See the inner exception for details. 
     Inner: constraint failed  UNIQUE constraint failed: Widgets.Id
 
as yes, there is 1 row in the Widget table, with Id = 0

Does Data.Sqlite handle advancing the PRIMARY KEY value?

Thanks!

Craig

I think the "Id" column should be PRIMARY KEY AUTOINCREMENT.  Please try that.

Hi

I thought "AUTOINCREMENT" wasn't required as INSERT statments not specifying an Id from within sqlite were working fine.

eg: INSERT INTO WIDGET( Code, Fullname ) VALUES ( 'W10', 'Widget #10' ) 

But now I see it makes sense as the value it has on insert is 0, not NULL, because
thats what it got when I had:

     widget = new Widget();

I've added AUTOINCREMENT & all seems OK now. 

Thanks.

Craig