EF6 Code First: Using Fluent API to declare a Foreign Key

Question

I'm working on an insurance industry related app with EF 6 and Code First. For this app, each Account can have multiple Policies and each Policy can have multiple Transactions. Further, each Account must have a relationship to an Identity (Name, Address, City, etc.). The Policy also has a relationship to an Identity, but it is optional.

Because of Account -> Identity and Account ->> Policy -> Identity, I have found that I need to use Fluent API to set the WillCascadeDelete to False for at least one of those paths.

My question is how do I configure the Account.IdentityId and Policy.InsuredIdentityId properties to be foreign keys? I've avoided adding any navigation/foreign key fields to the identity class because there should never be a reason to navigate from an identity to another class. Is that why I'm having a tough time figuring this out?

public class Account
{
    public long Id { get; set; }
    public long IdentityId { get; set; }
    public virtual Identity Identity { get; set; }

    public ICollection<Policy> Policies { get; set; }
}

public class Policy
{
    public long Id { get; set; }

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

    public bool UseAccountIdentity { get; set; }
    public long InsuredIdentityId { get; set; }
    public virtual Identity InsuredIdentity { get; set; }
}

 public class Identity 
{        
    public long Id { get; set; }

    public string Name { get; set; }
    public string Address1 { get; set; }
    public string Address2 { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string Zip { get; set; }
}

public class AccountConfiguration : EntityTypeConfiguration<Account>
{
    public AccountConfiguration()
    {
        HasRequired(a => a.Identity).WithOptional(x => x.Account).WillCascadeOnDelete(false);
        HasMany(a => a.Policies).WithRequired(p => p.Account).HasForeignKey(p => p.AccountId);
    }
}

public class PolicyConfiguration : EntityTypeConfiguration<Policy>
{
    public PolicyConfiguration()
    {
        HasOptional(p => p.InsuredIdentity).WithOptionalPrincipal().WillCascadeOnDelete(false);
    }
}

As a side question, are there any good books or other reference sources for EF Code First? I have Julia Lerman's Programming Entity Framework: Code First, which is fine for the examples that it does cover but it doesn't cover enough cases.

Answer 1

Firstly, public ICollection<Policy> Policies { get; set; } should be public virtual ICollection<Policy> Policies { get; set; }.

You can only map EF foreign key properties when one side of the relationship is a one, and the other is a many. Any time you have a 1:1 or 1:0..1 relationship, the dependent entity will take the same primary key as the principal. In these cases there cannot be a foreign key because the dependent's foreign key IS its primary key.

So for this:

HasRequired(a => a.Identity).WithOptional(x => x.Account)

... account's Id will be the same value as the identity's Id. Meaning, you can completely remove the IdentityId property from the Account entity. However I don't understand how that code compiles, because Identity does not have an Account navigation property..?

When it comes to your Policy mapping, is this really how you want to do it? You say that a Policy can have an optional relationship to an Identity, but the way you are doing it:

HasOptional(p => p.InsuredIdentity).WithOptionalPrincipal()

...means that an Identity can only be related (directly) do one Policy. What's wrong with making the mapping look like this?

HasOptional(p => p.InsuredIdentity).WithMany()
    .HasForeignKey(x => x.InsuredIdentityId)
    .WillCascadeOnDelete(false);

With the above, you still do not have any navigation properties from Identity to Policy. You don't need one, you can just invoke .WithMany() as a no-arg. However this tells the database to set up Identity as the principal, and Policy as the dependent, so you have a normal fk relationship in the db, and you still cannot navigate to a Policy from an Identity in code.

Other comments:

The Policy also has a relationship to an Identity, but it is optional.

This means that Policy.InsuredIdentityId should be a System.Nullable<long>.

Answer 2

My problem was conceptual. While typically an Identity would only apply to one Account (leading me to visualize it as a 1:1), theoretically the Identity could be used more then once and therefore on more then one Account. If I use the following configuration, I get what I was looking for. Its then up to me to enforce any rules such as an Identity can only be used with one Account (but as many Policies as are needed)

public class IdentityConfiguration : EntityTypeConfiguration<Identity>
{
    public IdentityConfiguration()
    {
        HasMany(i => i.Accounts).WithRequired(i => i.Identity).HasForeignKey(a => a.IdentityId);            
    }
}

Alternatively, I could have made the Identity the Primary entity and the Account the optional dependent. this would work because all Accounts have an Identity but not all Identities are linked to an Account. Account would no longer have its own key.

That's not intuitive, which is somewhat concerning.