How do I Create multiple not null properties of same type using fluent mapping in Entity Framework Code First

Question

I am using EF5 with a Code First and Fluent Mapping Approach. Here's my scenario, trimmed down for clarity:

public class SecuritySettings
{
   public int Id { set; set }
   public Company Company { get; set; }
   public int MaximumInvalidPasswordAttempts { get; set;
   // etc.
}

public class Company
{
   public int Id { set; set }
   public string Name { get; set; }
   public SecuritySettings InternalUserSecuritySettings { get; set; }
   public SecuritySettings ExternalUserSecuritySettings { get; set; }
}

With just the above, I get two FKs in the Company table:

   InternalUserSecuritySettings_Id int, null
   ExternalUserSecuritySettings_Id int, null

So far, so good.

I have configuration classes I used to set out my fluent mapping - one for Company and one for SecuritySettings. These are stipulating required properties. Within the Company configuration, if I do the following:

HasRequired(x => x.InternalUserSecurityConfiguration);

This results in:

InternalUserSecuritySettings_Id int, not null ExternalUserSecuritySettings_Id int, null

BUT, if I then add this:

HasRequired(x => x.ExternalUserSecurityConfiguration);

I get the following exception:

Introducing FOREIGN KEY constraint 'FK_dbo.Companies_dbo.SecuritySettings_ExternalUserSecurityConfiguration_Id' on table 'Companies' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

I have tried:

1. .WithRequiredDependent(y => y.Company).WillCascadeOnDelete(false)
2. .WithRequiredDependent(y => y.Company).Map(m => m.MapKey("ExternalUSC_Id"))

Amongst other such permutations but either I get an exception or only one of the FKs get created. I would like to do this within the Fluent Mapping of the entity configuration - preferably the Company one. Am I missing something obvious. It seems such a shame that going from something so easy and trivial with the not nulls to finding it so hard to simply stipulate I want a key for each and both must not be null quite frustrating.

I did find a way to get this to work by using a separate property for the actual Foreign Key but firstly, this seems such an extra effort for a not null requirement and secondly, I had to declare a lot of configuration in the OnModelCreating method using a "WithMany()" mapping which is just counter-intuitive to me!

Answer 1

You can get this to work with not null properties by using the following fluent:

        modelBuilder.Entity<Company>().
            HasRequired(x => x.InternalUserSecuritySettings)
            .WithMany()
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<Company>().
            HasRequired(x => x.ExternalUserSecuritySettings)
           .WithMany()
            .WillCascadeOnDelete(false);

Produces this database:

However - I'm a bit concerned about the Company property on your SecuritySettings entity. Are you wanting that nav property to map to the opposite end of the Internal/External SecuritySettings properties? If so - this can't be done w/ EF (you can't have a single property on one entity, map to 2 other properties). To consider why: If you added a Company to a SecuritySetting -

var CompanyA = new Company;
CompanyA.SecuritySettings(new SecuritySettings());

Which end on the SecuritySettings entity should it map to (Internal or External?) Since it's not possible to tell with the scenario above it's invalid.

You could add 2 different SecuritySettings collections to your Company, and then you'd have a proper mapping.

Answer 2

RE: Mark Oreta - The Company property wasn't originally there when I look at source control history - it must have been one of my 'let's try this and see what happens' attempts! I have removed this as I don't need to ever reference a security setting without doing so within the context of a company. I did have a similar version working as yours but I've taken yours as the answer as I was able to lead on from your answer to at least being able to add all of the mappings inside my CompanyConfiguration class, rather than in the OnModelCreating method. The only sore point now is that these are still meant to be one-to-one mappings which WithMany() does not infer or enforce. So they only way I can force this through would be with a unique constraint. Not very pretty! Thank you for your time and answer.

EDIT:

Just to share my current version of answer to this problem:

1. The company configuration Class (DataEntityBaseConfiguration inherits from EntityTypeConfiguration)

public class CompanyConfiguration : DataEntityBaseConfiguration<Company>
   {
    public CompanyConfiguration()
    {
        Property(x => x.Name).IsRequired();
        Property(x => x.Name).HasMaxLength(256);
        HasRequired(x => x.InternalUserSecurityConfiguration).WithMany().WillCascadeOnDelete(false);
        HasRequired(x => x.ExternalUserSecurityConfiguration).WithMany().WillCascadeOnDelete(false);

    }
   }

And then, in my Context Initialiser class, I add the following lines of code to enforce a unique constraint on both properties:

AddUniqueConstraint<Company>(() => new Company().ExternalUserSecurityConfigurationId);
AddUniqueConstraint<Company>(() => new Company().InternalUserSecurityConfigurationId);

Where the AddUniqueConstraint is a method I wrote to execute the sql for creating a unique contraint - follow here to read more.