Using 'Auto-Sync' feature of DBML with SQLite

I'm having trouble trying to use the 'Auto-Sync' feature of DBML with SQLite. I have a class in my data model that contains a primary key (id). This key is defined as "INTEGER PRIMARY KEY", which SQLite maps to the rowid of the row. To support this, I have set "Auto Generated Value" to 'True' and "Auto-Sync" to 'OnInsert'.

The problem is, when I commit a new entry to the database, the SELECT string used by the LINQ to SQL classes is not supported by SQLite:

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

Instead, SQLite has the last_insert_rowid() function, which I cannot seem to point to.

Does anyone know how to remedy this? Possibly by changing the statement used to select the last row ID or 开发者_如何学编程some other trick I'm missing?

EDIT There appears to be some traffic on the provider's website, but no resolutions.

EDIT Since I seem to have confused the question, here's how I've set up my application. Hopefully it helps shed some light on my thought process, and maybe an underlying issue.

• Add new "LINQ to SQL Classes" file to my solution
• Model my database in the designer, named DataModel
• Open a database using a System.Data.SQLite.SQLiteConnection
• Initialize the DataModel instance using this connection

You are adding a "Linq-to-SQL" data model to your project, but you're using it against SQLite - that'll never work of course! Linq-to-SQL only ever supports SQL Server (always has, always will), and thus its SQL statements that it generates are SQL Server T-SQL Statements - nothing else.

If you want to use the Entity Framework with SQLite, you need to use "ADO.NET Entity Data Model" (file with the .EDMX extension) as your data model.

Only that will support third-party database drivers like SQLite and others!

Devart implementation of LINQ to SQLite does not contain this problem. The autoincrememnt column is treated correctly.

I ran into the same issue and found the following workaround - use the SQLiteConnection.Changed event to intercept the query and fix it up:

SQLiteConnection.Changed += (obj, eventArgs) => {
    var cmd = eventArgs.Command;
    if (eventArgs.EventType == SQLiteConnectionEventType.NewDataReader && cmd != null)
    {
        cmd.CommandText = cmd.CommandText.Replace( @"SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]"
                                                    , @"; SELECT last_insert_rowid() AS value");
    }
};