Issue with EF not loading related entities when using Include()

[MattNorman]

I am using EF with a C# .NET 7 app.

For some reason, related entities are not being loaded when using the Include() method:

using var db = new ApexImContext();
var test = db.StorageRequestsVirtuals.Include(sr => sr.Reason)
    .Include(sr => sr.VirtualServer)
    .Include(sr => sr.Volume).ToList();

This has not been an issue for any of my other database entities. I have tried dropping and recreating the tables, however still no luck.

If I manually load the related entities using the Load() method for each record, I get the data back.

Has anyone had a similar issue and know what the cause may be? I want to avoid having to manually loop through the records and manually loading the related entities where possible.

 

[cjard]

Need more info. Show entity definitions and context setup code (OnModelCreating) if it's done fluently

 

[MattNorman]

I am using EF with a C# .NET 7 app.

For some reason, related entities are not being loaded when using the Include() method:

using var db = new ApexImContext();
var test = db.StorageRequestsVirtuals.Include(sr => sr.Reason)
    .Include(sr => sr.VirtualServer)
    .Include(sr => sr.Volume).ToList();

This has not been an issue for any of my other database entities. I have tried dropping and recreating the tables, however still no luck.

If I manually load the related entities using the Load() method for each record, I get the data back.

Has anyone had a similar issue and know what the cause may be? I want to avoid having to manually loop through the records and manually loading the related entities where possible.

Here are the relevant parts from the context. This is all auto generated via the package manager console command line. All of my other entities are fine and load the relational data when using Include().

public virtual DbSet<StorageRequestsVirtual> StorageRequestsVirtuals { get; set; }
public virtual DbSet<StorageReason> StorageReasons { get; set; }
public virtual DbSet<VirtualServer> VirtualServers { get; set; }

modelBuilder.Entity<StorageRequestsVirtual>(entity =>
{
    entity.HasKey(e => e.Id).HasName("PK__StorageR__3214EC07D86C7383");

    entity.ToTable("StorageRequestsVirtual");

    entity.Property(e => e.ChangeReference).HasMaxLength(10);
    entity.Property(e => e.Drive).HasMaxLength(10);
    entity.Property(e => e.NewDeployment).HasMaxLength(3);
    entity.Property(e => e.RequestDate).HasColumnType("datetime");
    entity.Property(e => e.Requestor).HasMaxLength(30);
    entity.Property(e => e.TempDriveRemoved).HasMaxLength(3);
    entity.Property(e => e.TempRequirement).HasMaxLength(3);

    entity.HasOne(d => d.Reason).WithMany(p => p.StorageRequestsVirtuals)
        .HasForeignKey(d => d.ReasonId)
        .OnDelete(DeleteBehavior.ClientSetNull)
        .HasConstraintName("FK_StorageRequestsVirtual_StorageReasons");

    entity.HasOne(d => d.VirtualServer).WithMany(p => p.StorageRequestsVirtuals)
        .HasForeignKey(d => d.VirtualServerId)
        .OnDelete(DeleteBehavior.ClientSetNull)
        .HasConstraintName("FK_StorageRequestsVirtual_VirtualServers");

    entity.HasOne(d => d.Volume).WithMany(p => p.StorageRequestsVirtuals)
        .HasForeignKey(d => d.VolumeId)
        .OnDelete(DeleteBehavior.ClientSetNull)
        .HasConstraintName("FK_StorageRequestsVirtual_VirtualVolumes");
});

modelBuilder.Entity<StorageReason>(entity =>
{
    entity.HasKey(e => e.Id).HasName("PK__StorageR__3214EC07D3D68DDB");

    entity.HasIndex(e => e.Name, "C_StorageReasonName").IsUnique();

    entity.Property(e => e.Name).HasMaxLength(100);
});

modelBuilder.Entity<VirtualServer>(entity =>
{
    entity.HasKey(e => e.Id).HasName("PK__VirtualS__3214EC07F7939D53");

    entity.HasIndex(e => e.Name, "C_VirtualServerName").IsUnique();

    entity.Property(e => e.Ipaddress)
        .HasMaxLength(15)
        .HasColumnName("IPAddress");
    entity.Property(e => e.Name).HasMaxLength(50);
    entity.Property(e => e.SqlclusterId).HasColumnName("SQLClusterId");
    entity.Property(e => e.SqlversionId).HasColumnName("SQLVersionId");

    entity.HasOne(d => d.Environment).WithMany(p => p.VirtualServers)
        .HasForeignKey(d => d.EnvironmentId)
        .OnDelete(DeleteBehavior.ClientSetNull)
        .HasConstraintName("FK_VirtualServers_Environments");

    entity.HasOne(d => d.Location).WithMany(p => p.VirtualServers)
        .HasForeignKey(d => d.LocationId)
        .OnDelete(DeleteBehavior.ClientSetNull)
        .HasConstraintName("FK_VirtualServers_Locations");

    entity.HasOne(d => d.OperatingSystem).WithMany(p => p.VirtualServers)
        .HasForeignKey(d => d.OperatingSystemId)
        .OnDelete(DeleteBehavior.ClientSetNull)
        .HasConstraintName("FK_VirtualServers_OperatingSystems");

    entity.HasOne(d => d.PatchingGroup).WithMany(p => p.VirtualServers)
        .HasForeignKey(d => d.PatchingGroupId)
        .OnDelete(DeleteBehavior.ClientSetNull)
        .HasConstraintName("FK_VirtualServers_PatchingGroups");

    entity.HasOne(d => d.Platform).WithMany(p => p.VirtualServers)
        .HasForeignKey(d => d.PlatformId)
        .OnDelete(DeleteBehavior.ClientSetNull)
        .HasConstraintName("FK_VirtualServers_Platforms");

    entity.HasOne(d => d.ServerType).WithMany(p => p.VirtualServers)
        .HasForeignKey(d => d.ServerTypeId)
        .OnDelete(DeleteBehavior.ClientSetNull)
        .HasConstraintName("FK_VirtualServers_ServerTypes");

    entity.HasOne(d => d.Sqlcluster).WithMany(p => p.VirtualServers)
        .HasForeignKey(d => d.SqlclusterId)
        .HasConstraintName("FK_VirtualServers_SQLClusters");

    entity.HasOne(d => d.Sqlversion).WithMany(p => p.VirtualServers)
        .HasForeignKey(d => d.SqlversionId)
        .HasConstraintName("FK_VirtualServers_SQLVersions");

    entity.HasOne(d => d.WindowsCluster).WithMany(p => p.VirtualServers)
        .HasForeignKey(d => d.WindowsClusterId)
        .HasConstraintName("FK_VirtualServers_WindowsClusters");
});

public partial class StorageRequestsVirtual
{
    public int Id { get; set; }

    public DateTime RequestDate { get; set; }

    public string ChangeReference { get; set; } = null!;

    public int VirtualServerId { get; set; }

    public string Drive { get; set; } = null!;

    public int VolumeId { get; set; }

    public string Requestor { get; set; } = null!;

    public int ReasonId { get; set; }

    public string NewDeployment { get; set; } = null!;

    public int AmountRequested { get; set; }

    public int AmountDelivered { get; set; }

    public string TempRequirement { get; set; } = null!;

    public string TempDriveRemoved { get; set; } = null!;

    public string? Notes { get; set; }

    public virtual StorageReason Reason { get; set; } = null!;

    public virtual VirtualServer VirtualServer { get; set; } = null!;

    public virtual VirtualVolume Volume { get; set; } = null!;
}

public partial class StorageReason
{
    public int Id { get; set; }

    public string Name { get; set; } = null!;

    public virtual ICollection<StorageRequestsVirtual> StorageRequestsVirtuals { get; set; } = new List<StorageRequestsVirtual>();
}

public partial class VirtualServer
{
    public int Id { get; set; }

    public string Name { get; set; } = null!;

    public int PlatformId { get; set; }

    public string? Ipaddress { get; set; }

    public int OperatingSystemId { get; set; }

    public int? SqlversionId { get; set; }

    public string Description { get; set; } = null!;

    public int? WindowsClusterId { get; set; }

    public int? SqlclusterId { get; set; }

    public int LocationId { get; set; }

    public int PatchingGroupId { get; set; }

    public int EnvironmentId { get; set; }

    public int ServerTypeId { get; set; }

    public virtual Environment Environment { get; set; } = null!;

    public virtual Location Location { get; set; } = null!;

    public virtual OperatingSystem OperatingSystem { get; set; } = null!;

    public virtual PatchingGroup PatchingGroup { get; set; } = null!;

    public virtual Platform Platform { get; set; } = null!;

    public virtual ServerType ServerType { get; set; } = null!;

    public virtual Sqlcluster? Sqlcluster { get; set; }

    public virtual Sqlversion? Sqlversion { get; set; }

    public virtual ICollection<StorageRequestsVirtual> StorageRequestsVirtuals { get; set; } = new List<StorageRequestsVirtual>();

    public virtual ICollection<VirtualServerDocument> VirtualServerDocuments { get; set; } = new List<VirtualServerDocument>();

    public virtual ICollection<VirtualServerNote> VirtualServerNotes { get; set; } = new List<VirtualServerNote>();

    public virtual WindowsCluster? WindowsCluster { get; set; }
}

 

[cjard]

I wasn't able to reproduce your complaint with the schema I manually setup to check:

Please provide an MDF I can attach that has a minimal set of tables, and at least one set of related data records, to make the given pared-down context work, and provide all the entities of the context. You may optionally supply the entire context unabridged together with an MDF based data extract if you don't want to reduce it (but you should check that the reduced context does exhibit the problem), and I'm happy to receive a zip password via PM if you'd prefer

 

[MattNorman]

I wasn't able to reproduce your complaint with the schema I manually setup to check:

View attachment 2765

View attachment 2766

View attachment 2768


Please provide an MDF I can attach that has a minimal set of tables, and at least one set of related data records, to make the given pared-down context work, and provide all the entities of the context. You may optionally supply the entire context unabridged together with an MDF based data extract if you don't want to reduce it (but you should check that the reduced context does exhibit the problem), and I'm happy to receive a zip password via PM if you'd prefer

Thanks for your time looking at this.

I have managed to find the issue which was that the specific data class for this entity was using the Include() extension method from System.Data.Entity instead of Microsoft.EntityFrameworkCore.

I set up a new console app with the database context and everything worked fine. I then copied all of the generated entities to my main project and the data was still missing. I then moved the data fetch to the app start up and noticed the two conflicting namespaces for the Include method.