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:
{
[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:
{
[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:
[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:
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.
ADD CONSTRAINT [FK_dbo.Firsts_dbo.AspNetUsers_UserID]
FOREIGN KEY([UserID])
REFERENCES [dbo].[AspNetUsers] ([Id])