The default behavior of translate boolean on SQLite

[Cologler]

By default, .Where(x => x.BoolProperty) will translate to SQL WHERE BoolProperty, which prevents the SQLite database to use the index.

Using .HasConversion(new BoolToZeroOneConverter<int>()) can help us translate .Where(x => x.BoolProperty) to WHERE 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

[roji]

Can you point to documentation for this, or just post two SQL queries with their varying plans?

[Cologler]

@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

[roji]

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.

Minimal SQLite sample

DROP TABLE IF EXISTS data;
CREATE TABLE data (num INTEGER, bool INTEGER);
CREATE INDEX ix ON data(1) WHERE bool = 1;

INSERT INTO data
SELECT x, x % 10 = 0 FROM (
    WITH RECURSIVE
        cnt(x) AS (
            SELECT 1
            UNION ALL
            SELECT x+1 FROM cnt
            LIMIT 10000000
        )
    SELECT x FROM cnt
);

EXPLAIN QUERY PLAN SELECT * FROM data where bool; -- SCAN TABLE data
EXPLAIN QUERY PLAN SELECT * FROM data where bool = 1; -- SCAN TABLE data USING INDEX ix
EXPLAIN QUERY PLAN SELECT * FROM data where bool <> 1; -- SCAN TABLE data

On PostgreSQL the index is used, no need to add the equality operator.

Minimal PG sample

DROP TABLE IF EXISTS data;
CREATE TABLE data AS SELECT num FROM generate_series(1,10000000) num;
ALTER TABLE data ADD COLUMN bool boolean;
UPDATE data SET bool = num % 10 = 0;
CREATE INDEX ix ON data((1)) WHERE bool;
DROP INDEX ix;

EXPLAIN SELECT * FROM data where bool; -- Index Scan using ix on data
EXPLAIN SELECT * FROM data where bool = true; -- Index Scan using ix on data
EXPLAIN SELECT * FROM data where bool = false; -- Seq scan on data
EXPLAIN SELECT * FROM data where bool <> true; -- Seq scan on data
EXPLAIN SELECT * FROM data where bool <> false; -- Index Scan using ix on data

[maumar]

i was thinking the same thing @roji