I am using EF Core 3.1.3 and I am have these two queries:
var test = await _unitOfWork.Context.PersonEmail
.OrderBy(e => e.EmailId)
.Take(10)
.Include(e => e.EmailRecord)
.ToListAsync();
var test2 = await _unitOfWork.Context.PersonEmail
.OrderBy(e => e.EmailId)
.Take(10)
.ToListAsync();
I was expecting both queries to return 10 results, but for 6 of the items from the test
query to have null
for the EmailRecord
property (since the EmailRecord table does not contain an entry for those particular items). However what actually happens is that the test
query returns only 4 results, whilst the test2
query returns the expected 10.
The entities are declared like this:
[Table("PersonEmail")]
public class PersonEmail
{
public Guid PersonId { get; set; }
public Guid EmailId { get; set; }
[ForeignKey("PersonId, EmailId")]
public EmailRecord EmailRecord { get; set; }
}
[Table("EmailRecord")]
public class EmailRecord
{
public Guid PersonId { get; set; }
public Guid EmailId { get; set; }
public DateTimeOffset LastUpdated { get; set; }
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<PersonEmail>().HasKey(x => new { x.PersonId, x.EmailId });
modelBuilder.Entity<EmailRecord>().HasKey(x => new { x.PersonId, x.EmailId });
}
It's because of the (improper) relationship configuration.
The way you have defined it
[ForeignKey("PersonId, EmailId")]
in PersonEmail
makes it one-to-one relationship with PersonEmail
being the dependent (because of the FK) and EmailRecord
being the principal.
If that was true, then "since the EmailRecord table does not contain an entry for those particular items" simply cannot happen due to the enforced FK constraint. And because of that, EF Core 3.0+ Include
is generating correctly inner join
, which should not filter the main set, but in fact does filter it because of the actual data relationship in the database.
To fix it, you have to reflect the actual relationship, which seems to be other way around - one-to-one with principal PersonEmail
and dependent EmailRecord
.
Remove the above [ForeignKey]
attribute and add the following fluent configuration:
modelBuilder.Entity<PersonEmail>()
.HasOne(x => x.EmailRecord)
.WithOne()
.HasForeignKey<EmailRecord>(x => new { x.PersonId, x.EmailId });
Now the Include
will use left outer join
and won't filter the PersonEmail
records.
For more info, see Relationships - One-to-one EF Core documentation.