EF Core : creating multiple filtered indexes on the same column

Is there a reason that EF Core will not allow you to create multiple indexes on the same column?

    builder
      .ForSqlServerHasIndex(x => x.ColumnX)
      .ForSqlServerInclude(nameof(TableA.ColumnA), nameof(TableA.ColumnB))
      .HasFilter($"{nameof(TableA.ColumnX)} = 1 AND {nameof(TableA.IsDeleted)} = 0")
      .HasName($"IX_{nameof(TableA)}_{nameof(TableA.ColumnX)}_Filter_1}");

    builder
      .ForSqlServerHasIndex(x => x.ColumnX)
      .ForSqlServerInclude(nameof(TableA.ColumnA), nameof(TableA.ColumnB))
      .HasFilter($"{nameof(TableA.ColumnX)} = 0 AND {nameof(TableA.IsDeleted)} = 0")
      .HasName($"IX_{nameof(TableA)}_{nameof(TableA.ColumnX)}_Filter_0}");

Above is just a sample of what I am attempting to do. EF Core won't generate two indexes. Instead it just generates an index for the first occurrence, I then have to manually edit the migration scripts to get the second index.

It is not as if SQL complains about it?

Solution

I have a very similar requirement to yours - two indexes on the same property, but with different filters. I've just found out the hard way that Entity Framework Core uses the indexed properties as the identifier for the index. This is confirmed by the documentation on creating indexes:

EF Core only supports one index per distinct set of properties. If you use the Fluent API to configure an index on a set of properties that already has an index defined, either by convention or previous configuration, then you will be changing the definition of that index. This is useful if you want to further configure an index that was created by convention.

It is possible to bypass this behaviour, by manually adding the first index back into the model snapshot, the migration, and the migration's snapshot. This was the route that we took, and everything is working as expected. However, every time you generate a subsequent migration, it removes the first index, and we have to keep adding it back manually. It would be nice if we could use the name as the unique identifier for the index instead of using the list of indexed properties.

As an aside, in the example you posted, you could use the IN() operator and create the following single index to cover both scenarios:

builder
    .ForSqlServerHasIndex(x => x.ColumnX)
    .ForSqlServerInclude(nameof(TableA.ColumnA), nameof(TableA.ColumnB))
    .HasFilter($"{nameof(TableA.ColumnX)} IN (1, 2) AND {nameof(TableA.IsDeleted)} = 0");

UPDATE - 17 Nov 2021

This issue has been resolved in EntityFramework 5.0. From the documentation:

Multiple indexes are now allowed on the same set or properties. These indexes are now distinguished by a name in the model. By convention, the model name is used as the database name; however it can also be configured independently using HasDatabaseName.