EF Core foreign keys not working with existing database

Question

I am building a NET Core MVC app that consumes an existing MS SQL database. Primary keys and foreign keys are already established and working correctly at the database level.

I followed the example in this article and used package manager console to reverse engineer the models and database context from the database. This seemed to work well. It resulted in all models being added to my app's Models folder including a robust database context class. The problem I'm having is that relational information about these entities isn't being populated at runtime. I'm getting nulls for related entities for which foreign keys are established both in the database and in the fluent API code generated by the scaffolding process.

I have two tables, Mode and Submode, that are related via foreign key.

Scaffolding generated these two classes for the above two tables:

public partial class Submode
{
    public Submode()
    {
        Contact = new HashSet<Contact>();
    }

    public int Id { get; set; }
    public int ModeId { get; set; }
    public string Code { get; set; }
    public bool Visible { get; set; }
    public bool IsDefault { get; set; }

    public Mode Mode { get; set; }
    public ICollection<Contact> Contact { get; set; }
}

public partial class Mode
{
    public Mode()
    {
        Contact = new HashSet<Contact>();
        Submode = new HashSet<Submode>();
    }

    public int Id { get; set; }
    public string Code { get; set; }
    public bool Visible { get; set; }
    public bool IsDefault { get; set; }

    public ICollection<Contact> Contact { get; set; }
    public ICollection<Submode> Submode { get; set; }
}

Scaffolding also generated this fluent API snippet in the database context:

            modelBuilder.Entity<Submode>(entity =>
        {
            entity.HasIndex(e => e.Code)
                .HasName("UQ__Submode__A25C5AA75D2A9AE7")
                .IsUnique();

            entity.Property(e => e.Code)
                .IsRequired()
                .HasMaxLength(100)
                .IsUnicode(false);

            entity.HasOne(d => d.Mode)
                .WithMany(p => p.Submode)
                .HasForeignKey(d => d.ModeId)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_Submode_ModeId");
        });

Every example I've read on setting foreign keys with fluent API show a similar pattern to the above snippets. But Mode comes back null for Submode.

Null at runtime

And I get a null reference exception in the returned view because I'm trying to display properties of the related Mode object. Am I missing some configuration or is there a problem with the scaffolded code?

UDPATE - as requested, here's the implementation that's fetching data from the database context.

public class SQLSubModeData : ISubModeData
{
    private w4lkrContext _context;

    public SQLSubModeData(w4lkrContext context)
    {
        _context = context;
    }

    public IQueryable<Submode> Get()
    {
        return _context.Submode.OrderBy(p => p.Id);
    }

    public Submode Get(int id)
    {
        return _context.Submode.FirstOrDefault(p => p.Id == id);
    }
}

UPDATE (SOLVED) - Enabling lazy loading fixed the problem. Three steps got me there:

1. Installed Microsoft.EntityFrameworkCore.Proxies(2.1.2) via NuGet

2. Updated Startup.cs -> AddDbContext() method, as follows:

   services.AddDbContext(options => options.UseLazyLoadingProxies().UseSqlServer(_configuration.GetConnectionString("W4LKR")));

3. Made all navigation properties virtual. This had to be done on every model in the app, not just the one being called in my example above. Errors are thrown if even one is left out.

Answer 1

But Mode comes back null for Submode.

Since your Navigation Properties aren't declared as virtual, you have disabled Lazy Loading, so EF will only populate your Navigation Properties if you do Eager Loading, or Explicit Loading.

See Loading Related Data