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.

Chris Curtis · Accepted Answer · 2013-04-08 23:30:42Z

1

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);

answered Apr 8, 2013 at 23:30

Chris Curtis

1,4781 gold badge10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

John Adams Over a year ago

I will try this tomorrow when I get back to the office and I will let you know how it runs.

John Adams Over a year ago

I went with a slight variation of this solution. It worked, thank you!

Kirsten Over a year ago

What was the slight variation?

Komengem · Accepted Answer · 2013-04-08 22:38:23Z

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.

edited Apr 8, 2013 at 22:38

answered Apr 8, 2013 at 22:33

Komengem

3,7747 gold badges35 silver badges57 bronze badges

9 Comments

Kirsten Over a year ago

how do you handle the cascade delete?

Komengem Over a year ago

@kirsteng Entity Framework ensures that Objects are created with cascade delete. Follow the link on the matter stackoverflow.com/questions/5520418/…

Kirsten Over a year ago

Oops, i meant how do you turn cascade delete off?

Komengem Over a year ago

@kirsteng change public int PersonId {get; set;} to public int PersonId? {get; set;} makes the foreign keys to allow null. Never tested it but logically makes sense.

Kirsten Over a year ago

true, but I don't think that affects cascade deletes

|

Kirsten · Accepted Answer · 2013-04-10 13:18:27Z

0

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.

edited Apr 10, 2013 at 13:18

answered Apr 8, 2013 at 22:42

Kirsten

18.8k52 gold badges213 silver badges368 bronze badges

8 Comments

Komengem Over a year ago

I made some changes to your answer since you asked for improvement. The reason why you are getting an extra Person_Id1 column in the PersonContact table is because you didn't map PersonId foreign key in your POCO class. Also the approach below would solve this issue

Kirsten Over a year ago

Thanks, I usually seem to be able to get away without defining FK's explicitly in the POCO class. Should I always define them explicitly or only in scenarios like this?

Komengem Over a year ago

You either do property mapping or fluent mapping. In the question, he tried fluent mapping and in my answer belong i did property mapping which i prefere, easier to follow in my case. Note: The edit in your answer wasn't approved but you can relate to my answer

Kirsten Over a year ago

I cant see where to approve your edit - did you delete it? I am actually using a bindingList instead of a list in my class. Could that be the cause of the extra field? I tried adding the FK - but the extra field is generated. The extra field is not generated if I use your code.

Komengem Over a year ago

Mhmmm looks like mod didn't allow my edit, look into my answer on this question.

|

Collectives™ on Stack Overflow

Code first mapping for self-related xref table

3 Answers 3

3 Comments

9 Comments

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

9 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related