locked
An entity with two one-to-one links to another entity RRS feed

  • Question

  • Here is my situation.  I have an entity class which has two foreign key links into a second entity class.  One Coordinator is required, while the second coordinator is optional.  If the second coordinator does not exist, the SecondaryCoordinatorId value will be 0.  Here is the pared down code:

     

    <pre lang="x-c#">public class Record
    {
        [Required]
        public int CoordinatorId {get;set;}
    
        [Required]
        public int SecondaryCoordinatorId {get;set;}
       
        [Required]
        public virtual Coordinator Coordinator { get; set; }
    
        public virtual Coordinator SecondaryCoordinator { get; set; }
    
    }
    
    public class Coordinator
    {
        [Key]
        public int CoordinatorId { get; set; }
    
        public virtual ICollection<Record> Records { get; set; }
    }
    
    public class RecordContext : DbContext
    {
        public DbSet<Coordinator> Coordinators { get; set; }
        public DbSet<Record> Records{get;set;}
    
        modelBuilder.Entity<Record>().HasRequired(r => r.Coordinator).WithMany().HasForeignKey(r => r.CoordinatorId);
    
        modelBuilder.Entity<Record>().HasRequired(r => r.SecondaryCoordinator).WithMany().HasForeignKey(r => r.SecondaryCoordinatorId);
    
          
    }
    

     

    The problem is the line:

    public virtual ICollection<Record> Records { get; set; }

    I'm guessing that this has something to do with the Coordinator class not knowing whether I want Records with the Coordinator as the primary or secondary coordinator.  With that line in, when I try to load a Record with a Find() call.  The resulting SQL query is looking for a field named CoordinatorCoordinatorId which does not exist.  If I comment out that line, everything runs as expected and I can access the Coordinator or, if it exists, the SecondaryCoordinator.

    I figured I could create a SecondaryCoordinator entity class and map it to the same Coordinator table, and everything would probably work, but I haven't tried it because that seems to be a poor solution.  

    Any ideas?

     

    Tuesday, February 15, 2011 9:37 PM

Answers

  • Hi,

    You are spot on, the fluent API calls you have made all call .WithMany without specifying a navigation property. This means that Coordinator.Records isn't matched up with either of the the two relationships. This means you end up with a third relationship which where the CoordinatorCoordinatorId column comes from.

    If Records is part of the primary coordinator relationship then the fluent calls should look like this:

    modelBuilder.Entity<Record>().HasRequired(r => r.Coordinator).WithMany(c => c.Records).HasForeignKey(r => r.CoordinatorId);
    
    

     

    ~Rowan

    Monday, February 21, 2011 7:20 PM
    Moderator

All replies

  • Hi,

    You are spot on, the fluent API calls you have made all call .WithMany without specifying a navigation property. This means that Coordinator.Records isn't matched up with either of the the two relationships. This means you end up with a third relationship which where the CoordinatorCoordinatorId column comes from.

    If Records is part of the primary coordinator relationship then the fluent calls should look like this:

    modelBuilder.Entity<Record>().HasRequired(r => r.Coordinator).WithMany(c => c.Records).HasForeignKey(r => r.CoordinatorId);
    
    

     

    ~Rowan

    Monday, February 21, 2011 7:20 PM
    Moderator
  • Thanks, that fixed the problem.
    Tuesday, February 22, 2011 6:29 PM