Why Entity Framework Core one to many relationship creates only left join?. IsRequired is not working

Question

I decided to use Entity Framework Core for my Web API. I have a problem with one to many relationship. Let's say we have Person and PersonAddress entities. The entities related as one to many relation.

public class Person
{
  public int Id { get; set;}
  public string Name { get; set;}
  
  //Navigation property
  public ICollection<PersonAddress> Addresses { get; set;}
}

public class PersonAddress
{
  public int Id { get; set;}
  public string Street { get; set;}
  public int PersonId { get; set;}
  
  //Navigation property
  public Person Person {get;set;}
}

And I configurated the entities in DbContext OnModelCreating method as following

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  modelBuilder.Entity<PersonAddress>()
                .HasOne(x => x.Person)
                .WithMany(x => x.PersonAddresses)
                .IsRequired(true)
                .HasForeignKey(x => x.PersonId);
}

Problem is that when I try to get Person inclueded PersonAddress entityframework created query with left join. But I want to create with inner join. My code and query is like that:

 var result = _dbContext.Persons.
                .Include(x => x.PersonAddresses)
                .ToList();

SELECT `l`.`*
FROM `Person` AS `l`
LEFT JOIN 
`PersonAddresses` AS `l0`
 ON `l`.`Id` = `t`.`PersonId`

I tried a lot of way to solve the problem but it was not solved. Does anyone faced the problem? How can I solve it?

I use Microsoft.EntityFrameworkCore 5.0.5 version

Answer 1

Entity Framework created query with left join.

It's fundamental to the relational database concept. There's no way to enforce by database constraints that a Person has at least one PersonAddress. (Unless you want to reverse the association for one address, but that's a different story having its own issues).

Neither does the mapping API have any formal instructions to map a 1:1..n relationship, which would required client-side validation when saving data.

The only thing EF can do is: simply return what you ask. You ask _dbContext.Persons so you get them all. The Include doesn't tell that you only want persons with addresses. It tells that you want all persons with their addresses (0..n) included.

How can I solve it?

1. by adding a predicate:

_dbContext.Persons.
    .Where(p => p.PersonAddresses.Any())
    .Include(x => x.PersonAddresses)
    .ToList()

Which gets the correct data by OUTER JOIN + WHERE but is inferior to an inner join.

2. Manually write an inner join and group in memory

    var result = (
        from p in _dbContext.Persons
        join pa in _dbContext.Addresses on p.Id equals pa.PersonId
        select new { p, pa }
    ).AsEnumerable()
    .GroupBy(r => r.p)
    .Select(r => r.Key);

This creates a query with inner join as you want, but of course it's quite a hassle. The switch to memory (or client-side evaluation) is necessary because EF can't translate the GroupBy.

This question made me coin an IncludeInner method as EF feature request.