Entity Framework with Proper Foreign Key Names

Full source code here.

One of the very nice features of Entity Framework is the navigational properties. But it is easy to set them up in a less that optimal way. If you have ever ended up with a foreign key column looking something like - TableName_TableNameId, for example Account_AccountId instead of just AccountId it’s probably because you missed a property on the related entity.

Here is the Account class with a collection of Phones and Addresses.

 1    public class Account
 2    {
 3        public Account()
 4        {
 5            Addressess = new List<Address>();
 6            Phones = new List<Phone>();
 7        }
 8
 9        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
10        public Guid AccountId { get; set; }
11        public string FirstName { get; set; }
12        public string LastName { get; set; }
13
14        public virtual ICollection<Phone> Phones { get; set; }
15        public virtual ICollection<Address> Addressess { get; set; }
16    }

The Phone has a navigation property to Account, but it does not have a property for the for the AccountId, this results in a foreign key named Account_AccountId in the table. This works just fine, but doesn’t look quite right.

 1    public class Phone
 2    {
 3        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
 4        public Guid PhoneId { get; set; }
 5        public string Number { get; set; }
 6
 7        // No AccountId defined
 8        // Entity Framework will add a foreign key to Phones table called Account_AccountId
 9
10        // Navigation property
11        public virtual Account Account { get; set; }
12    }

The better way is to add a property for the AccountId to the linked entity, resulting in a foreign key named AccountId.

 1    public class Address
 2    {
 3        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
 4        public Guid AddressId { get; set; }
 5        public string Line1 { get; set; }
 6        public string Line2 { get; set; }
 7        public string City { get; set; }
 8        public string State { get; set; }
 9        public string ZipCode { get; set; }
10       
11        // This will be the name of the foreign key in the table
12        public Guid AccountId { get; set; }
13        
14        // Navigation property
15        public virtual Account Account { get; set; }
16    }

Here are the created tables, as you can see the Phones table will has the poorly named foreign key and the Address table has the properly named foreign key.

Good And Bad Foreign Keys

Full source code here.

comments powered by Disqus

Related