Cannot use a SQL Server filtered index on a bit column with value of 0

[ryanjshaw]

Background

It is common to design a table that tracks pending work items with a column IsProcessed, IsPublished, etc. One possible index design for a table design like this is to have a filtered index on IsProcessed = (0) or IsPublished = (0). This allows the application to efficiently retrieve pending work items and keeps the index size to a minimum.

Expected Behaviour

I would expect the following LINQ query:
Where(e => e.IsProcessed == false)

To produce this SQL:
[IsProcessed] = CAST(0 AS bit)

Actual Behaviour

Instead we get this:
[IsProcessed] <> CAST(1 AS bit)

Unfortunately (at least on SQL Server 2016), with the latter SQL the optimiser will not make use of the filtered index and instead perform a full clustered index scan i.e. it does not understand a bit only has 2 values and <> CAST(1 AS bit) is equivalent to = CAST(0 AS bit).

This seems to be a regression because in EF6 the expected SQL was produced, see https://stackoverflow.com/a/23399905

Test (EF Core 5.0.4)

    class Event
    {
        public int Id { get; set; }
        public bool IsProcessed { get; set; }
        public byte[] Payload { get; set; }
    }

    class EventDbContext : DbContext
    {
        public DbSet<Event> Events { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("Data Source=.");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Event>(entityBuilder =>
            {
                entityBuilder.HasKey(e => e.Id);
                
                entityBuilder.Property(e => e.IsProcessed);
                entityBuilder.HasIndex(b => b.IsProcessed).HasFilter("[IsProcessed] = (0)");

                entityBuilder.Property(e => e.Payload);                
            });
        }
    }

    public class BitFilterBugTest
    {
        private EventDbContext _context = new EventDbContext();

        [Fact]
        public void Not_bool_translates_literally()
        {
            var queryString = _context.Events.Where(e => !e.IsProcessed).ToQueryString();
            // This passes as expected
            Assert.Contains("[IsProcessed] <> CAST(1 AS bit)", queryString);
        }

        [Fact]
        public void Bool_equals_false_translates_literally()
        {
            var queryString = _context.Events.Where(e => e.IsProcessed == false).ToQueryString();
            // This does not pass
            Assert.Contains("[IsProcessed] = CAST(0 AS bit)", queryString);
        }
    }

Workaround

Inverting the field's meaning allows a workaround e.g. using IsUnprocessed with a filtered index of IsUnprocessed = (1) and then Where(e => e.IsUnprocessed) will generate SQL that uses the correct index.

[maumar]

dupe of #23472 which has already been fixed