Inserting into an Sqlite database with LINQ in Mono throws TargetInvocationException and SqliteException

Question

I'm trying to open a Sqlite file and insert a row into a table with Mono. I've referenced the Mono.Data.Sqlite, System.Data, and System.Data.Linq assemblies. Both the Sqlite file and the table exist. The Status class has Table and Column attributes. When I use Mono's Sqlite library alone, I can successfully insert.

Status data = GetStatusFoo();
using (SqliteConnection con = new SqliteConnection("Data Source=" + Config.SQLITE_DB_FILE + ";Version=3;"))
{
    con.Open();

    using (SqliteCommand cmd = new SqliteCommand("INSERT INTO " + _table + _insert + " VALUES " + _values, con))
    {
        cmd.Parameters.Add(new SqliteParameter("@Timestamp", data.Timestamp));
        // ...
        cmd.ExecuteNonQuery();
    }
    con.Close()
}

However, I don't want to type out 1000 SqliteParameters, so I'm trying to use Linq-to-SQL. The following code throws a TargetInvocationException in the DataContext constructor.

Status data = GetStatusFoo();
using (DataContext db = new DataContext("Data Source=" + Config.SQLITE_DB_FILE + ";Version=3;"))
{
    Table<Status> statuses = db.GetTable<Status>();
    statuses.InsertOnSubmit(data);
    db.SubmitChanges();
}

It complains that Version isn't a valid keyword, so when I remove it, leaving just

Status data = GetStatusFoo();
using (DataContext db = new DataContext("Data Source=" + Config.SQLITE_DB_FILE + ";")
{
    Table<Status> statuses = db.GetTable<Status>();
    statuses.InsertOnSubmit(data);
    db.SubmitChanges();
}

it throws a System.Data.SqlClient.SqlException at db.SubmitChanges() that the "Server does not exist or connection refused".

However, this changes when I use a SqliteConnection for DataContext's IDbConnection, i.e.

Status data = GetStatusFoo();
using (SqliteConnection con = new SqliteConnection("Data Source=" + Config.SQLITE_DB_FILE + ";"))
using (DataContext db = new DataContext(con))
{
    Table<Status> statuses = db.GetTable<Status>();
    statuses.InsertOnSubmit(data);
    db.SubmitChanges();
}

This throws a Mono.Data.Sqlite.SqliteException: SQLite error unrecognized token: "@" at db.SubmitChanges();.

Adding the Version=3; back doesn't help, and trying Version=2 throws a System.NotSupportedException. The Console and DataContext.Log give something like this:

INSERT INTO [Status] ([Timestamp], ...) VALUES (@Timestamp, ...)
-- @Timestamp: Input Int64 (Size = 0; Prec = 0; Scale = 0) [1397710927]
-- ...
-- Context: SqlServer Model: AttributedMetaModel Build: 4.0.0.0
SELECT @@IDENTITY
-- @Timestamp: Input Int64 (Size = 0; Prec = 0; Scale = 0) [1397710927]
-- Context: SqlServer Model: AttributedMetaModel Build: 4.0.0.0

Unhandled Exception:
Mono.Data.Sqlite.SqliteException: SQLite error
unrecognized token: "@"

What gives? Why doesn't it like the "@" symbol? Is it failing to do Parameter substitution? Or is it the @@IDENTITY call at the end?

EDIT: Additional Info

After poking around the stack trace, the SqliteException exception occurs in Mono.Data.SqliteCommand.BuildNextCommand, and this.CommandText == "SELECT @@IDENTITY". Apparently this MS/Transact-SQL command isn't supported by SQLite. From what I can tell, an "outputCommand" for this is created in DbLinq.Data.Linq.Sugar.Implementation.QueryRunner.Upsert (target={Status}, insertQuery={DbLinq.Data.Linq.Sugar.UpsertQuery})

So I'm altering my question to include: how do I tell Linq to avoid SQL-only commands?

Answer 1

I have a partial answer to my question.

Adding DbLinqProvider=sqlite; to the SqliteConnection query string tells Linq-to-SQL to use SQLite commands, and fixes the "SELECT @@IDENTITY" issue. This code works:

Status data = GetStatusFoo();
using (SqliteConnection con = new SqliteConnection("Data Source=" + Config.SQLITE_DB_FILE + ";DbLinqProvider=sqlite;"))
using (DataContext db = new DataContext(con))
{
    Table<Status> statuses = db.GetTable<Status>();
    statuses.InsertOnSubmit(data);
    db.SubmitChanges();
}

However, I still have to use a SqliteConnection object, because new DataContext("Data Source=" + Config.SQLITE_DB_FILE + ";DbLinqProvider=sqlite;") still throws the "Server does not exist or connection refused" exception.

Edit: Complete answer

It turns out that without a DbLinqConnectionType parameter, the DataContext defaults to using System.Data.SqlClient.SqlConnection for its connection. The connection type has to be an assembly fully qualified name, so the following code works for me:

// in Config
public static string SQLITE_CONNECTION_CLASS_AQN = typeof(Mono.Data.Sqlite.SqliteConnection).AssemblyQualifiedName


// in database class
static string _connection_string = "Data Source=" + Config.SQLITE_DB_FILE + ";DbLinqProvider=sqlite;DbLinqConnectionType=" + Config.SQLITE_CONNECTION_CLASS_AQN + ";";


// in database method
Status data = GetStatusFoo();
using (DataContext db = new DataContext(_connection_string))
{
    Table<Status> statuses = db.GetTable<Status>();
    statuses.InsertOnSubmit(data);
    db.SubmitChanges();
}

For reference, typeof(Mono.Data.Sqlite.SqliteConnection).AssemblyQualifiedName evaluates to "Mono.Data.Sqlite.SqliteConnection, Mono.Data.Sqlite, Version=4.0.0.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756".