Using MicroLite on SQLite

Question

I set up a SQLite db with the same schema as my existing SQL server db and noted the following...

1. SQLite field names (and presumably everything else) are case sensitive.
2. MicroLite's SqlBuilder appears to insert the prefix 'dbo.' before the table name, which SQLite doesn't like...

This query works...

query = new SqlQuery("SELECT [ClubID], [Name] FROM [Clubs] WHERE [ClubID] = @p0", 3);

clubs = session.Fetch<MicroLiteClub>(query);

This one doesn't...

query = SqlBuilder.Select("*")
                  .From(typeof(MicroLiteClub))
                  .Where("ClubID = @p0", 3)
                  .OrWhere("ClubID = @p1", 22)
                  .OrderByDescending("Name")
                  .ToSqlQuery();

clubs = session.Fetch<MicroLiteClub>(query);

MicroLite logged: "no such table: dbo.Clubs"

Answer 1

This is happening because SQLite doesn't support table schemas like MS SQL Server does.

In the hand crafted query, you are not specifying a schame for the table FROM [Clubs] however in your mapping attribute you will have specified dbo as the schema like this:

[Table(schema: "dbo", name: "Clubs")]

The SqlBuilder doesn't know what SQL Dialect is in use so if a schame is present on the table mapping, it will be used. This means that it would generate FROM [dbo].[Clubs]. To rectify this, simply remove the schema value on the TableAttribute as is optional from MicroLite 2.1 onwards.

On a side note, MicroLite 2.1 introduced support for In in the SqlBuilder fluent API so you could change:

.Where("ClubID = @p0", 3)
.OrWhere("ClubID = @p1", 22)

to

.Where("ClubID").In(3, 22)