Composite keys do not work properly when generating code first schema

[KallDrexx]

We are encountering a ton of issues in trying to convert our production database from EDMX to scaffolded code first context (using VS 2017). This is in EF 6.2.0. It seems like EF6 has a lot of trouble with composite keys and keeps performing joins, inserts, and other operations on incorrect columns.

To start with I"m operating with the following schema:

create table test.dbo.Account
(
  AccountID int not null identity primary key,
)

CREATE TABLE test.dbo.Message
(
    MessageID int NOT NULL identity,
    AccountID int NOT NULL,
    CONSTRAINT Message_pk PRIMARY KEY (AccountID, MessageID),
    CONSTRAINT Message__Account FOREIGN KEY (AccountID) REFERENCES test.dbo.Account (AccountID)
)

create table test.dbo.MessageUser
(
  MeessageUserID int NOT NULL identity,
  AccountID int not null,
  MessageID int not null,
  constraint MessageUser_pk PRIMARY KEY  (AccountID, MeessageUserID),
  constraint MessageUser__Message foreign key (AccountID, MessageID) references test.dbo.message (AccountID, MessageID),
  constraint MessageUser__Account foreign key (AccountID) references test.dbo.Account (AccountID)
)

set identity_insert Account on;
insert into Account(AccountID) values (1001);
set identity_insert Account off;

Bug 1

1. Open a new project in VS 2017
2. Right click on the project, Add -> new item -> ADO.NET Entity Data Model -> Code First From database -> All tables
3. Add the following code to Program.cs

    class Program
    {
        static void Main(string[] args)
        {
            var context = new TestDbContext();
            var account = context.Accounts.First(x => x.AccountID == 1001);
            var message = new Message { Account = account };
            var user = new MessageUser { Message = message, Account = account };
            context.Accounts.Add(account);
            context.Messages.Add(message);
            context.MessageUsers.Add(user);
            context.SaveChanges();
        }
    }

---> Exception Occurs:

(13,10) : error 3015: Problem in mapping fragments starting at lines 13, 21: Foreign key constraint 'Message_MessageUsers' from table MessageUser (AccountID, MessageID) to table Message (MessageID, AccountID):: Insufficient mapping: Foreign key must be mapped to some AssociationSet or EntitySets participating in a foreign key association on the conceptual side.

Fixing this requires changing

            modelBuilder.Entity<Message>()
                .HasMany(e => e.MessageUsers)
                .WithRequired(e => e.Message)
                .HasForeignKey(e => new { e.MessageID, e.AccountID })
                .WillCascadeOnDelete(false);

To

            modelBuilder.Entity<Message>()
                .HasMany(e => e.MessageUsers)
                .WithRequired(e => e.Message)
                .HasForeignKey(e => new { e.AccountID, e.MessageID })
                .WillCascadeOnDelete(false);

Bug 2

Once that is changed if you re-run you now get the following exception:

SqlException: Cannot insert explicit value for identity column in table 'Message' when IDENTITY_INSERT is set to OFF.

This is because Visual Studio did not generate the identity column correctly. To fix this I had to manually add [DatabaseGenerated(DatabaseGeneratedOption.Identity)] to my 'Account.AccountID', Message.MessageID and 'MessageUser.MessageUserID` columns.

Bug 3

Now the following exception occurs:

ConstraintException: Referential integrity constraint violation. A Dependent Role has multiple principals with different values.

This can be addressed by changing the [Column] order value of the keys for both Message and MessageUser primary keys to make AccountID first.

Bug 4

Now run the following SQL to add a 3 composite key table

create table test.dbo.Location
(
  LocationID int not null identity,
  AccountID int not null,
  constraint Location_pk primary key (LocationID, AccountID),
  constraint Location_Account foreign key (AccountID) references test.dbo.Account (AccountID)
)

create table test.dbo.Inbox
(
  InboxID int not null identity,
  AccountID int not null,
  LocationID int not null,
  constraint Inbox_pk primary key (InboxID, AccountID, LocationID),
  constraint Inbox_Account foreign key (AccountID) references test.dbo.Account (AccountID),
  constraint Inbox_Location foreign key (LocationID, AccountID) references Location (LocationID, AccountID),
)

create table test.dbo.InboxMessage
(
  InboxMessageID int not null identity,
  AccountID int not null,
  InboxID int not null,
  MessageID int not null,
  LocationID int not null,
  constraint InboxMessage_pk primary key (InboxMessageID, AccountID),
  constraint InboxMessage_Location foreign key (LocationID, AccountID) references Location (LocationID, AccountID),
  constraint InboxMessage_Inbox foreign key (InboxID, AccountID, LocationID) references Inbox (InboxID, AccountID, LocationID),
  constraint InboxMessage_Message foreign key (AccountID, MessageID) references Message (AccountID, MessageID)
)

set identity_insert Message on;
insert into Message (AccountID, MessageID) values (1001, 2000);
set identity_insert Message off;
set identity_insert Location on;
insert into Location (AccountID, LocationID) values (1001, 3000);
set identity_insert Location off;
set identity_insert Inbox on;
insert into Inbox (AccountID, LocationID, InboxID) values (1001, 3000, 4000);
set identity_insert Inbox off;
set identity_insert InboxMessage on;
insert into InboxMessage (AccountID, InboxID, MessageID, LocationID, InboxMessageID) values (1001, 4000, 2000, 3000, 5000)
set identity_insert InboxMessage off;

Regenerate the code first models, run the app (so you get the fragment errors) and fix the foreign key constraints exactly as the exceptions tell you:

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Account>()
                .HasMany(e => e.Inboxes)
                .WithRequired(e => e.Account)
                .WillCascadeOnDelete(false);

            modelBuilder.Entity<Account>()
                .HasMany(e => e.Locations)
                .WithRequired(e => e.Account)
                .WillCascadeOnDelete(false);

            modelBuilder.Entity<Account>()
                .HasMany(e => e.Messages)
                .WithRequired(e => e.Account)
                .WillCascadeOnDelete(false);

            modelBuilder.Entity<Account>()
                .HasMany(e => e.MessageUsers)
                .WithRequired(e => e.Account)
                .WillCascadeOnDelete(false);

            modelBuilder.Entity<Inbox>()
                .HasMany(e => e.InboxMessages)
                .WithRequired(e => e.Inbox)
                .HasForeignKey(e => new { e.AccountID, e.InboxID, e.LocationID })
                .WillCascadeOnDelete(false);

            modelBuilder.Entity<Location>()
                .HasMany(e => e.Inboxes)
                .WithRequired(e => e.Location)
                .HasForeignKey(e => new { e.AccountID, e.LocationID })
                .WillCascadeOnDelete(false);

            modelBuilder.Entity<Location>()
                .HasMany(e => e.InboxMessages)
                .WithRequired(e => e.Location)
                .HasForeignKey(e => new { e.AccountID, e.LocationID })
                .WillCascadeOnDelete(false);

            modelBuilder.Entity<Message>()
                .HasMany(e => e.InboxMessages)
                .WithRequired(e => e.Message)
                .HasForeignKey(e => new { e.AccountID, e.MessageID })
                .WillCascadeOnDelete(false);

            modelBuilder.Entity<Message>()
                .HasMany(e => e.MessageUsers)
                .WithRequired(e => e.Message)
                .HasForeignKey(e => new { e.AccountID, e.MessageID })
                .WillCascadeOnDelete(false);
        }

Now change the main method to be:

        static void Main(string[] args)
        {
            var context = new TestDbContext();
            var account = context.Accounts.First(x => x.AccountID == 1001);
            var inbox = context.Inboxes
                .Include(x => x.InboxMessages)
                .First(x => x.InboxID == 4000 && x.AccountID == 1001);

            Console.WriteLine(inbox.InboxMessages.Count);
        }

Now execute and you'll see a 0 being returned, even though a 1 should be returned. Looking at the sql via sql profiler shows the following sql being generated:

 SELECT 
    [Project1].[InboxID] AS [InboxID], 
    [Project1].[AccountID] AS [AccountID], 
    [Project1].[LocationID] AS [LocationID], 
    [Project1].[C1] AS [C1], 
    [Project1].[InboxMessageID] AS [InboxMessageID], 
    [Project1].[AccountID1] AS [AccountID1], 
    [Project1].[InboxID1] AS [InboxID1], 
    [Project1].[MessageID] AS [MessageID], 
    [Project1].[LocationID1] AS [LocationID1]
    FROM ( SELECT 
        [Limit1].[InboxID] AS [InboxID], 
        [Limit1].[AccountID] AS [AccountID], 
        [Limit1].[LocationID] AS [LocationID], 
        [Extent2].[InboxMessageID] AS [InboxMessageID], 
        [Extent2].[AccountID] AS [AccountID1], 
        [Extent2].[InboxID] AS [InboxID1], 
        [Extent2].[MessageID] AS [MessageID], 
        [Extent2].[LocationID] AS [LocationID1], 
        CASE WHEN ([Extent2].[InboxMessageID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM   (SELECT TOP (1) [Extent1].[InboxID] AS [InboxID], [Extent1].[AccountID] AS [AccountID], [Extent1].[LocationID] AS [LocationID]
            FROM [dbo].[Inbox] AS [Extent1]
            WHERE (4000 = [Extent1].[InboxID]) AND (1001 = [Extent1].[AccountID]) ) AS [Limit1]
        LEFT OUTER JOIN [dbo].[InboxMessage] AS [Extent2] ON ([Limit1].[InboxID] = [Extent2].[AccountID]) AND ([Limit1].[AccountID] = [Extent2].[InboxID]) AND ([Limit1].[LocationID] = [Extent2].[LocationID])
    )  AS [Project1]
    ORDER BY [Project1].[InboxID] ASC, [Project1].[AccountID] ASC, [Project1].[LocationID] ASC, [Project1].[C1] ASC

You can clearly see that join failing because the left outer join says ([Limit1].[InboxID] = [Extent2].[AccountID]) AND ([Limit1].[AccountID] = [Extent2].[InboxID]) AND ([Limit1].[LocationID] = [Extent2].[LocationID])

In order to fix this I had to change the Inbox's key column orders to:

        [Key]
        [Column(Order = 2)]
        public int InboxID { get; set; }

        [Key]
        [Column(Order = 0)]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int AccountID { get; set; }

        [Key]
        [Column(Order = 1)]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int LocationID { get; set; }

and then change the foreign key definition to:

            modelBuilder.Entity<Inbox>()
                .HasMany(e => e.InboxMessages)
                .WithRequired(e => e.Inbox)
                .HasForeignKey(e => new { e.AccountID, e.LocationID, e.InboxID })
                .WillCascadeOnDelete(false);

Final Thoughts

Needless to say this has caused me much aggravation in the past few days trying to figure out what is going on. While this schema looks not great, this is a project that's been in production for 2+ years and therefore is not going to be trivial to untangle, so for better or for worse we are stuck where we are right now.

It is extremely concerning to me that these joins are failing due to (seemingly) arbitrary rules with no easy way to validate at test time that every foreign key definition and every column ordering was generated properly. It's also concerning to me that a lot of these errors are originating from Visual Studio generating incorrect CF definitions.

Our production database has about 300 tables so manually generating these code first POCOs is not an option, and the high table + view count means that moving to code first is not a "nice to have" as the EDMX management is making it impossible to deal with the current database contexts.

Further technical details

EF version: 6.2.0
Database Provider: EntityFramework.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 15.8.7

[KallDrexx]

I guess upon further reflection this bug is more of a Visual Studio CF generation bug (as it's generating clearly bad metadata that does not match up properly) more than an EF6 bug. I guess there's not really a good way for EF's runtime to guarantee your FKs are in the same order as the PK as you may not be joining on the PK only.

[KallDrexx]

For what it's worth I used this time to go ahead and just went straight to EF Core. EF Core's scaffolding did everything properly and things just worked.

[ajcvickers]

@KallDrexx Sorry, I was a bit trigger happy on the close button. When I attempt to create the database tables using the code posted above I get: Incorrect syntax near 'isu'.

[KallDrexx]

@ajcvickers Sorry I was also in a bad mood last night and should have worded it better.

I have fixed the script in the first post to remove the isu that somehow got appended (probably extra typing due to focus being shifted, that's what I assumed happened locally when I went through to reproduce the issue). I just took the updated script and verified it runs again.

[ajcvickers]

@KallDrexx Thanks. With this change I was able to reproduce the behavior. EF6 has always had issues around foreign key ordering that is different than the ordering of the columns in the tables. I'm not sure if this meets the bar for EF6 since customer feedback over the years has indicated that it's actually pretty uncommon to do this. That being said, I'll put this in 6.3 for now and look into fixing.

[KallDrexx]

Sounds good. I understand it's an uncommon scenario and that a lot of the EF teams effort is towards EF Core these days (for good reason), so even if it's determined that it's not worth fixing that's fine for me as long as the intention of that was stated. We still use EF6 since we can't use Core at the moment due to more complicated workflows, but we did end up not using scaffolding due to this bug, but it's not clear we'd have an easy path to do so in the future due to some customization we did in our EF entities.