Entity Framework Core one-to-zero-or-one not binding included relations when querying

Question

I have an Entity Framework Core code-first database with a one-to-one-or-zero relationship set up like this:

public class PrimaryRow 
{
    public int Id { get; set; }
    public string Value { get; set; }

    public OptionalRow { get; set; }
}

public class OptionalRow 
{
    public int Id { get; set; }
    public string OptionalValue { get; set; }

    public int PrimaryRowId { get; set; }
    public PrimaryRow PrimaryRow { get; set; }
}

public class MyContext : DbContext 
{
    public DbSet<PrimaryRow> PrimaryRows { get; set; }
    public DbSet<OptionalRow> OptionalRows { get; set; }
}

This creates the following tables:

CREATE TABLE "PrimaryRows" 
(
    "Id" INTEGER NOT NULL CONSTRAINT "PK_PrimaryRows" PRIMARY KEY AUTOINCREMENT,
    "Value" TEXT NULL
)

CREATE TABLE "OptionalRows" 
(
    "Id" INTEGER NOT NULL CONSTRAINT "PK_OptionalRows" PRIMARY KEY AUTOINCREMENT,
    "OptionalValue" TEXT NULL,
    "PrimaryRowId" INTEGER NOT NULL,
    CONSTRAINT "FK_OptionalRows_PrimaryRows_PrimaryRowId" FOREIGN KEY ("PrimaryRowId") REFERENCES "PrimaryRows" ("Id") ON DELETE CASCADE
)

I query the data using this code:

var rows = await MyContext.PrimaryRows
                          .Include(p => p.OptionalRow)
                          .ToListAsync();

I can see that this generates the following SQL:

SELECT "p"."Id", "p"."Value", "p0"."Id", "p0"."OptionalValue", "p0"."PrimaryRowId"
FROM "PrimaryRows" AS "p"
LEFT JOIN "OptionalRows" AS "p0" ON "p"."Id" = "p0"."PrimaryRowId"

Which does indeed include the optional relational data.

However, when I look at the contents of rows the OptionalRow property is NULL! So the querying works, but the data binding doesn't.

What am I missing here?

Answer 1

A couple of things to check and eliminate.

First would be to eliminate the obvious: Is it using the same database you expect at runtime? Often a different database connection string is preserved in a config file that at runtime it ends up executing on a database you don't expect. You are capturing the SQL

Second, A typical cause for data in one-to-one relationships not coming back as expected has to do with the default way EF expects to associate one-to-one relationships, by PK to PK. Identifying this issue can be obfuscated in attempts to provide a simpler example in a question.

So for instance with:

public class PrimaryRow 
{
    public int Id { get; set; }
    public string Value { get; set; }

    public OptionalRow? OptionalRow { get; set; }
}

public class OptionalRow 
{
    public int Id { get; set; }
    public string OptionalValue { get; set; }

    public int PrimaryRowId { get; set; }
    public PrimaryRow PrimaryRow { get; set; }
}

By default EF when set up with a:

modelBuilder.Entity<PrimaryRow>()
    .HasOne(x => x.OptionalRow)
    .WithOne(x => x.PrimaryRow);

... it is going to try and join these rows on the Id PK in both tables. This can result in the wrong OptionalRow coming back, or no OptionalRow. When configuring with a dedicated FK column you need to tell EF which property on what entity to use:

modelBuilder.Entity<PrimaryRow>()
    .HasOne(x => x.OptionalRow)
    .WithOne(x => x.PrimaryRow)
    .HasForeignKey<OptionalRow>(x => x.PrimaryRowId);

This would show up in the real captured SQL which would have:

LEFT JOIN "OptionalRows" AS "p0" ON "p"."Id" = "p0"."Id" // PK to PK

.. rather than

LEFT JOIN "OptionalRows" AS "p0" ON "p"."Id" = "p0"."PrimaryRowId" // PK to FK

... in the case that you might have inadvertently substituted the SQL from the real tables in your project for your example naming without noticing it was joining on the PK.