EF Core 6 and newer scaffolding skips foreign keys on tables with multiple references

[skuami]

File a bug

The EF Core scaffolder skips foreign keys on tables with multiple references to the same table but on different columns.

This issue occures on following example:
In the database is one table for all translations and other tables refernces translations with foreign key. Tables with only one translated column work just fine. But tables with multiple translated columns (multiple references to translation table) only the first column gets a navigation propery.

This scenario worked fine with EF 5. I think there is an issue in the dupplicate foreign key check. According to the unit test the skip should only happen when the same column has multiple reference to the same table. In my case there are different columns.

The same behaviour can be reproduced with EF 7 and EF 8-preview.

Detailed description of the problem with generated code examples on Stack Overflow:
https://stackoverflow.com/questions/76592136/ef-core-6-scaffolding-skips-foreign-key-on-tables-with-multiple-references

Reproduce the issue

1. Add following tables and constraints to an empty database.

CREATE TABLE [dbo].[Translation](
	[TranslationKey] [uniqueidentifier] NOT NULL,
	[LanguageCode] [nvarchar](2) NOT NULL,
	[TranslationText] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_Translation] PRIMARY KEY CLUSTERED 
(
	[TranslationKey] ASC,
	[LanguageCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[Hazmat](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[TranslationKeyName] [uniqueidentifier] NOT NULL,
	[TranslationKeyDescription] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Hazmat] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [UNIQUE_Hazmat_TranslationKeyDescription] UNIQUE NONCLUSTERED 
(
	[TranslationKeyDescription] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
CONSTRAINT [UNIQUE_Hazmat_TranslationKeyName] UNIQUE NONCLUSTERED 
(
	[TranslationKeyName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Translation]  WITH NOCHECK ADD  CONSTRAINT [FK_Translation_HazmatDescription] FOREIGN KEY([TranslationKey])
REFERENCES [dbo].[Hazmat] ([TranslationKeyDescription])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[Translation] NOCHECK CONSTRAINT [FK_Translation_HazmatDescription]
GO
ALTER TABLE [dbo].[Translation]  WITH NOCHECK ADD  CONSTRAINT [FK_Translation_HazmatName] FOREIGN KEY([TranslationKey])
REFERENCES [dbo].[Hazmat] ([TranslationKeyName])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[Translation] NOCHECK CONSTRAINT [FK_Translation_HazmatName]
GO

2. Create an empty console application and add following three nuget packages:

• Microsoft.EntrityFrameworkCode.Design
• Microsoft.EntrityFrameworkCode.SqlServer
• Microsoft.EntrityFrameworkCode.Tools

3. Use following command in the Visual Studio Developer Command Prompt:
   dotnet ef dbcontext scaffold "Server=xxx;Database=xxx;Trusted_Connection=True;TrustServerCertificate=True" Microsoft.EntityFrameworkCore.SqlServer --no-onconfiguring -f

Include stack traces

The EF Core 6 scaffolder gives following warning and generates only one navigation property eventhough the references are on two different columns:

Include verbose output

Using project 'c:\tmp\EfCoreIssue\ConsoleApp1\ConsoleApp1.csproj'.
Using startup project 'c:\tmp\EfCoreIssue\ConsoleApp1\ConsoleApp1.csproj'.
Writing 'c:\tmp\EfCoreIssue\ConsoleApp1\obj\ConsoleApp1.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\andyd\AppData\Local\Temp\tmpFA54.tmp /verbosity:quiet /nologo c:\tmp\EfCoreIssue\ConsoleApp1\ConsoleApp1.csproj
Writing 'c:\tmp\EfCoreIssue\ConsoleApp1\obj\ConsoleApp1.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\andyd\AppData\Local\Temp\tmpFCB6.tmp /verbosity:quiet /nologo c:\tmp\EfCoreIssue\ConsoleApp1\ConsoleApp1.csproj
Build started...
dotnet build c:\tmp\EfCoreIssue\ConsoleApp1\ConsoleApp1.csproj /verbosity:quiet /nologo

Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:00:01.19
Build succeeded.
dotnet exec --depsfile c:\tmp\EfCoreIssue\ConsoleApp1\bin\Debug\net6.0\ConsoleApp1.deps.json --additionalprobingpath C:\Users\andyd\.nuget\packages --additionalprobingpath "C:\Program Files (x86)\Microsoft Visual Studio\Shared\NuGetPackages" --runtimeconfig c:\tmp\EfCoreIssue\ConsoleApp1\bin\Debug\net6.0\ConsoleApp1.runtimeconfig.json C:\Users\andyd\.dotnet\tools\.store\dotnet-ef\8.0.0-preview.5.23280.1\dotnet-ef\8.0.0-preview.5.23280.1\tools\net6.0\any\tools\netcoreapp2.0\any\ef.dll dbcontext scaffold Server=xxx;Database=xxx;Trusted_Connection=True;TrustServerCertificate=True Microsoft.EntityFrameworkCore.SqlServer --no-onconfiguring -f --assembly c:\tmp\EfCoreIssue\ConsoleApp1\bin\Debug\net6.0\ConsoleApp1.dll --project c:\tmp\EfCoreIssue\ConsoleApp1\ConsoleApp1.csproj --startup-assembly c:\tmp\EfCoreIssue\ConsoleApp1\bin\Debug\net6.0\ConsoleApp1.dll --startup-project c:\tmp\EfCoreIssue\ConsoleApp1\ConsoleApp1.csproj --project-dir c:\tmp\EfCoreIssue\ConsoleApp1\ --root-namespace ConsoleApp1 --language C# --framework net6.0 --nullable --working-dir c:\tmp\EfCoreIssue\ConsoleApp1 --verbose
Using assembly 'ConsoleApp1'.
Using startup assembly 'ConsoleApp1'.
Using application base 'c:\tmp\EfCoreIssue\ConsoleApp1\bin\Debug\net6.0'.
Using working directory 'c:\tmp\EfCoreIssue\ConsoleApp1'.
Using root namespace 'ConsoleApp1'.
Using project directory 'c:\tmp\EfCoreIssue\ConsoleApp1\'.
Remaining arguments: .
Finding design-time services referenced by assembly 'ConsoleApp1'...
Finding design-time services referenced by assembly 'ConsoleApp1'...
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 'ConsoleApp1'...
No design-time services were found.
Found default schema 'dbo'.
Found type alias with name 'sys.sysname' which maps to underlying data type nvarchar(128).
Found table with name 'dbo.Hazmat'.
Found table with name 'dbo.Translation'.
Found column with table: dbo.Hazmat, column name: Id, ordinal: 1, data type: sys.bigint, maximum length: 8, precision: 19, scale: 0, nullable: False, identity: True, default value: (null), computed value: (null), computed value is stored: False.
Found column with table: dbo.Hazmat, column name: TranslationKeyName, ordinal: 2, data type: sys.uniqueidentifier, maximum length: 16, precision: 0, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False.
Found column with table: dbo.Hazmat, column name: TranslationKeyDescription, ordinal: 3, data type: sys.uniqueidentifier, maximum length: 16, precision: 0, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False.
Found column with table: dbo.Translation, column name: TranslationKey, ordinal: 1, data type: sys.uniqueidentifier, maximum length: 16, precision: 0, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False.
Found column with table: dbo.Translation, column name: LanguageCode, ordinal: 2, data type: sys.nvarchar, maximum length: 4, precision: 0, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False.
Found column with table: dbo.Translation, column name: TranslationText, ordinal: 3, data type: sys.nvarchar, maximum length: -1, precision: 0, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False.
Found primary key on table 'PK_Hazmat' with name 'dbo.Hazmat'.
Found unique constraint on table 'UNIQUE_Hazmat_TranslationKeyDescription' with name 'dbo.Hazmat'.
Found unique constraint on table 'UNIQUE_Hazmat_TranslationKeyName' with name 'dbo.Hazmat'.
Found primary key on table 'PK_Translation' with name 'dbo.Translation'.
Found foreign key on table 'FK_Translation_HazmatDescription' with name 'dbo.Translation', principal table 'dbo.Hazmat', delete action NO_ACTION.
Found foreign key on table 'FK_Translation_HazmatName' with name 'dbo.Translation', principal table 'dbo.Hazmat', delete action NO_ACTION.
Skipping foreign key 'FK_Translation_HazmatName' on table 'dbo.Translation' since it is a duplicate of 'FK_Translation_HazmatDescription'.

Include provider and version information

EF Core version: Tested on 6.0.19, 7.0.8 and 8.0.0-preview.5
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Windows 11 Version 22H2
IDE: Visual Studio 2022 17.4.5

[ErikEJ]

@skuami I do not understand you design. Should the foreign keys not be in the Hazmat table and point to Translation? (so that Hazmat.TranslationKeyName and Hazmat.TranslationKeyDescription becomes foreign keys)

[skuami]

@ErikEJ You're right - it is not a obvious design nor best practice. It is a fairly large application with a database of about 150 tables and one can say it has historicaly grown.
There is a story in our backlog to redesign the translation system but unfortunately there is no time to do so.

Here are some example records and how the translations are "patched" together:

-- insert some test records
INSERT INTO dbo.Translation VALUES ('FE56B53E-C5FF-4D1C-B56D-565DC03E457F', 'DE', 'Mein Name')
INSERT INTO dbo.Translation VALUES ('FE56B53E-C5FF-4D1C-B56D-565DC03E457F', 'EN', 'My Name')
INSERT INTO dbo.Translation VALUES ('D990BD93-2CD3-477F-A3BD-30C75D3FAB68', 'DE', 'Meine Beschreibung')
INSERT INTO dbo.Translation VALUES ('D990BD93-2CD3-477F-A3BD-30C75D3FAB68', 'EN', 'My Description')

INSERT INTO dbo.Hazmat VALUES ('FE56B53E-C5FF-4D1C-B56D-565DC03E457F', 'D990BD93-2CD3-477F-A3BD-30C75D3FAB68')

-- select test record from LanguageCode 'EN'
SELECT h.Id, tName.TranslationText AS [Name], tDesc.TranslationText AS [Description] FROM dbo.Hazmat h
INNER JOIN dbo.Translation tName ON h.TranslationKeyName = tName.TranslationKey
INNER JOIN dbo.Translation tDesc ON h.TranslationKeyDescription = tDesc.TranslationKey
WHERE tName.LanguageCode = 'EN' and tDesc.LanguageCode = 'EN'

Since the PK of the translation table is a combination of two columns - it is not possible to create a FK from the Hazmat table or all other tables in our database. That's why it is the other way around.

Eitherway EF 5 could handle this design and generated following code;

public partial class Translation
{
    ...
    public virtual Hazmat TranslationKey12 { get; set; }
    public virtual Hazmat TranslationKey15 { get; set; }
    ...
}

public partial class Hazmat
{
    public Hazmat()
    {
        TranslationTranslationKey12s = new HashSet<Translation>();
        TranslationTranslationKey15s = new HashSet<Translation>();
    }

    public Guid TranslationKeyName { get; set; }
    public Guid TranslationKeyDescription { get; set; }

    public virtual ICollection<Translation> TranslationTranslationKey12s { get; set; }
    public virtual ICollection<Translation> TranslationTranslationKey15s { get; set; }
}

[ErikEJ]

@skuami got it and agree there is a bug. I am working on a fix.

[ErikEJ]

@ajcvickers Patch 6 ?? Or not worth it?

[skuami]

Thanks Erik for your help!

[ajcvickers]

@ErikEJ Not for now. We may reconsider with more reports.