SQL Server migration SQL is incorrect for INDEX creation where OPTIONS are present along with a "legacy filter"

[reskusic]

During migration SQL generation for SQL Server the following test (which can be added to SqlServerMigrationSqlGeneratorTest.cs) will unexpectedly fail:

        [ConditionalFact]
        public void CreateIndexOperation_unique_online()
        {
            Generate(
                new CreateIndexOperation
                {
                    Name = "IX_People_Name",
                    Table = "People",
                    Schema = "dbo",
                    Columns = new[] { "FirstName", "LastName" },
                    IsUnique = true,
                    [SqlServerAnnotationNames.CreatedOnline] = true
                });

            AssertSql(
                @"CREATE UNIQUE INDEX [IX_People_Name] ON [dbo].[People] ([FirstName], [LastName]) WHERE [FirstName] IS NOT NULL AND [LastName] IS NOT NULL WITH (ONLINE = ON);
");
        }

The actual generated SQL is as follows:

CREATE UNIQUE INDEX [IX_People_Name] ON [dbo].[People] ([FirstName], [LastName]) WITH (ONLINE = ON) WHERE [FirstName] IS NOT NULL AND [LastName] IS NOT NULL;

This seems to be invalid TSQL, with the WITH and WHERE clauses out of order.

This was found in EF Core 3.1, but the migration generating code seems to still have the issue in "main".

[roji]

Thanks, submitted #24643 as a fix. As noted, this only occurs with "legacy index filters", so only with migrations from before EF Core 2.0.0.

[ErikEJ]

Is EF Core 1 supported?

[roji]

@ErikEJ no, but this is about migrations which were generated in pre-2.0, and which could still be in use in new versions (so not about fixing a runtime issue in pre-2.0). Maybe a bit far-fetched, but...

[reskusic]

Thanks for the quick turnaround on this issue!