Friday, June 7, 2013

Multiple foreign keys within same table using CodeFrist Enitty Framework and Fluent API

This example shows how to work with multiple foreign key referred to the same table using CodeFirst Entity Framework (Inverse Navigation Properties 
)

Parent Table (Branch) has two foreign keys (Primary Contact and Secondary Contact) referring to the Child Table (Contact).


Branch
BranchIDguid
Branch Namevarchar
PrimaryContactIDguid,null
SecondaryContatIDguid,null



Contact
ContactIDguid
Firstnamevarchar
LastNamevarchar
Addressvarchar
Genderchar
First of all we need to create the Branch Class

 public class Branch
    {
        public Guid BranchID { get; set; }
        public string BranchName { get; set; }
        public Nullable<Guid> PrimaryContactID { get; set; }
        public Nullable<Guid> ScondaryContactID{ get; set; }
        public Contact PrimaryContact { get; set; }
        public Contact SecondaryContact { get; set; }
        
    }

Then creating the Contact Class


 public class Contact
    {
        public Guid ContactID { get; set; }
        public string Firstname { get; set; }
        public string Lastname { get; set; }        public ICollection<Branch> PrimaryContactFor { get; set; }
        public ICollection<Branch> ScondaryContactFor{ get; set; }
        
    }


When configuring in the fluent API below shown coding is used.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<Branch>().HasOptional(b => b.PrimaryContact).WithMany(a => a.PrimaryContactFor).HasForeignKey(b=>b.PrimaryContactID);
            modelBuilder.Entity<Branch>().HasOptional(b => b.SecondaryContact).With Many (a => a.ScondaryContactFor).HasForeignKey(b=>b.ScondaryContactID);

        }

Happy Coding :)





3 comments:

Unknown said...

Perfect explanations and very good approach... Thank you very much.

Anonymous said...

This is very helpful, thanks.

Anonymous said...

Thanks so much!
In a newer version, I was having multiple "Invalid column name" errors with different aliases. Adding FK schema took care of 3, but I needed this to fix the last 2. Though I modified the .tt to add a new function call that I placed in a new partial class for the .edmx's .cs partial class file. The .tt file allows you to make changes to the (now?) automatically generated partial class .cs file. While you can probably put all of your changes in the .tt file, it's easier to create a new partial class *of the same name*. Anyway, thanks again!