Entity Framework Core: related data missing for every other result with SQLite

Question

I have an odd issue when retrieving data from the database. This is a project that is being upgraded from .NET Core 1.0 towards .NET Core 2.1. Everything was working beforehand, but the upgrade has some strange side effects when loading related data. The exact versions used are 2.1 for .NETCore.app; 2.1.4 for AspNetCore, 2.1.4 for EntityFrameworkCore(.Sqlite).

The problem can be expressed using the following models. Upon insertion, first a Match is added without the related Result entities. Then, each Result is created and finally the Match is updated again with these instances. This is the reason for the nullable ID fiels in Match.

public class Result
{
  public int Id { get; set; }
  public int MatchId { get; set; }
  public Match Match { get; set; }
}

public class Match
{
  public int Id { get; set; }
  public int? HomeId { get; set; }
  public int? AwayId { get; set; }
  public Result Home { get; set; }
  public Result Away { get; set; }
}

What happens is when I retrieve data (for example, context.Results.Include(r => r.Match)) and inspect the results, every other result misses the related data. This only seems to happen with these tables, so my guess is something goes astray due to the bidirectionality of the models.

The inspector shows a result like below. It should be noted that sets of two consecutive results always points towards the same Match.

results - [0] Id = 1000 Match = Null - [1] Id = 1001 Match = <Match object> - [2] Id = 1002 Match = Null - [3] Id = 1003 Match = <Match object>

And so on. I have troubles inserting data into these tables as well (similar issues pop up), but let's keep that out of the scope for this question at this point.

Answer 1

To answer my own question: the problem was in the relation mapping. There are several related questions that happened to match mine, but often the suggestion lies in creating additional fields to better describe the mapping. As the database schema was not to be changed this was not an option.

As a side note, this issue has not a lot to do with SQLite. What is odd however is that things used to work as described in the question. I did not look into what caused this, but it looks like either Entity Framework of the SQLite binaries handled this case different. Not saying it was a bug, but that being the main difference between the versions makes it likely it had at least something to do with this.

That said, the solution. The ModelBuilder originally had the following definitions:

modelBuilder.Entity<Match>()
  .HasOne(m => m.Home)
  .WithMany()
  .HasForeignKey(m => m.HomeId);

modelBuilder.Entity<Match>()
  .HasOne(m => m.Away)
  .WithMany()
  .HasForeignKey(m => m.AwayId);

modelBuilder.Entity<Result>()
  .HasOne(r => r.Match)
  .WithOne()
  .HasForeignKey<Result>(r => r.MatchId);

One issue with this that the WithMany mappings from Match were incorrect. Changing this to WithOne - although more correct - made no difference to the problem. It was fixed by removing the relation mapping from Result to Match. Apparently this silently mapped the reverse relation, choosing one of the two relations towards Result. As (I assume) Entity Framework takes care of the reverse relation once one end was specified, this could (should) be left out to fix things and preserve all navigations.

For completeness, the final ModelBuilder definition:

modelBuilder.Entity<Match>()
  .HasOne(m => m.Home)
  .WithOne()
  .HasForeignKey(m => m.HomeId);

modelBuilder.Entity<Match>()
  .HasOne(m => m.Away)
  .WithOne()
  .HasForeignKey(m => m.AwayId);