Code First Foreign Keys With Different Names

The Entity Framework’s Code First scheme is handy way to do quick development of new websites. I recently had an issue with a foreign key relationship between a table I created and the ASP.Net Identity user table AspNetUsers. Typically, EF intelligently maps foreign key columns back to their parent key names. For example:

public class First
{
    [Key]
    public int FirstID;
    public int SecondID;

    // ...

    public virtual Second second { get; set; }
}

public class Second
{
    [Key]
    public int SecondID;
}

will properly result in a database schema where a foreign key relationship is set up between First and Second with SecondID as the foreign key field.

ASP.Net Identity presents a problem in that the primary key for the AspNetUser table is simply “Id”. I didn’t want a foreign key in my table simply called “Id” because this would not be very descriptive when looking at the code later. What happens when you don’t include a foreign key column but do add the reference to the code? Here’s the code:

    public class First
    {
        [Key]
        public int FirstID {get; set; }
        // I'd like this to be the foreign key to the AspNetUser table.
        public string UserID {get; set; }

        public virtual ApplicationUser AspNetUser { get; set; }
    }

And here’s a part of the resulting SQL database schema:

CREATE TABLE [dbo].[Firsts](
    [FirstID] [INT] IDENTITY(1,1) NOT NULL,
    [SecondID] [INT] NOT NULL,
        // Points TO nothing.
    [UserID] [nvarchar](MAX) NOT NULL,
        // FOREIGN KEY TO USER TABLE.
    [AspNetUser_Id] [nvarchar](128) NULL
...

Note that EF does create a foreign key as is necessary, but it prefixes the “Id” name with the table name, in this case “AspNetUsers_Id”. It’s nice that it works, but that’s not the name I wanted to use as the foreign key. There had to be some way to employ a foreign key whose name was not the same as the primary key. A Google search showed a couple of examples on how to do this. Unfortunately, many suggested using the EF fluent interface to update the entity context at create time. The code in some cases looked extremely hard to follow.

Eventually I stumbled upon a much simpler solution. By simply adding the ForeignKey attribute to the foreign key declaration EF will be able to properly associate the foreign and primary keys even if their names are not the same. All that’s required is that you spell out the table which the key references:

[Required, ForeignKey("AspNetUser")]
public string UserID { get; set; }

With this simple one line fix I was able to use the foreign key column name that I thought was easier to read, UserID.

ALTER TABLE [dbo].[Firsts] WITH CHECK
    ADD CONSTRAINT [FK_dbo.Firsts_dbo.AspNetUsers_UserID]
    FOREIGN KEY([UserID])
REFERENCES [dbo].[AspNetUsers] ([Id])
This entry was posted in Programming and tagged . Bookmark the permalink.

2 Responses to Code First Foreign Keys With Different Names

  1. Hi, i read your blog occasionally and i own a similar one and i
    was just wondering if you get a lot of spam
    feedback? If so how do you prevent it, any plugin or anything you can advise?
    I get so much lately it’s driving me crazy so any assistance is very much appreciated.

    • Robert Barron says:

      Can’t say that I get much spam feedback. But then, this is not that big of a blog! Lots of higher profile targets out there for spammers.

Leave a Reply

Your email address will not be published. Required fields are marked *