EF Core 6 scaffolding skips foreign key on tables with multiple references

Question

I migrated an application from .NET 5 with EF Core 5 to .NET 6 with EF Core 6. We use the scaffolder to generate our database context. In our database we have a translation table to store all translations for dynamic data that needs to be translated.

Tables with only one translated column work fine. But in some cases we have tables with multiple translated columns e.g. name and description.

Following example for the Hazmat table we have two columns - one for the name (TranslationKeyName) and one for the description (TranslationDescriptionKey). For each column we have a relation to the translation table key.

The scaffolder of EF Core 5 generated following code which worked fine (appart from the ugly naming):

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; }
}

The EF Core 6 scaffolder however gives following warning and generates only one navigation property: Skipping foreign key 'FK_Translation_HazmatName' on table 'Application.Translation' since it is a duplicate of 'FK_Translation_HazmatDescription'.

For some reason the scaffolder thinks the two foreign keys are on the same column.

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

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

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

    public virtual ICollection<Translation> Translations { get; set; }
}

Is there a way to get the same behaviour to generate the navigation properties as in EF 5 or to override the skipping foreign key part?

Answer 1

It appears to be a bug in EF6, EF7 and EF8-preview. This bug will be fixed in EF8 (https://github.com/dotnet/efcore/issues/31169#issuecomment-1618581640).

Thanks to the amazing help of @ErikEJ - there is a workaround for EF6 & EF7. He added the fix also to the EF Core Power Tools (2.5.1520 and newer)!

When generating the DB context with the EF Core Power Tools - the navigation propeties will be generated correctly.