Navigation property are null when querying parent entity

Question

Below is my base class for Domain entities:

public interface IBaseEntity
{
     public int Id { get; set; }
     
     public DateTime CreatedDate { get; set; }
     
     public DateTime UpdatedDate { get; set; }
}


public class BaseEntity : IBaseEntity
{
     public int Id { get; set; }
     
     public DateTime CreatedDate { get; set; }
     
     public DateTime UpdatedDate { get; set; }
}

public class ExternalSystem : BaseEntity
{
   public string Name { get; set; } 
   public string ConnectionUrl { get; set; } 
   public ICollection<ExternalSystemRules> ExternalSystemRules { get; set; } 
}

public ExternalSystemRules : BaseEntity
{
     public string RuleName { get; set; } 
     
     public string ConfiguredBy { get; set; } 
     
     public int ExternalSystemId { get; set; } 
     
     public ExternalSystem ExternalSystem { get; set; } 
     
     public ICollection<TaskSchedular> TaskSchedulars { get; set; } 
}

public class ExternalSystemConfiguration : IEntityTypeConfiguration<ExternalSystem>
{
    public void Configure(EntityTypeBuilder<ExternalSystem> builder)
    {
        builder.ToTable("ExternalSystem");
        builder.Property(e=>e.Id).HasColumnName("ExternalSystemId");
        builder.HasKey(e=>e.Id);
    }
}


public class ExternalSystemRulesConfiguration : IEntityTypeConfiguration<ExternalSystemRules>
{
    public void Configure(EntityTypeBuilder<ExternalSystemRules> builder)
    {
        builder.ToTable("ExternalSystemRules");
        builder.Property(e=>e.Id).HasColumnName("ExternalSystemRuleId");
        builder.HasKey(e=>e.Id);
        builder.HasOne(d=>d.ExternalSystem)
               .WithMany(p=>p.ExternalSystemRules)
               .HasForeignKey(p=>p.ExternalSystemId)
               .HasConstraintName("FK_ExternalSystemRules_ExternalSystemId");
               
        builder.Navigation(p=>p.ExternalSystem)
                .IsRequired()
                .AutoInclude();
    }
}


public class MyDatabaseContext : DbContext
{
   private readonly IConfiguration _configuration;
   public MyDatabaseContext(IConfiguration configuration)
   {
        _configuration = configuration;
         Database.EnsureCreated(); 
   }
   public DbSet<ExternalSystem> ExternalSystem {get; set; }
   public DbSet<ExternalSystemRules> ExternalSystemRule {get; set; }
   public void Save()
   {
        this.SaveChanges();
   }
}

I already had existing database created so I created all this domain models and configuration based on existing database tables and relationships.

Now when I am trying to get list of ExternalSystems like below :

var myDatabaseContext = new MyDatabaseContext();
var externalSystems = myDatabaseContext.ExternalSystem.ToList();

This returns the list of ExternalSystems but my "ExternalSystemRules" navigation property is null. All the other related child entities are null as well.

Now, I don't want to explicitly keep on using .Include() to load related entities. I want to use default feature of entity framework core of eager loading other related entities when querying parent entity.

What might be the problem here?

Database table:

ExternalSystem:
ExternalSystemId(PK)  Name   ConnectionUrl 

ExternalSystemRules:
ExternalSystemRuleId(PK)   RuleName   ConfiguredBy    ExternalSystemId(F.K)

Answer 1

This isn't a problem with your configuration, but how you query your data. You should manually include the relational record / navigation properties, you are looking for:

var externalSystems = myDatabaseContext
        .ExternalSystem
        .Include(es => es.ExternalSystemRules)
        .ToList();

I would advise against using AutoInclude, though (*). While it might not seem to make a difference, when you have tens or hundreds of objects to query, performance will degrade very rapidly, once your dataset grows. Everytime you query a set with AutoInclude enabled, you will get all its navigation properties with it, whether you need it, or not. In addition, this will also apply, to all entity-types derived from this entity. If you decide to use it anyway, you can disable it for single queries by using .IgnoreAutoIncludes().

A problem common to both approaches (Include(), as well as AutoInclude()) are hierachies. They will work for very simple models, but once you try to map a hierachy - think of something like a tree, where a Rule can have sub-rules of the same type, you might run into problems with self-referencing loops and need to manually project.

There is a nice article explaining the problem here: https://khalidabuhakmeh.com/ef-core-and-aspnet-core-cycle-issue-and-solution

A better way of querying your data, would be to use "view models", as to avoid returing unused or sensitive data to your clients.

public class ExternalSytemVm
{
    public int Id {get; set;}
    public IEnumerable<ExternalSystemRulesVm> Rules {get; set;}
    /* ...*/
}

public class ExternalSytemRulesVm
{
    public int Id {get; set;}
    public string Name {get; set;}
    /* ...*/
}

var externalSystems = myDatabaseContext
            .ExternalSystem
            .Select(es => new ExternalSystemVm {
                Id = es.Id
                Rules = es.ExternalSystemRules.Select(esr => {
                    /* ... */
                })
             })
            .ToList();

(*) If you are absolutely sure, that you will always need all of your properties, with all of the navigation properties in each and every query in your application, this might be fine.

Documentation: https://learn.microsoft.com/en-us/ef/core/querying/related-data/eager

Answer 2

Add the AutoInclude() to your Fluent API configuration as follows (similar to what you already have for ExternalSystemRules):

public class ExternalSystemConfiguration : IEntityTypeConfiguration<ExternalSystem>
{
    public void Configure(EntityTypeBuilder<ExternalSystem> builder)
    {
        builder.ToTable("ExternalSystem");
        builder.Property(e=>e.Id).HasColumnName("ExternalSystemId");
        builder.HasKey(e=>e.Id);

        builder.Navigation(e => e.ExternalSystemRules)
           .AutoInclude();
    }
}