SQL Server uniquifies index names globally, when only per-table is needed

[fuzzykiller]

Description

When adding (via migration) a new index with the same explicit name as an existing index, indexes get needlessly renamed. Most of the time, the existing index gets renamed to Existing_Index1. Index names are per-table in SQL Server. There is no need to rename the new index, much less the existing one. This may be different on other database engines, like SQLite.

The existing logic, which is probably somewhere in the core, could move to the database adapter. Alternatively, the user could be made to handle this conflict.

Code

https://github.com/fuzzykiller/ef-core-migrations-index (The code is in a state where dotnet ef migrations add AddHourIndex can be run to demonstrate the behavior.)

The relevant change, adding an index with the same name on a different entity:

using System.ComponentModel.DataAnnotations;
using Microsoft.EntityFrameworkCore;

namespace efcoretest.Model;

[Index(nameof(Name), IsUnique = true, Name = "IX_Name_Unique")] // <------- This line is new
public class Hour
{
    [Key] public int Hour_Id { get; set; }

    public string Name { get; set; }
}

Then running dotnet ef migrations add AddHourIndex results in the existing index IX_Name_Unique getting renamed to IX_Name_Unique1.

Verbose output

PS C:\SRC\efcoretest> dotnet ef migrations add --verbose AddHourIndex
Using project 'C:\SRC\efcoretest\efcoretest.csproj'.
Using startup project 'C:\SRC\efcoretest\efcoretest.csproj'.
Writing 'C:\SRC\efcoretest\obj\efcoretest.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\D.Betz\AppData\Local\Temp\tmp7F6.tmp /verbosity:quiet /nologo C:\SRC\efcoretest\efcoretest.csproj
Writing 'C:\SRC\efcoretest\obj\efcoretest.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\D.Betz\AppData\Local\Temp\tmp97E.tmp /verbosity:quiet /nologo C:\SRC\efcoretest\efcoretest.csproj
Build started...
dotnet build C:\SRC\efcoretest\efcoretest.csproj /verbosity:quiet /nologo
C:\SRC\efcoretest\Model\Minute.cs(12,19): warning CS8618: Non-nullable property 'Name' must contain a non-null value when exiting constructor. Consider declaring the property as nullable. [C:\SRC\efcoretest\efcoretest.csproj]
C:\SRC\efcoretest\Model\Hour.cs(11,19): warning CS8618: Non-nullable property 'Name' must contain a non-null value when exiting constructor. Consider declaring the property as nullable. [C:\SRC\efcoretest\efcoretest.csproj]

Build succeeded.

C:\SRC\efcoretest\Model\Minute.cs(12,19): warning CS8618: Non-nullable property 'Name' must contain a non-null value when exiting constructor. Consider declaring the property as nullable. [C:\SRC\efcoretest\efcoretest.csproj]
C:\SRC\efcoretest\Model\Hour.cs(11,19): warning CS8618: Non-nullable property 'Name' must contain a non-null value when exiting constructor. Consider declaring the property as nullable. [C:\SRC\efcoretest\efcoretest.csproj]
    2 Warning(s)
    0 Error(s)

Time Elapsed 00:00:01.02
Build succeeded.
dotnet exec --depsfile C:\SRC\efcoretest\bin\Debug\net6.0\efcoretest.deps.json --additionalprobingpath C:\Users\D.Betz\.nuget\packages --additionalprobingpath "C:\Program Files (x86)\Microsoft Visual Studio\Shared\NuGetPackages" --runtimeconfig C:\SRC\efcoretest\bin\Debug\net6.0\efcoretest.runtimeconfig.json C:\Users\D.Betz\.dotnet\tools\.store\dotnet-ef\
6.0.3\dotnet-ef\6.0.3\tools\netcoreapp3.1\any\tools\netcoreapp2.0\any\ef.dll migrations add AddHourIndex --assembly C:\SRC\efcoretest\bin\Debug\net6.0\efcoretest.dll --project C:\SRC\efcoretest\efcoretest.csproj --startup-assembly C:\SRC\efcoretest\bin\Debug\net6.0\efcoretest.dll --startup-project C:\SRC\efcoretest\efcoretest.csproj --project-dir C:\SRC\e
fcoretest\ --root-namespace efcoretest --language C# --framework net6.0 --nullable --working-dir C:\SRC\efcoretest --verbose
Using assembly 'efcoretest'.
Using startup assembly 'efcoretest'.
Using application base 'C:\SRC\efcoretest\bin\Debug\net6.0'.
Using working directory 'C:\SRC\efcoretest'.
Using root namespace 'efcoretest'.
Using project directory 'C:\SRC\efcoretest\'.
Remaining arguments: .
Finding DbContext classes...
Finding IDesignTimeDbContextFactory implementations...
Finding application service provider in assembly 'efcoretest'...
Finding Microsoft.Extensions.Hosting service provider...
Using environment 'Development'.
Using application service provider from Microsoft.Extensions.Hosting.
Found DbContext 'AppDbContext'.
Finding DbContext classes in the project...
Using context 'AppDbContext'.
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 6.0.3 initialized 'AppDbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer:6.0.3' with options: None
Finding design-time services referenced by assembly 'efcoretest'...
Finding design-time services referenced by assembly 'efcoretest'...
No referenced design-time services were found.
Finding design-time services for provider 'Microsoft.EntityFrameworkCore.SqlServer'...
Using design-time services from provider 'Microsoft.EntityFrameworkCore.SqlServer'.
Finding IDesignTimeServices implementations in assembly 'efcoretest'...
No design-time services were found.
An operation was scaffolded that may result in the loss of data. Please review the migration for accuracy.
Writing migration to 'C:\SRC\efcoretest\Migrations\20220316112808_AddHourIndex.cs'.
Writing model snapshot to 'C:\SRC\efcoretest\Migrations\AppDbContextModelSnapshot.cs'.
Done. To undo this action, use 'ef migrations remove'

Provider and version information

EF Core version: 6.0.3
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Windows 10 Enterprise 21H2
IDE: Rider 2021.3.3

[AndriySvyryd]

Related to #23144

[N0D4N]

Same issue when using Pomelo.EntityFrameworkCore.MySql provider - EF Core tries to rename existing indexes, even though their names are explicitly set via Fluent API.
Fix is however pretty simple - remove calls to RenameIndex in migration file, but leave indexes with names that EF wants in Migration.Designer.cs file and ModelSnapshot, this way EF wouldn't try to rename those indexes in this and subsequent migrations. Even though this way EF would think that indexes have different names than they actually do have in DB, I don't think it would break something if you review your migrations' file which is a good practice anyway.