entity-frameworkentity-framework-coreef-core-2.0

How can I stop EF Core from creating a filtered index on a nullable column


I have this model:

public class Subject
{
    public int Id { get; set; }

    [Required]
    [StringLength(50)]
    public string Name { get; set; }

    public int LevelId { get; set; }

    [ForeignKey("LevelId")]
    public Level Level { get; set; }

    [Column(TypeName = "datetime2")]
    public DateTime? DeletedAt { get; set; }
}

And index configured via Fluent API:

entityBuilder.HasIndex(e => new { e.LevelId, e.Name, e.DeletedAt })
    .IsUnique();

It's creating a table with a unique filtered index. How can I prevent EF from adding the filter? I just want the index and don't want it filtered.


Solution

  • Creating filtered index excluding NULL values is the default EF Core behavior for unique indexes containing nullable columns.

    You can use HasFilter fluent API to change the filter condition or turn it off by passing null as sql argument:

    entityBuilder.HasIndex(e => new { e.LevelId, e.Name, e.DeletedAt })
        .IsUnique()
        .HasFilter(null);