EF Core foreign keys not working with existing database

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:

Installed Microsoft.EntityFrameworkCore.Proxies(2.1.2) via NuGet

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

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

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.

1 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.

virtual

See Loading Related Data

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.