lundi 27 juillet 2015

Error using System.Data.Linq.Mapping and IsDbGenerated with sqlite

I'm using sqlite and System.Data.Linq.Mapping. I'm having a 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 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

Aucun commentaire:

Enregistrer un commentaire