Code first mapping for self-related xref table

Question

I have read through several threads on StackOverflow and have not been able to figure this out. I am hoping someone can offer some advice. I have some POCO classes that look like this:

Person
{
  int PersonCode {get; set;}
  ...
  virtual List<PersonContact> {get; set;}
}

PersonContact
{
  int PersonPersonCode {get; set;}
  int ContactPersonCode {get; set;}
  int PersonContactTypeCode {get; set;}

  virtual PersonContactType {get; set;}
  virtual Person Person {get; set;}    // not sure I really need this one
  virtual Person Contact {get; set;}
}

Each Person record will have zero to many PersonContact records. Each PersonContact record links one Person record to one other Person record and indicates the type of relationship between the two Person records with the PersonContactTypeCode.

I need to be able to map this so that a Person record can be navigated to his related PersonContact records. Something like this:

var john = new Person(...);
var david = new Person(...);
john.PersonContacts.Add(new PersonContact
  {
    Contact = david,
    PersonContactType = ... // manager
  });

and then

john.PersonContacts
  .Where(c => c.PersonContactType.PersonContactTypeCode == "manager")
  .FirstOrDefault();

would return

david

I have tried so many combinations of Data Annotations and Fluent API that I can hardly remember where I started. I seemed to have the best luck with this combination:

modelBuilder.Entity<Person>()
    .HasMany(entity => entity.PersonContacts)
        .WithRequired(person => person.Person)
        .HasForeignKey(xref => xref.PersonPersonCode)
        .WillCascadeOnDelete(false);

modelBuilder.Entity<Person>()
    .HasMany(entity => entity.PersonContacts)
        .WithRequired(xref => xref.Contact)
        .HasForeignKey(entity => entity.ContactPersonCode)
        .WillCascadeOnDelete(false);

But, when I try to add more than one PersonContact to a Person, I get this error:

Multiplicity constraint violated. The role 'Person_PersonContacts_Source' of the
relationship '...Entities.Person_PersonContacts' has multiplicity
1 or 0..1.

I really appreciate any help, I am just completely stumped right now. By the way, I am open to changing these POCOs if necessary.

Answer 1

Try this:

public class Person
{
   [Key]
   [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   public int PersonId {get; set;}
   ...
   public virtual ICollection<PersonContact> PersonContacts {get; set;}
}


public class PersonContact
{
   [Key]
   [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   public int ContactId {get; set;}

   [ForeignKey("Person"), DatabaseGenerated(DatabaseGeneratedOption.None)]
   public int PersonId {get; set;}       

   public virtual Person Person {get; set;}         
}

I have used Property Mapping instead of Fluent Mapping like you tried in your attempt. If you have any questions let me know. As far as the relationship between your two Entities, this is what you need.

Answer 2

I'd guess it's because you are using the same navigation property to link to PersonContact.Person and PersonContact.Contact.

Assuming this:

Person
{
  int PersonCode {get; set;}
  ...
  virtual ICollection<PersonContact> PersonContacts {get; set;}
}

Try something like:

modelBuilder.Entity<PersonContact>()
    .HasRequired(x => x.Person)
        .WithMany(x => x.PersonContacts)
        .HasForeignKey(x => x.PersonPersonCode)
        .WillCascadeOnDelete(false);

modelBuilder.Entity<PersonContact>()
    .HasRequired(x => x.Contact)
        .WithMany()
        .HasForeignKey(x => x.ContactPersonCode)
        .WillCascadeOnDelete(false);

Answer 3

I managed to get something similar working using something like this

in my domain classes

public class PersonContact
{
    public int Id { get; set; }

    [Required]
    public virtual Person ContactPerson { get; set; }

    [Required]
    public virtual Person Person { get; set; }

[Required]
public int ContactType { get; set; }

}

public class Person
{
int Id { get; set; }

    private readonly  List<PersonContact> _contacts = new  List<Contact>();

    public virtual  List<PersonContact> Contacts
    {
        get
        {
            return this._contacts;
        }
    }
}

in my context

 public DbSet<Person> People { get; set; }
 public DbSet<PersonContact> Contacts { get; set; }

I made this change in a migration , and had to edit the generated create table code to set Cascade delete to false for the fwo Foreign Keys to the person table, inside the PersonContact Table.

I get an extra Person_Id1 column in the PersonContact table. It seems to populate with the same data as Person_Id. This seems to be needed by EF when I create a bindingsource - as I get errors without it.

I wouldn't put explicit Foreign keys in, let the migration create them.