Scaffold-DbContext generates incorrect nav property on foreign key with filtered unique index

[bwalters3]

I've run into an issue using the Scaffold-DbContext command for the Package Manager Console in Visual Studio to reverse engineer a database on a SQL Server 2016 instance. It seems that command misinterprets one-to-many foreign key relationships as one-to-one when there is a filtered unique index on the foreign key column(s) in the child table. The navigation property on the entity type generated for the parent table should be a collection of child entities but it is instead scaffolded as a single child entity.

Steps to reproduce

Here's a contrived example that should help illustrate the issue:

Create the following table structure in an empty SQL Server (2016) database:

CREATE TABLE [dbo].[Person](
	[PersonId] [varchar](10) NOT NULL,
	CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ([PersonId])
)

CREATE TABLE [dbo].[PersonRole](
	[PersonRoleId] [int] IDENTITY(1,1) NOT NULL,
	[PersonId] [varchar](10) NOT NULL,
	[RoleId] [varchar](5) NOT NULL,
	[IsActive] [bit] NOT NULL,
	CONSTRAINT [PK_PersonRole] PRIMARY KEY CLUSTERED ([PersonRoleId]),
	CONSTRAINT [FK_PersonRole_Person] FOREIGN KEY ([PersonId]) REFERENCES [dbo].[Person]([PersonId])
)

CREATE UNIQUE INDEX [IX_PersonRole_PersonId] ON [dbo].[PersonRole]([PersonId]) WHERE [IsActive] = 1

Then, create a new project in Visual Studio using the Console App (.NET Core) template. Ensure that the project targets netcoreapp2.0 then add the following NuGet packages to the .csproj file:

<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.0.2" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.0.2" />

Next, run the Scaffold-DbContext command in the Package Manager Console to reverse engineer the database (modify connection string as needed):

Scaffold-DbContext "server=.;database=ScaffoldDbContextTest;Integrated Security=SSPI" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

Expected Result:

The PersonRole navigation property on the Person class should be of type ICollection<PersonRole> since the index allows for an unlimited number of rows with the same PersonId to exist in the PersonRole table as long as no more than one of them satisfies the filter clause.

Actual Result:

The configuration code in the DbContext class appears to describe the index correctly (including the filter clause) but the entity relationship is configured as one-to-one...

modelBuilder.Entity<PersonRole>(entity =>
{
    entity.HasIndex(e => e.PersonId)
        .IsUnique()
        .HasFilter("([IsActive]=(1))");
    ...
    entity.HasOne(d => d.Person)
        .WithOne(p => p.PersonRole)
        .HasForeignKey<PersonRole>(d => d.PersonId)
        .OnDelete(DeleteBehavior.ClientSetNull)
        .HasConstraintName("FK_PersonRole_Person");
});

...and the navigation property in the Person class is therefore not a collection, as if the filter doesn't exist:

public PersonRole PersonRole { get; set; }

Further technical details

EF Core version: 2.0.2
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Database Server: SQL Server 2016 13.0.4206.0
Operating system: Windows 10 Enterprise
IDE: Visual Studio 2017 15.6.2
.NET Core SDK: 2.1.101
.NET Core runtime: 2.0.6

[bricelam]

Similar to #10183. The provider would need to provide additional information about the index filter. If it's not the by-convention WHERE..IS NOT NULL, RevEng shouldn't treat the index as unique.

[ajcvickers]

Punting this bug from 6.0 since it has high risk/complexity.

[ajcvickers]

Blocked on implementation of #10183.