In Entity Framework Core 9.0.9, the query builder fails with SQLite Syntax
builder.Services.AddDbContext<MyDbContext>(fun options ->
options.UseSqlite(connectionString) |> ignore
It generates this SELECT syntax:
FETCH FIRST 10 ROWS ONLY
and
OFFSET 10 ROWS
correct by ANSI/ISO SQL:2008
but in SQLite it causes an error:
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'near "FETCH": syntax error'.
FETCH FIRST … ROWS ONLY → DB2, Oracle 12c+, PostgreSQL.
OFFSET … ROWS → SQL Server (2012+), PostgreSQL, Oracle 12c+.
OFFSET … ROWS FETCH NEXT … ROWS ONLY; Microsoft SQL Server.
In SQLite it must be
LIMIT 10
LIMIT -1 OFFSET 50
LIMIT 10 OFFSET 50
My work-around in F#
type MyQuerySqlGenerator(deps: QuerySqlGeneratorDependencies) =
inherit QuerySqlGenerator(deps)
override this.GenerateLimitOffset (x: SelectExpression): unit =
// Default SQLite: LIMIT <fetch> OFFSET <offset>
// Custom: e.g., only LIMIT, or wrap in subquery
//.Take(10) → LIMIT 10
//.Skip(50) → LIMIT -1 OFFSET 50
//.Skip(50).Take(1) → LIMIT 10 OFFSET 50
match x.Limit with
| null ->
if x.Offset = null then
base.GenerateLimitOffset(x) // no Take
else
this.Sql.Append(" LIMIT -1 OFFSET ") |> ignore
this.Visit(x.Offset) |> ignore
| fetchExpr ->
this.Sql.Append(" LIMIT ") |> ignore
this.Visit(fetchExpr) |> ignore
if x.Offset <> null then
this.Sql.Append(" OFFSET ") |> ignore
this.Visit(x.Offset) |> ignore
type MyQuerySqlGeneratorFactory(deps: QuerySqlGeneratorDependencies) =
interface IQuerySqlGeneratorFactory with
member _.Create() =
upcast new MyQuerySqlGenerator(deps)
type MyDbContext(options: DbContextOptions<ReadDbContext>) =
inherit DbContext(options)
override _.OnConfiguring(optionsBuilder: DbContextOptionsBuilder) =
optionsBuilder
.ReplaceService<IQuerySqlGeneratorFactory, MyQuerySqlGeneratorFactory>()
|> ignore
My code has alread customized MyQuerySqlGenerator and I used "patch". Is there some other more elegant solution?
UseSqlitethen why is there PostgreSQL in the exception message (and is that still the exception message)?Microsoft.EntityFrameworkCore.Sqliteshould generate the correct syntax.