Entity Framework Core .Include() is filtering the query

.net c# entity-framework entity-framework-core

Question

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 });
}
1
1
3/25/2020 4:25:14 PM

Accepted Answer

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.

1
3/25/2020 5:06:05 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow