Entity Framework - Updated relationship does not retrieve data created from the old relationship

I recently updated my Entity Framework (version 6.2.0) mapping to improve the performance of my application but without modifying the database schema. Basically, I had a One-to-Many relationship I turned into a One-to-One relationship.

When I create new objects with this new mapping, everything works as expected: the objects are saved in the database with the right values in the foreign key colums, etc. When I restart my application (and reset the DbContext) and access to those objects, the navigation properties of my entities are fulfilled.

The problem occurs when I try to access objects that were created before this EF mapping update. With those old objects, the navigation properties are null and I can't access to the other end of the relationship.

Concretely, I have two tables defined like this:

 ______           _____________
|Sample|         |Sample_Result|
|------|         |-------------|
|Id    |<--------|Result_Of    |
|______|  Fk     |_____________|

From a business perspective, a sample can only have one result. But we used to have it mapped in Entity Framework as a One-to-Many relation ship as follow:

[Table("Sample")]
public class SampleEntity
{
    public virtual IList<SampleResultEntity> Results { get; set; }
}

[Table("Sample_Result")]
public class SampleResultEntity
{
    [Required]
    [Column("Result_Of")]
    public Guid ResultOfFk { get; set; }

    [ForeignKey(nameof(ResultOfFk))]
    public virtual SampleEntity ResultOf { get; set; }
}

We had terrible performance with this mapping so I recently update it to a One-to-One relationship:

[Table("Sample")]
public class SampleEntity
{
    public virtual SampleResultEntity Result { get; set; }
}

[Table("Sample_Result")]
public class SampleResultEntity
{
    [Required]
    [Column("Result_Of")]
    public Guid ResultOfFk { get; set; }

    public virtual SampleEntity ResultOf { get; set; }
}

And in my dbContext, I added the following mapping:

 modelBuilder.Entity<SampleResultEntity>()
            .HasRequired(sr => sr.ResultOf)
            .WithRequiredDependent(s => s.Result);

When I access to samples created after the mapping update, sample.Result is defined.

When I access to samples created before the mapping update, sample.Result is null.

When I query the database with an inner join over the two tables, like this:

SELECT *
  FROM Sample s
  INNER JOIN Sample_Result sr ON s.Id = sr.Result_Of;

I don't "lose" rows (if I have 40 samples, I get 40 rows). So the foreign keys are well set.

Can someone explain me this behavior?

Solution

You will probably need to outline your complete table structure because from what you describe you cannot simply change a mapping from 1-to-many to 1-to-1 based on a schema.

A 1-to-many schema will look something like this:

Sample
SampleID (PK)

SampleResult
SampleResultID (PK)
SampleID (FK)

A 1-to-1 schema will look like: Sample SampleID (PK)

SampleResult
SampleID (PK + FK)

So, in a 1-to-many schema containing effectively 1 to 1 rows you would have Sample ID=1 with a SampleResult with SampleResultID = 16 and SampleID = 1. If you were to simply change the mapping to 1-to-1, EF would expect the PKs on both tables to equate. Since SampleResult's PK is SampleResultId, getting the right record is impossible as it'd be comparing Sample.SampleId /w SampleResult.SampleResultId (not SampleResult.SampleId)

Your new test records will appear to work because what you would probably find would be:

Sample.SampleID = **220**

SampleResult.SampleResultID = **220**
SampleResult.SampleID = 220

If you were using Identity (Int) columns for keys this problem would have manifested somewhat differently where you'd either be getting empty links, or getting links to incorrect results. Because you are using GUIDs and each ID will be relatively universally unique, the result will be empty links.

I would recommend using a Profiler on your test database to capture the SQL being used when loading a Sample/Result pair to verify the correct columns are being joined.

Without changing the schema, as long as SampleResult.SampleResultId is set up as an Identity / Default, and you can guarantee that there are no 1-to-many instances (multiple results on a single sample) you should be able to update the mapping in EF to treat the SampleId on the SampleResult as the Key. This way, with your new mapping EF will link the 2 together. This will mean your previous "new" test records will no longer work because they would have been marrying the Sample.SampleID with the SampleResult.SampleResultID, but your original records should work, and newer records should work as well. EF's Key does not have to match the PK on the table, so long as you are using DB-First (no migrations) and the schema is satisfied. If the DB column for SampleResultId defaults to NewSequentialId() or NewId() then you shouldn't need to worry about it. If the PK is set in code then you may need to map the property as a regular column and make a note to use the SampleId. Where this can "break" is if you have any other entities linking to SampleResult based on SampleResultId. EF will be considering the SampleId as the Key when joining.

On a final note: I'm a bit wary about the conclusion that a 1-to-many relationship between Sample and SampleResult led to performance issues that would be resolved by re-mapping to a 1-to-1. While it's somewhat misleading to have a 1-to-many relationship that only is meant to contain 1 child, in the end EF will be using an Inner Join in both cases. I would strongly suspect something else is at play. GUID keys can be problematic as systems grow if you are using NewId() / Guid.New for new rows. This is because the relative randomness of the 128 bit key in a Clustered Index can lead to considerable fragmentation in the index tables. It is better to use NewSequentialId() or a code-based implementation of NewSequentialId() to generate unique, but sort-able GUIDs that minimize this effect, and combine that with regular index maintenance on the database tables.