Two One-to-Many relationships in the same table

Question

I have a table called SystemAccount, which (up until recently) had a MasterAccountID on it that would point to its parent account (obviously an int?). My client has now told me that there may be sometimes where an account can have 2 parent accounts (none ever have more than that). I've been trying to make the adjustment in my SystemAccount class, but it's not generating the relationship that I want.

Here's part of the class code:

[ForeignKey("MasterAccount")]
public int? MasterAccountID { get; set; }

[ForeignKey("SecondMasterAccount")]
public int? SecondMasterAccountID { get; set; }

public virtual SystemAccount MasterAccount { get; set; }

public virtual SystemAccount SecondMasterAccount { get; set; }

public virtual List<SystemAccount> AllSubAccounts { get; set; }

public virtual List<SystemAccount> SecondarySubAccounts { get; set; }

When I do this I get 4 FKs in the table, 2 of which are auto-generated (SystemAccount_ID and SystemAccount_ID1). I've even tried to put the [InverseProperty] attribute on MasterAccount and SecondMasterAccount to point to the Lists, and it gives me an error each time (EDIT: It gives me a NullReferenceException).

I know that I should make it into a many-to-many relationship, but I'm facing a deadline soon, and refactoring the uses of MasterAccount and MasterAccountID would take me way beyond the deadline.

How can I get this to work?

EDIT: Exception stack trace:

System.NullReferenceException was unhandled by user code
  HResult=-2147467261
  Message=Object reference not set to an instance of an object.
  Source=EntityFramework
  StackTrace:
       at System.Data.Entity.ModelConfiguration.Configuration.Types.EntityTypeConfiguration.Configure(EdmEntityType entityType, EdmModel model)
       at System.Data.Entity.ModelConfiguration.Configuration.ModelConfiguration.ConfigureEntities(EdmModel model)
       at System.Data.Entity.ModelConfiguration.Configuration.ModelConfiguration.Configure(EdmModel model)
       at System.Data.Entity.DbModelBuilder.Build(DbProviderManifest providerManifest, DbProviderInfo providerInfo)
       at System.Data.Entity.DbModelBuilder.Build(DbConnection providerConnection)
       at System.Data.Entity.Infrastructure.EdmxWriter.WriteEdmx(DbContext context, XmlWriter writer)
       at System.Data.Entity.Migrations.Extensions.DbContextExtensions.<>c__DisplayClass1.<GetModel>b__0(XmlWriter w)
       at System.Data.Entity.Migrations.Extensions.DbContextExtensions.GetModel(Action`1 writeXml)
       at System.Data.Entity.Migrations.Extensions.DbContextExtensions.GetModel(DbContext context)
       at System.Data.Entity.Migrations.DbMigrator..ctor(DbMigrationsConfiguration configuration, DbContext usersContext)
       at System.Data.Entity.Migrations.DbMigrator..ctor(DbMigrationsConfiguration configuration)
       at System.Data.Entity.MigrateDatabaseToLatestVersion`2.InitializeDatabase(TContext context)
       at System.Data.Entity.Database.<>c__DisplayClass2`1.<SetInitializerInternal>b__0(DbContext c)
       at System.Data.Entity.Internal.InternalContext.<>c__DisplayClass8.<PerformDatabaseInitialization>b__6()
       at System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action)
       at System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization()
       at System.Data.Entity.Database.Initialize(Boolean force)
       at Tests.Core.UI.SessionStartTests.ShouldSuccessfullyInitializeDatabase() in c:\Projects\Current\tests\Tests.Core\UI\StartTests.cs:line 72
  InnerException: 

EDIT 2: When I used Moho's suggestion:

System.Data.Entity.ModelConfiguration.ModelValidationException : One or more validation errors were detected during model generation:

\tSystem.Data.Entity.Edm.EdmAssociationEnd: : Multiplicity is not valid in Role 'SystemAccount_AllSubAccounts_Target' in relationship 'SystemAccount_AllSubAccounts'. Valid values for multiplicity for the Principal Role are '0..1' or '1'.
\tSystem.Data.Entity.Edm.EdmAssociationEnd: : Multiplicity is not valid in Role 'SystemAccount_AllSubAccounts_Source' in relationship 'SystemAccount_AllSubAccounts'. Because the Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be '*'.
\tSystem.Data.Entity.Edm.EdmAssociationEnd: : Multiplicity is not valid in Role 'SystemAccount_SecondarySubAccounts_Target' in relationship 'SystemAccount_SecondarySubAccounts'. Valid values for multiplicity for the Principal Role are '0..1' or '1'.
\tSystem.Data.Entity.Edm.EdmAssociationEnd: : Multiplicity is not valid in Role 'SystemAccount_SecondarySubAccounts_Source' in relationship 'SystemAccount_SecondarySubAccounts'. Because the Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be '*'.

EDIT 3: My code for updating the database:

Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyDbContext, Configuration>());
var db = new MyDbContext();
db.Database.Initialize(true);

My OnModelCreating method:

modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

modelBuilder.Entity<ClientStatisticsView>().ToTable("ClientStatistics");

base.OnModelCreating(modelBuilder);

My Configuration file:

public Configuration()
{
  AutomaticMigrationsEnabled = true;
  AutomaticMigrationDataLossAllowed = true;
}

protected override void Seed(MyDbContext context)
{
}

Answer 1

Have you tried decorating the collection properties with the InverseProperty attribute?

[InverseProperty( "MasterAccount" )]
public virtual List<SystemAccount> AllSubAccounts { get; set; }

[InverseProperty( "SecondMasterAccount" )]
public virtual List<SystemAccount> SecondarySubAccounts { get; set; }

Here's a demo that works for me:

public class HierarchicalEntity
{
    public int Id { get; set; }
    public string Description { get; set; }

    [ForeignKey( "PrimaryParent" )]
    public int? PrimaryParentId { get; set; }

    [ForeignKey( "SecondaryParent" )]
    public int? SecondaryParentId { get; set; }
    public virtual HierarchicalEntity PrimaryParent { get; set; }

    public virtual HierarchicalEntity SecondaryParent { get; set;}

    [InverseProperty( "PrimaryParent" )]
    public ICollection<HierarchicalEntity> ChildrenViaPrimaryParent { get; set; }

    [InverseProperty( "SecondaryParent" )]
    public ICollection<HierarchicalEntity> ChildrenViaSecondaryParent { get; set; }
}

Answer 2

I have reproduced the problem now with EF 5. I get exactly the same exception and stack trace and also the exception in your EDIT 2 when applying Moho's code. The problem does not occur with EF 6. So, if upgrading to EF 6 is an option for you that would solve the problem.

If you need to stick with EF 5 using Fluent API mapping instead of using the [InverseProperty] attribute worked for me without exceptions. You can remove all attributes then:

public class SystemAccount
{
    public int ID { get; set; }

    public int? MasterAccountID { get; set; }
    public int? SecondMasterAccountID { get; set; }

    public virtual SystemAccount MasterAccount { get; set; }
    public virtual SystemAccount SecondMasterAccount { get; set; }

    public virtual List<SystemAccount> AllSubAccounts { get; set; }
    public virtual List<SystemAccount> SecondarySubAccounts { get; set; }
}

Relationship mapping with Fluent API:

modelBuilder.Entity<SystemAccount>()
    .HasOptional(s => s.MasterAccount)
    .WithMany(s => s.AllSubAccounts)
    .HasForeignKey(s => s.MasterAccountID);

modelBuilder.Entity<SystemAccount>()
    .HasOptional(s => s.SecondMasterAccount)
    .WithMany(s => s.SecondarySubAccounts)
    .HasForeignKey(s => s.SecondMasterAccountID);

The fact that the [InverseProperty] attribute with your model causes exceptions appears to be a bug in EF 5. The bug is most likely related to the self-referencing kind of the relationships because normally with relationships between different entities the attribute works without problems.