Bob.at.SBS Bob.at.SBS - 2 months ago 20
C# Question

Duplicate foreign key

I have two POCO entities, ApplicationUser and Account.


  • One Account has many ApplicationUsers.

  • Each account has exactly one ApplicationUser who is the BillingContact



So, my entities are:

public class ApplicationUser
{
public string Id { get; set; }

public int AccountId { get; set; }
public virtual Account Account { get; set; }
}

public class Account
{
public int Id { get; set; }

[ForeignKey("BillingContact")]
public string BillingContactId { get; set; }
public virtual ApplicationUser BillingContact { get; set; }

public virtual ICollection<ApplicationUser> Users { get; set; }


My problem is that when I create a migration, code-first only partially understands that BillingContact is a foreign key. The migration code creates the Account table with the BillingContactId foreign key, but it also creates an extra Account_Id foreign key field.

I've found a workaround, but it seriously doesn't pass the "smell" test. The underlying problem is that I have multiple relationships between the two classes, which confuses code-first. In particular, the ApplicationUser entity does not have a navigation property that is the "other end" of the BillingContact foreign key.

If I add a navigation property to ApplicationUser, and mark it with the InverseProperty attribute then code-first seems to understand the foreign key and doesn't create the extra field:

public class ApplicationUser
{
public string Id { get; set; }

public int AccountId { get; set; }
public virtual Account Account { get; set; }

[InverseProperty("BillingContact")]
public virtual ICollection<Account> MyBillingAccounts { get; set; }
}


The problem with this workaround is that the MyBillingAccounts navigation property is totally bogus. An Account has a related ApplicationUser that is the billing contact, but reverse relationship (navigating from an ApplicationUser back to the BillingContactId foreign key) makes no actual sense.

So... Is there a better (or proper) way to teach code-first about the BillingContactId foreign key?

Answer

You can only do this with fluent mapping. For example in an override of OnModelCreating of your context:

modelBuilder.Entity<Account>()
            .HasOptional(acc => acc.BillingContact)
            .WithMany()
            .HasForeignKey(acc => acc.BillingContactId);

The empty WithMany() call indicates that there is an inverse end of the association without a navigation property.

Comments