Error Using System.data.linq.mapping And Auto Incrementing The Primary Key In A Sqlite Db

I'm using SQLite and System.Data.Linq.Mapping. I'm having an issue with the id AUTOINCREMENT field when using the linq mapping attribute IsDbGenerated = true. Syntax to create my t

Solution 1:

According to the SQLite documentation (A column declared INTEGER PRIMARY KEY will AUTOINCREMENT.) just remove AUTOINCREMENT in your Table creation, Writing integer primary key is enough. SQLite will automatically increment your ids:

sqlite_cmd.CommandText = "CREATE TABLE [TestTable] ([id] INTEGER PRIMARY KEY NOT NULL , [title] TEXT)";

Also you don't need to set IsDbGenerated = true in your TestTable class and don't enter the id manually, it does insert fine just by inserting title:

com.InsertOnSubmit(new TestTable { title = "asdf2" });//will automatically increment id.

Edit: Your TestTable should look like this now:

[Table(Name = "TestTable")]
publicclassTestTable
{
    [Column(IsPrimaryKey = true)]
    publicint? id { get; set; }

    [Column]
    publicstring title { get; set; }
}

The result in SQLite Manager:

Solution 2:

How do I create an AUTOINCREMENT field

Short answer: A column declared INTEGER PRIMARY KEY will autoincrement.

Longer answer: If you declare a column of a table to be INTEGER PRIMARY KEY, then whenever you insert a NULL into that column of the table, the NULL is automatically converted into an integer which is one greater than the largest value of that column over all other rows in the table, or 1 if the table is empty. Or, if the largest existing integer key 9223372036854775807 is in use then an unused key value is chosen at random. For example, suppose you have a table like this:

CREATETABLE t1(
  a INTEGERPRIMARY KEY,
  b INTEGER
);

With this table, the statement

INSERTINTO t1 VALUES(NULL,123);

is logically equivalent to saying:

INSERTINTO t1 VALUES((SELECTmax(a) FROM t1)+1,123);

There is a function named sqlite3_last_insert_rowid() which will return the integer key for the most recent insert operation.

Note that the integer key is one greater than the largest key that was in the table just prior to the insert. The new key will be unique over all keys currently in the table, but it might overlap with keys that have been previously deleted from the table. To create keys that are unique over the lifetime of the table, add the AUTOINCREMENT keyword to the INTEGER PRIMARY KEY declaration. Then the key chosen will be one more than the largest key that has ever existed in that table. If the largest possible key has previously existed in that table, then the INSERT will fail with an SQLITE_FULL error code.

References :

Autoincrement in SQLite

How to create Autoincrement Field ?

SO post dealing with Autoincrement in SQLite

Solution 3:

SQLLite doesn't work with Linq commands for autoincrement values This command produces error

SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]

You have only 2 way:

1. Do not use Linq for SQLLite. Use some third party solutions, or your own commands.

2. Use another ways to inrement your ID, as it was written by [utility]

The first one better, because there are other examples of SQL statements being passed to sqlite via Linq which aren't valid.