Multiple one-to-many relationships with entity framework code first

Question

I am trying to form a relationship of 2 tables to a 3rd, on a 1 to many basis. I have the following code:

    public class CompanyInvolvement
    {
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    public DateTime StartDate { get; set; }

    public DateTime? EndDate { get; set; }

    public virtual Person Person { get; set; }
    public virtual Company Company { get; set; }
    }

    public class Person
    {
    public Person()
    {
        CompanyInvolvements = new Collection<CompanyInvolvement>();
    }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    [Required]
    public string ClientIdReference { get; set; }

    public virtual ICollection<CompanyInvolvement> CompanyInvolvements { get; set; }
    }


 public class Company
 {
    public Company()
    {
        Involvements = new Collection<CompanyInvolvement>();
    }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    [Required]
    public string ClientIdReference { get; set; }

    [Required]
    public string CompanyName { get; set; }

    public virtual ICollection<CompanyInvolvement> Involvements { get; set; }
  }

So effectively a Person can have many involvements in companies and a Company can have many people involved with it. The model builder is used like so:

modelBuilder.Entity<CompanyInvolvement>().HasRequired(x => x.Person).WithMany(x => x.CompanyInvolvements);
modelBuilder.Entity<CompanyInvolvement>().HasRequired(x => x.Company).WithMany(x => x.Involvements);

I originally created the relationship using the modelbuilder, specifying left and right keys (CompanyId and PersonId) and this worked great. But now I need the Start and End dates for an Involvement, I guess I needed to create a dedicated entity.

The question: When I use the above structure, I can create and read out involvements for a company and also see involvements for a Person. However, when I try to do the following:

var person = _context.People.FirstOrDefault(x => x.Id == personId);
var involvement = company.Involvements.FirstOrDefault(x => x.Person == person );
company.Involvements.Remove(involvement);
_context.SaveChanges();

I get the following error:

A relationship from the 'CompanyInvolvement_Company' AssociationSet is in the 'Deleted' state. Given multiplicity constraints, a corresponding 'CompanyInvolvement_Company_Source' must also in the 'Deleted' state.

I think my virtual properties in the 3 entities are correct, but I have the feeling the modelbuilder logic I have may be slightly misconfigured?

I cannot say definitively, but I strongly suspect that this is related to the absence of a foreign key property in the CompanyInvolvement class (say, CompanyId). Without foreign key properties, relationships become what are known as independent associations and have to be handled separately from the entities themselves. Theoretically, it is possible to create relationships in EF without foreign key properties but it becomes VERY difficult (trust me, I tried for a long time and eventually gave up). — Jason Richmeier

envio envio · Accepted Answer · 2014-12-11T21:44:42

I finally figured out what I was doing wrong. I needed to remove the Id property from the CompanyInvolvement entity and add the following composite key:

    [Key, Column(Order = 0)]
    public Guid PersonId { get; set; }

    [Key, Column(Order = 1)]
    public Guid CompanyId { get; set; }

I'm guessing by convention, these two properties were then linked as foreign keys to the Person and Company entities respectively. I also removed the modelbuilder mapping as stated in my original question. Once these were done, deleting CompanyInvolvements worked as expected.

Multiple one-to-many relationships with entity framework code first

1 Answers