I tried to use System.Data.SQLite together with LINQ (LINQ-to-SQL that is). Basically, I had the following classes:
class MsgDatabase : DataContext
public MsgDatabase(IDbConnection connection) : base(connection)
public readonly Table<Msg> Msg = null;
[Column(IsPrimaryKey = true)] public long Ticks;
[Column] public byte Payload;
Then, I connected to the database by
var db = new MsgDatabase(new SQLiteConnection(...));
Selecting from the Msg table like
var foo = from m in db.Msg where m.Ticks > 10048;
just worked fine.
But OrderBy together with First lead to invalid SQL code:
var bar = db.Msg.OrderBy(m => m.Ticks).First();
The generated code contained select top (1), which is not understood by SQLite. It should be limit 1 instead.
So, finally here is my question: there seems to be a SQLite-code generator in the namespace Data.System.SQLite.Linq which obviously is not used in the setting I sketched (but the T-SQL code generator from .net). Is my understanding correct that the latter code generator is only in action when Entity Framework is used or is there any way to use it also in a plain LINQ-to-SQL setting?