Understanding behavior of one-to-many relationship in NPGSQL and Entity Framework

Question

I have two PostgreSQL tables, "stock_appreciation_rights" and "sar_vest_schedule" which are mapped to the classes "StockAppreciationRights" and "SarVestingUnit". The relationship is this: One SarVestingUnit is associated with one StockAppreciationRights, and one StockAppreciationRight is associated with many SarVestingUnit. Here is the SarVestingUnit class:

public class SarVestingUnit
{
    public string UbsId { get; set; }
    public short Units { get; set; }
    public DateTime VestDate { get; set; }
    public virtual StockAppreciationRights Sar { get; set; }
}

Here is the StockAppreciationRights class:

public class StockAppreciationRights
{
    public StockAppreciationRights()
    {
        SarVestingUnits = new HashSet<SarVestingUnit>();
    }

    public string UbsId { get; set; }
    public DateTime GrantDate { get; set; }
    public DateTime ExpirationDate { get; set; }
    public short UnitsGranted { get; set; }
    public decimal GrantPrice { get; set; }
    public short UnitsExercised { get; set; }
    public virtual ICollection<SarVestingUnit> SarVestingUnits { get; set; }
}

And a snippet from my dbContext:

modelBuilder.Entity<SarVestingUnit>(entity =>
{
    entity.HasKey(e => new { e.UbsId, e.VestDate })
        .HasName("sar_vest_schedule_pkey");

    entity.ToTable("sar_vest_schedule");

    entity.Property(e => e.UbsId)
        .HasColumnName("ubs_id")
        .HasColumnType("character varying");

    entity.Property(e => e.VestDate)
        .HasColumnName("vest_date")
        .HasColumnType("date");

    entity.Property(e => e.Units).HasColumnName("units");

    entity.HasOne(d => d.Sar)
        .WithMany(p => p.SarVestingUnits)
        .HasForeignKey(d => d.UbsId)
        .OnDelete(DeleteBehavior.ClientSetNull)
        .HasConstraintName("sar_vest_schedule_ubs_id_fkey")
        .IsRequired();
});

Here is the behavior I don't understand. If I get just the StockAppreciationRights from my dbcontext like this:

var x = new personalfinanceContext();
//var test = x.SarVestingUnit.ToList();
var pgSARS = x.StockAppreciationRights.ToList();

The SarVestingUnits collection is empty in my StockAppreciationRights objects. Likewise, if I just get the SarVestingUnits and not the StockAppreciationRights like this:

var x = new personalfinanceContext();
var test = x.SarVestingUnit.ToList();
//var pgSARS = x.StockAppreciationRights.ToList();

The Sar property in my SarVestingUnit objects is null. However, if I get them both like this:

var x = new personalfinanceContext();
var test = x.SarVestingUnit.ToList();
var pgSARS = x.StockAppreciationRights.ToList();

Then everything is populated as it should be. Can someone explain this behavior? Obviously I'm new to entity framework and PostgreSQL.

Answer 1

From your described behaviour it seems that lazy loading is either disabled or not available (I.e. EF Core <=2.1)

Lazy loading would assign proxies for related references that it hasn't loaded so that if those are later accessed, a DB query against the DbContext would be made to retrieve them. This allows data to be retrieved "as required" but can result in a significant performance penalty.

Alternatively you can eager-load related data. For example:

var pgSARS = context.StockAppreciationRights.Include(x => x.SarVestingUnits).ToList();

would tell EF to load the StockAppreciation rights and pre-fetch any vesting units for each of those entries. You can use Include and ThenInclude to drill down and pre-fetch any number of dependencies.

The reason your final example appears to work is that EF will auto-populate relations that the context knows about. When you load the first set, it won't resolve any of the related entities, however, loading the second set and EF already knows about the related other entities and will auto-set those references for you.

The real power of EF though is not dealing with entities like a 1-to-1 mapping to tables (outside of editing/inserting) but rather leveraging projection to pull relational data to populate what you need. Leveraging Select or Automapper's ProjectTo methods mean you can extract whatever data you want through the mapped EF relationships and EF can build you an efficient query to fetch it rather than worrying about lazy or eager loading. For instance if you want to get a list of vesting units with their associated right details:

var sars = context.StockAppreciationRights.Select(x => new SARSummary
{
    UbsId = x.UbsId,
    Units = x.Units,
    VestDate = x.VestDate,
    GrantDate = x.Sar.GrantDate,
    ExpirationDate = x.Sar.ExpirationDate,
    UnitsGranted = x.Sar.UnitsGranted,
    GrantPrice = x.Sar.GrantPrice
}).ToList();

From within the Select statement you can access any of the related details to flatten into a view model that serves your immediate needs, or even compose a hierarchy of view models simplified for what the view/consumer needs.