0

I am absolutely stumped in trying to figure out how to implement a bi-directional 1..n relationship in Entity Framework using Code First. For example, a team (represented by a Team entity) has a coach and a manager (both represented by a Person entity). So, my Team model could be as follows:

public class Team
{
    public Team()
    {
        Manager = new Person();
        Coach = new Person();
    }

    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int TeamID { get; set; }
    public string Name { get; set; }

    [ForeignKey("Manager")]
    public int ManagerID { get; set; }
    public virtual Person Manager { get; set; }

    [ForeignKey("Coach")]
    public int CoachID { get; set; }
    public virtual Person Coach { get; set; }
}

I can implement one-way navigation by implementing the Person Entity as follows:

public class Person
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int PersonID { get; set; }
    public string Name { get; set; }
}

and the fluent API:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Team>()
        .HasRequired(t => t.Manager);

    modelBuilder.Entity<Team>()
        .HasRequired(t => t.Coach);

    base.OnModelCreating(modelBuilder);
}

However, while that allows me to navigate from the Team entity to the related Coach and Manager (both instances of Person), it doesn't allow me to directly navigate from the Coach or Manager to the related Team. So, to implement 2-way navigation, I modified the Person entity as follows:

public class Person
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int PersonID { get; set; }
    public string Name { get; set; }

    [ForeignKey("Team")]
    public int TeamID { get; set; }
    public virtual Team Team { get; set; }
}

While that builds ok, I get the following runtime error when I try to save to the database:

System.Data.Entity.Core.UpdateException
InnerException: Unable to determine a valid ordering for dependent operations. Dependencies may exist due to foreign key constraints, model requirements, or store-generated values.

So, to specify the ordering between the entities, I tried to modify the fluent API by adding the "WithRequiredPricipal" as follows:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Team>()
        .HasRequired(t => t.Manager)
        .WithRequiredPrincipal(t => t.Team);

    modelBuilder.Entity<Team>()
        .HasRequired(t => t.Coach)
        .WithRequiredPrincipal(t => t.Team);

    base.OnModelCreating(modelBuilder);
}

However, when I attempt to execute "add-migration" in the Package Manager Console, I get the following error:

System.InvalidOperationException: The navigation property 'Team' declared on type 'RelatedEntities.Models.Person' has been configured with conflicting foreign keys.

What I'm attempting to achieve seems like a straightforward requirement but I've done heaps of searching for a solution without yet finding an answer. Am I missing something in the fluent API or the annotations?

(I don't want to implement a workaround, such as implementing separate models for Coach and Manager because a team may have numerous other roles (eg, Assistant Coach, Public Relations Manager, etc). I would want each to simply be an instance of a Person entity.)

Harry Smith
  • 55
  • 5

2 Answers2

0

You didn't define your foreign key constraints.

.HasRequired(t => t.Manager)
.WithMany()
.HasForeignKey(t => t.ManagerId)

As a side note. If your manager was managing multiple teams the WithMany would be .WithMany(m => m.Teams) and your manager model would need something like this:

private ICollection<Team> _teams
public ICollection<Team> Teams 
{
    get { return _teams ?? (teams = new List<Team>()); }
    protected set { _teams = value; }
}

Sorry for formatting. On my phone. Good luck.

Yuliam Chandra
  • 14,494
  • 12
  • 52
  • 67
trevorc
  • 3,023
  • 4
  • 31
  • 49
  • Thank you for your suggestion, @nameEqualsPNamePrube, but that is not the issue (see my next comment below). The fluent API that you propose is just an alternative way of specifying what I had already specified using the "ForeignKey" annotations in my sample code (above); the fluent API and the annotations produce the identical Up() method of the DbMigration-derived class that is generated in response to the "add-migration" command in the Package Manager Console. – Harry Smith Sep 04 '14 at 04:07
  • The issue that I'm trying to solve is that Team and Person end up with foreign keys pointing to each other and Entity Framework can't tell which one of Team and Person is the principal and which is the dependent - ie, Team contains a Person and Person contains a Team. That's where I thought "WithRequiredPrincipal" comes into play but, as mentioned in my original question, that results in Entity Framework complaining of conflicting foreign keys. Without specifying the principal/dependent relationship, I get runtime errors (as described above) when I attempt to save the entities. – Harry Smith Sep 04 '14 at 04:13
  • I'm pretty sure that is not possible. Besides a Team contains 'People' and a Person 'belongs' to a team. I think I would look at revamping your data model first. EF can't tell who is the principle and dependent because your model inherently doesn't say either. – trevorc Sep 04 '14 at 17:10
  • No, what I'm trying to do is valid. If you don't like the hypothetical Team example that I used to demonstrate the problem, image Marriage. A Marriage isn't simply a collection of people. A Marriage has specific entities of Husband and Wife, both of type Person. It should be possible to navigate in both directions as in Marriage.Husband and Husband.Marriage. It turns out that EF has a recognized shortcoming. See: https://entityframework.codeplex.com/workitem/142. The EF Triage Team has flagged it to be addressed in a future release. I need to implement a workaround in the meantime – Harry Smith Sep 05 '14 at 07:25
0

Ok, I can now answer my own question in case anyone else encounters the same problem.

Firstly, as I mention in my comment above, the problem that I described in my question turns out to be a recognized shortcoming in Entity Framework that the EF Triage Team has flagged to be addressed in a future release of EF.

In the meantime, the workaround that has been suggested by a number of contributors in response to questions about how to implement circular dependencies in EF (which is what my example above is trying to do) is to do it in stages as follows:

  1. Create the principal entity
  2. Call SaveChanges() on DbContext
  3. Create the dependent entity and set the foreign key before calling SaveChanges() again (at some subsequent point)

So, using the Team example in my original question above, instead of creating the dependent Manager and Coach entities in the Team constructor, the first change was to make the Team's foreign keys to the Coach and Manager optional (instead of required), so that the Team could be instantiated without the Coach and Manager, and then call SaveChanges():

Team team = Teams.Add(new Team());
SaveChanges();

After that, I then create the dependent Manager and Coach entities and set their foreign keys to the ID of the Team instance:

team.Manager = new Person();
team.Manager.TeamID = team.TeamID;

team.Coach = new Person();
team.Coach.TeamID = team.TeamID;

At any time after that, SaveChanges() can be called without causing a runtime error as previously.

When this issue is addressed in a future release of EF, it should be possible to create the principal and dependent entities without having to call SaveChanges() in between.

Harry Smith
  • 55
  • 5