Entity Framework won't detect foreign key from database

Question

I've got two tables - Appointment and User. Appointments can be linked to two different Users - a student and a member of staff. The Appointment table contains two foreign keys: StaffUsername and ExternalID. These reference columns in the User table named Username (the User table's PK) and ExternalID (a UNIQUE index). Here are the table definitions:

CREATE TABLE [dbo].[Appointment]
(
    [ID] INT NOT NULL IDENTITY(1,1),
    [AppointmentTypeID] INT NOT NULL,
    [StartTime] DATETIME NOT NULL,
    [EndTime] DATETIME NOT NULL,    
    [AppointmentSlotID] INT NULL,    
    [StaffUsername] NVARCHAR(200) NOT NULL,
    [ExternalID] NVARCHAR(10) NULL,
    [BookedBy] NVARCHAR(200) NOT NULL,
    [BookedTimestamp] DATETIME NOT NULL,
    [ReminderEmailSentTimestamp] DATETIME NULL,
    [CancelledBy] NVARCHAR(200) NULL,
    [CancelledTimestamp] DATETIME NULL,
    [StudentDidNotAttend] BIT NULL,
    [LastModifiedTimestamp] DATETIME NOT NULL,
    [LastModifiedBy] NVARCHAR(200) NOT NULL,
    CONSTRAINT [PK_Appointment] PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [FK_Appointment_AppointmentType] FOREIGN KEY ([AppointmentTypeID]) REFERENCES [dbo].[AppointmentType]([ID]),
    CONSTRAINT [FK_Appointment_AppointmentSlot] FOREIGN KEY ([AppointmentSlotID]) REFERENCES [dbo].[AppointmentSlot]([ID]),
    CONSTRAINT [FK_Appointment_User_StaffUsername] FOREIGN KEY ([StaffUsername]) REFERENCES [dbo].[User]([Username]),
    CONSTRAINT [FK_Appointment_User_ExternalID] FOREIGN KEY ([ExternalID]) REFERENCES [dbo].[User]([ExternalID])
)

CREATE TABLE [dbo].[User]
(
    [Username] NVARCHAR(200) NOT NULL,
    [FirstName] NVARCHAR(200) NULL,
    [LastName] NVARCHAR(200) NULL,
    [EmailAddress] NVARCHAR(200) NULL,
    [IsStaff] BIT NOT NULL DEFAULT 0,
    [ExternalID] NVARCHAR(10) NOT NULL,
    [LastLogin] DATETIME NULL,
    CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ([Username] ASC),
    CONSTRAINT [UQ_ExternalID] UNIQUE ([ExternalID])
)

Unfortunately, when I use the Update model from database option in the EDMX model designer, it will not pick up the foreign key on the ExternalID columns. It remains looking like this (highlighted in green are the properties relating to the relationship which is modelled correctly, in yellow are the properties which should relate to a second relationship but are being ignored):

I know from experience that the EDMX designer can be quirky at times, especially when detecting changes to objects, so I've tried all the usual tricks. I've checked in Web.config that my connection string is pointing to the correct database. I've deleted the Appointment and User tables in the designer completely and run the Update command again. I've tried that with a save and restart of Visual Studio between deletion and update, too.

To check the relationship is correct in the database I've created a database diagram in SSMS which shows the troublesome relationship correctly:

I've also created a brand new project and added a new Entity Data Model pointing to the same database with the same credentials, just in case the issue was related to the fact that I'm updating an existing model, but no dice. Even in the new project, the relationship isn't detected.

I also tried to create the Navigation Property manually, but as you can see from this screenshot, the foreign key I'd need to select isn't available in the dropdown list:

I don't know if the issue somehow relates to the fact that the ExternalID column isn't the primary key of the User table, or maybe its NVARCHAR(10) data type. I've no idea, to be honest.

Any suggestions as to why this foreign key isn't being detected? And how I can fix it? My project targets .NET Framework 4.6 and I'm using EF6. Obviously I'm using Database First.

Answer 1

In EF6 an Entity only has one key, and so all Navigation Properties must use a Foreign Key that references the same key. EF Core supports Alternate Keys, and supports a Database-First workflow with Reverse Engineering.