Error using System.Data.Linq.Mapping and auto incrementing the primary key in a sqlite db

Question

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 table. I've tried this with/without the AUTOINCREMENT

CREATE TABLE [TestTable] ([id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,[title] TEXT  NULL)

My TABLE class:

[Table(Name = "TestTable")]
public class TestTable
{
    [Column(IsPrimaryKey = true, IsDbGenerated =true)]
    public int id { get; set; }

    [Column]
    public string title { get; set; }
}

Here's how I call it. When it goes to submit I get a error, I'll paste the error below this example. One thing to note is if I take out the IsDbGenerated =true above and enter the id manually it does insert fine, but I'd like it to AUTOINCREMENT and for some reason the IsDbGenerated=true is killing the insert. Looking for some guidance.

static void Main(string[] args)
{
    string connectionString = @"DbLinqProvider=Sqlite;Data Source = c:\pathToDB\test.s3db";
    SQLiteConnection connection = new SQLiteConnection(connectionString);
    DataContext db = new DataContext(connection);
    db.Log = new System.IO.StreamWriter(@"c:\pathToDB\mylog.log") { AutoFlush = true };

    var com = db.GetTable<TestTable>();
    com.InsertOnSubmit(new TestTable {title = "asdf2" });
    try {
        db.SubmitChanges();
    }
    catch(SQLiteException e)
    {
        Console.WriteLine(e.Data.ToString());
        Console.WriteLine(e.ErrorCode);
        Console.WriteLine(e.HelpLink);
        Console.WriteLine(e.InnerException);
        Console.WriteLine(e.Message);
        Console.WriteLine(e.StackTrace);
        Console.WriteLine(e.TargetSite);
        Console.WriteLine(e.ToString());
    }
    foreach (var TestTable in com)
    {
        Console.WriteLine("TestTable: {0} {1}", TestTable.id, TestTable.title);
    }
    Console.ReadKey();
}

Error message:

SQL logic error or missing database\r\nnear \"SELECT\": syntax error

Stack trace:

at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)\r\n at System.Data.SQLite.SQLiteCommand.BuildNextCommand()\r\n at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)\r\n at System.Data.SQLite.SQLiteDataReader.NextResult()\r\n at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)\r\n at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)\r\n at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n at System.Data.Common.DbCommand.ExecuteReader()\r\n
at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)\r\n at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)\r\n at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)\r\n at System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicInsert(TrackedObject item)\r\n at System.Data.Linq.ChangeDirector.StandardChangeDirector.Insert(TrackedObject item)\r\n at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)\r\n at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)\r\n at System.Data.Linq.DataContext.SubmitChanges()\r\n at SqlLinq.Program.Main(String[] args) in Program.cs:line 29"

Here is what I'm seeing in the log output:

INSERT INTO [company]([title])
VALUES (@p0)

SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
-- @p0: Input String (Size = 4000; Prec = 0; Scale = 0) [asdf2]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.6.81.0

SELECT [t0].[id], [t0].[title]
FROM [company] AS [t0]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.6.81.0

Answer 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")]
public class TestTable
{
    [Column(IsPrimaryKey = true)]
    public int? id { get; set; }

    [Column]
    public string title { get; set; }
}

The result in SQLite Manager:

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

CREATE TABLE t1(
  a INTEGER PRIMARY KEY,
  b INTEGER
);

With this table, the statement

INSERT INTO t1 VALUES(NULL,123);

is logically equivalent to saying:

INSERT INTO t1 VALUES((SELECT max(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

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