Open Cologler opened 1 year ago
Can you point to documentation for this, or just post two SQL queries with their varying plans?
@roji https://www.sqlite.org/optoverview.html show all usable operators for using indexes.
I also use
// See https://aka.ms/new-console-template for more information
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;
new ExampleDbContext().Database.EnsureCreated();
Console.WriteLine("Hello, World!");
class ExampleDbContext : DbContext
{
public ExampleDbContext(): base() { }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlite("Data Source=test-for-index.sqlite3");
}
public DbSet<ExampleObject> ExampleObjects { get; set; }
}
[Index(nameof(BoolProperty))]
record ExampleObject
{
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
public bool BoolProperty { get; set; }
}
to generate the database, and test with:
❯ sqlite3 test-for-index.sqlite3
SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite> EXPLAIN QUERY PLAN SELECT * FROM ExampleObjects WHERE BoolProperty;
QUERY PLAN
`--SCAN ExampleObjects
sqlite> EXPLAIN QUERY PLAN SELECT * FROM ExampleObjects WHERE BoolProperty = 1;
QUERY PLAN
`--SEARCH ExampleObjects USING COVERING INDEX IX_ExampleObjects_BoolProperty (BoolProperty=?)
sqlite> EXPLAIN QUERY PLAN SELECT * FROM ExampleObjects WHERE BoolProperty <> 1;
QUERY PLAN
`--SCAN ExampleObjects
Thanks, I can indeed see this happening. We should do search condition manipulation to add the equality operator like how we do it on SQL Server, /cc @maumar.
On PostgreSQL the index is used, no need to add the equality operator.
i was thinking the same thing @roji
By default,
.Where(x => x.BoolProperty)
will translate to SQLWHERE BoolProperty
, which prevents the SQLite database to use the index.Using
.HasConversion(new BoolToZeroOneConverter<int>())
can help us translate.Where(x => x.BoolProperty)
toWHERE BoolProperty = 1
, and it will use index correct;Is there any document for this? Or just make this the default behavior.
Include provider and version information
EF Core version: 7.0.5 Database provider: Microsoft.EntityFrameworkCore.Sqlite Target framework: .NET 7.0