How to join information whose relationship is repeated between two tables using EF Core

Question

I have a double relationship between the Person and PersonCompany tables where a Person can be any individual or legal person or a Person who is registered as a Company. When I need to fetch a Person (Person table) with Id 2 from the bank, the EF should return the People and PersonsCompany that relate to the Person table, but this is not happening ... I believe the problem occurs because the Person and PersonCompany properties are from same type as Person. This makes EF understand that they are the same thing and returns values that do not match the related PersonCompany.

Do I have to do some sort of "Select" within the PersonsCompan navigation property? Does anyone know how to help me?

//Get value of table Person
public Pessoa GetById(int id)
        {


            return DbSet
                .Include(pe => pe.Persons)
                    .ThenInclude(p => p.Person)
                .Include(pe => pe.PersonsCompany)
                    .ThenInclude(pe => pe.PersonCmpany)

                //(... other related tables )

                .FirstOrDefault(x => x.PersonId == id);
                
         }

public void Configure(EntityTypeBuilder<PersonEntity> builder)
        {
            builder.ToTable("PersonEntity");

            builder.HasKey(pg => new { pg.PersonId, pg.PersonType});

            builder
               .HasOne(p => p.Person)
               .WithMany(pg => pg.Persons)
               .HasForeignKey(pg => pg.PersonId)
               .OnDelete(DeleteBehavior.ClientSetNull);

            builder.Property(pg => pg.PersonId)
               .HasColumnName("PersonId")
               .HasColumnType("integer")
               .IsRequired();

            builder.Property(pg => pg.PersonType)
               .HasColumnName("PersonTypeId")
               .HasColumnType("integer")
               .IsRequired();

            builder.Property(pg => pg.IdGeneral)
               .HasColumnName("IdGeneral")
               .HasColumnType("integer")
               .IsRequired();

            builder
              .HasOne(f => f.PersonCompany)
              .WithMany(pg => pg.PersonsCompany)
              .HasForeignKey(pg => pg.PersonCompanyId)
              .OnDelete(DeleteBehavior.ClientSetNull);

            builder.Property(pg => pg.PersonCompanyId)
               .HasColumnName("PersonCompanyId")
               .HasColumnType("integer")
               .IsRequired();
           
        }

 public class Person : Entity
 {
 public virtual ICollection<PersonEntity> Persons { get; private set; }
 public virtual ICollection<PersonEntity> PersonsCompany { get; private set; }
 }

 public class PersonEntity
    {
        public int Id { get; private set; }
        public int PersonId { get; private set; }
        public int PersonCompanyId { get; private set; }

        public virtual PersonType PersonType { get; private set; }
        public virtual Person Person { get; private set; }
        public virtual Person PersonCompany { get; private set; }
 }

Please post the entity classes in question and relevant fluent configuration if any. — Ivan Stoev, Apr 14 '18 at 11:32

Ivan Stoev · Answer 1 · 2018-06-12T11:40:44.700

If I understand correctly, the problem is similar to Entity Framework Core: many-to-many self referencing relationship, so is the solution.

The confusion in your case comes from the collection navigation property names and their mappings to the reference navigation properties:

Person.Persons -> PersonEntity.Person

and

Person.PersonsCompany -> PersonEntity.PersonCompany

You should probably rename them like this:

Person.PersonCompanies -> PersonEntity.Person

and

Person.CompanyPersons -> PersonEntity.PersonCompany

so the other reference navigation property represents the intended link.

E.g.

Model:

public class Person : Entity
{
    public virtual ICollection<PersonEntity> PersonCompanies { get; private set; }
    public virtual ICollection<PersonEntity> CompanyPersons { get; private set; }
}

Relationship configuration:

builder
   .HasOne(p => p.Person)
   .WithMany(pg => pg.PersonCompanies)
   .HasForeignKey(pg => pg.PersonId)
   .OnDelete(DeleteBehavior.ClientSetNull);

builder
  .HasOne(f => f.PersonCompany)
  .WithMany(pg => pg.CompanyPersons)
  .HasForeignKey(pg => pg.PersonCompanyId)
  .OnDelete(DeleteBehavior.ClientSetNull);

Usage:

.Include(p => p.PersonCompanies)
    .ThenInclude(pe => pe.PersonCompany)
.Include(p => p.CompanyPersons)
    .ThenInclude(pe => pe.Person)

Thank you for your help @Ivan Stoev! Now I have another problem with the return of the GetById () method. If I get a Person with Id = 2 and need to return all PersonEntities related to Person he should bring them, but he gets lost and returns a list of PersonEntities where PersonCompanies with Id = 2 ... It seems like he is confused with Person — Master JR, Apr 17 '18 at 19:18
It seems that I need to modify the method where the Includes () are so it does not mess with the Person and PersonCompany navigation properties. — Master JR, Apr 17 '18 at 19:21

How to join information whose relationship is repeated between two tables using EF Core

1 Answers1