Migrations: Avoid redundant IS NOT NULL on indexes

[Tasteful]

Steps to reproduce

Create an entity with string property that got an unique-index. Field is marked as required.

builder.Entity<UnitOfMeasurementEntity>(b =>
            {
                b.ToTable("UnitOfMeasurementEntity", "Products");
                b.HasKey(p => p.SystemId);

                b.Property(p => p.Id)
                    .HasMaxLength(100)
                    .IsRequired();
                b.HasIndex(p => p.Id).IsUnique();
            });

The issue

Index is created on non null column, index is still created as a filtered index with an where statement. Somehow the filtered index is not used during all querying and got slow response time from SQL server (SQL 2012r2).

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20160526125022_FixBrokenIndex2')
BEGIN
    CREATE UNIQUE INDEX [IX_UnitOfMeasurementEntity_Id] ON [Products].[UnitOfMeasurementEntity] ([Id]) WHERE [Id] IS NOT NULL;
END

Migration code looks like the following

            migrationBuilder.CreateIndex(
                name: "IX_UnitOfMeasurementEntity_Id",
                schema: "Products",
                table: "UnitOfMeasurementEntity",
                column: "Id",
                unique: true);

The column for the index is non nullable and then the index does not need any filter.

Further technical details

EF Core version: RC1
Operating system: Win10
Visual Studio version: VS2015 RC2

[MarcoLoetscher]

For indexes on SQL Server, it should be possible to set a filter might as string or lamda expression:
modelBuilder.Entity<Blog>() .HasIndex(b => b.Url, "[Url] IS NOT NULL AND [IsActive] = 1");
or
modelBuilder.Entity<Blog>() .HasIndex(b => b.Url, b => b.Url != null && b.IsActive == 1);

[bricelam]

@MarcoLoetscher Can you create a new issue? This one is specifically about removing WHERE...NOT NULL on NOT NULL columns.

[MarcoLoetscher]

Yes, I can. #5817