Error when using System.Data.Linq.Mapping and automatic primary key increment in sqlite db

Question

I am using SQLite and System.Data.Linq.Mapping . I am having a problem with the AUTOINCREMENT id AUTOINCREMENT when using the linq display attribute IsDbGenerated = true .

The syntax for creating my table. I tried this with / without 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; } } 

This is what I call it. When it is sent to submit, I get an error message, I will insert an error below this example. It is worth noting that if I pulled IsDbGenerated =true above and enter id manually, it inserts everything in order, but I would like it to be AUTOINCREMENT , and for some reason IsDbGenerated=true kills 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:

in System.Data.SQLite.SQLite3.Prepare (SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String & strRemain) \ r \ n in System.Data.SQLite.SQLiteCommand.BuildNextCommand () \ r \ n on System. Data.SQLite.SQLiteCommand.GetStatement (index Int32) \ r \ n on 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 in System.Data.Common.DbCommand.ExecuteReader ( ) \ r \ n
in 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 (query expression, QueryInfo [] queryInfos, IObjectReaderFactory factory, Object [] userArguments, ICompiledSubQuery [] subQueries) \ r \ n on System.Data.Linq.SqlClient.SqlProvider.System.Data.ataPataL.D. .IProvider.Execute (Expression request) \ 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 failMode) \ r \ n in System.Data.Linq.DataContext.SubmitChanges (ConflictMode failMode) \ r \ n in System.Data.Linq.DataContext.SubmitChanges () \ r \ n in SqlLinq.Program.Main (String [] args) in Program.cs: page ok 29 "

Here is what I see in the output of the magazine:

 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 ( The column declared by INTEGER PRIMARY KEY will be AUTOINCREMENT. ) Just delete AUTOINCREMENT in your table creation, writing an integer primary key enough. SQLite will automatically increase your ids :

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

Also, you do not need to set IsDbGenerated = true in your TestTable class and do not enter the id manually, it only inserts what you need to insert the title :

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

Edit: Your TestTable should look like this:

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

Result in SQLite Manager:

Answer 2

How to create an AUTOINCREMENT field

The short answer . The column declared by INTEGER PRIMARY KEY will auto-increment.

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

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

Statement in this table

 INSERT INTO t1 VALUES(NULL,123); 

logically equivalent to the above:

 INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123); 

There is a function called sqlite3_last_insert_rowid() that will return an integer key for the most recent insert operation.

Note that the integer key is larger than the largest key that was in the table immediately before insertion. The new key will be unique to all keys that are currently in the table, but may overlap with keys that were previously deleted from the table. To create keys that are unique throughout the life of the table, add the AUTOINCREMENT keyword to the INTEGER PRIMARY KEY declaration. Then the selected key will be one larger than the largest key that has ever existed in this table. If the largest possible key previously existed in this table, then the INSERT will exit with the SQLITE_FULL error code.

References:

Auto-increment in SQLite

How to create an auto-increment field?

SQL post on auto increments in SQLite

Answer 3

SQLLite does not work with Linq commands for auto-increment values. This command causes an error

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

You have only 2 ways:

• Do not use Linq for SQLLite. Use some third-party solutions, or your own teams.

• Use other methods to incorporate your identifier, as it was written [utility]

The first is better because there are other examples of SQL statements passed to sqlite via Linq that are not valid.