C# - Entity Framework Code first, lazy loading not working

Question

I have some issues with Entity Framework 6 code-first with a SQL Server database. I have an existing database, and I want to use them with Entity Framework. With Visual Studio 2017, I add a new item of type ADO.NET Entity Data Model to generate the code-first classes for the first time.

Once the classes are generated, I start to use the objects to interact with the database. Here I have some issues with lazy loading. Some navigation properties are null. The navigation properties are marked with virtual and lazy loading and ProxyCreationEnabled are true.

These screenshots illustrate the problem:

I can share the classes generated by Visual Studio:

public partial class ETRFContext : DbContext
{
    static string conn = "My connection string";

    public ETRFContext() : base(conn)
    {
    }

    public virtual DbSet<Acessos> Acessos { get; set; }
    public virtual DbSet<Caracteristicas> Caracteristicas { get; set; }
    public virtual DbSet<CircuitoAprovacoes> CircuitoAprovacoes { get; set; }
    public virtual DbSet<EstadosEstudo> EstadosEstudo { get; set; }
    public virtual DbSet<Estudos> Estudos { get; set; }
    public virtual DbSet<GruposAcesso> GruposAcesso { get; set; }
    public virtual DbSet<HistoricoDetalhadoEstudo> HistoricoDetalhadoEstudo { get; set; }
    public virtual DbSet<MembrosGruposAcesso> MembrosGruposAcesso { get; set; }
    public virtual DbSet<Normas> Normas { get; set; }
    public virtual DbSet<Paises> Paises { get; set; }
    public virtual DbSet<PrecedenciasEstudos> PrecedenciasEstudos { get; set; }
    public virtual DbSet<PrecedenciasProgramas> PrecedenciasProgramas { get; set; }
    public virtual DbSet<Programas> Programas { get; set; }
    public virtual DbSet<Projetos> Projetos { get; set; }
    public virtual DbSet<RegistosCAD> RegistosCAD { get; set; }
    public virtual DbSet<TiposEstadoEstudo> TiposEstadoEstudo { get; set; }
    public virtual DbSet<TiposMensagem> TiposMensagem { get; set; }
    public virtual DbSet<TiposProjeto> TiposProjeto { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Caracteristicas>()
            .Property(e => e.Chave)
            .IsFixedLength();

        modelBuilder.Entity<Caracteristicas>()
            .Property(e => e.Valor)
            .IsFixedLength();

        modelBuilder.Entity<Estudos>()
            .HasMany(e => e.EstadosEstudo)
            .WithRequired(e => e.Estudos)
            .HasForeignKey(e => e.idEstudo);

        modelBuilder.Entity<Estudos>()
            .HasMany(e => e.HistoricoDetalhadoEstudo)
            .WithRequired(e => e.Estudos)
            .HasForeignKey(e => e.idEstudo);

        modelBuilder.Entity<Estudos>()
            .HasMany(e => e.PrecedenciasEstudos)
            .WithRequired(e => e.Estudos)
            .HasForeignKey(e => e.idEstudo);

        modelBuilder.Entity<Estudos>()
            .HasMany(e => e.PrecedenciasEstudos1)
            .WithRequired(e => e.Estudos1)
            .HasForeignKey(e => e.idEstudoPrecedencia)
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<Estudos>()
            .HasMany(e => e.RegistosCAD)
            .WithRequired(e => e.Estudos)
            .HasForeignKey(e => e.idEstudo);

        modelBuilder.Entity<GruposAcesso>()
            .HasMany(e => e.Acessos)
            .WithRequired(e => e.GruposAcesso)
            .HasForeignKey(e => e.idGruposAcesso);

        modelBuilder.Entity<GruposAcesso>()
            .HasMany(e => e.MembrosGruposAcesso)
            .WithRequired(e => e.GruposAcesso)
            .HasForeignKey(e => e.idGrupoAcesso);

        modelBuilder.Entity<Paises>()
            .HasMany(e => e.Projetos)
            .WithOptional(e => e.Paises)
            .HasForeignKey(e => e.id_Pais);

        modelBuilder.Entity<Programas>()
            .HasMany(e => e.Acessos)
            .WithRequired(e => e.Programas)
            .HasForeignKey(e => e.idPrograma);

        modelBuilder.Entity<Programas>()
            .HasMany(e => e.CircuitoAprovacoes)
            .WithRequired(e => e.Programas)
            .HasForeignKey(e => e.idPrograma);

        modelBuilder.Entity<Programas>()
            .HasMany(e => e.Estudos)
            .WithRequired(e => e.Programas)
            .HasForeignKey(e => e.idProgramas);

        modelBuilder.Entity<Programas>()
            .HasMany(e => e.PrecedenciasProgramas)
            .WithRequired(e => e.Programas)
            .HasForeignKey(e => e.idProgramaPrecedencia);

        modelBuilder.Entity<Programas>()
            .HasMany(e => e.PrecedenciasProgramas1)
            .WithRequired(e => e.Programas1)
            .HasForeignKey(e => e.idPrograma)
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<Projetos>()
            .HasMany(e => e.Caracteristicas)
            .WithRequired(e => e.Projetos)
            .HasForeignKey(e => e.id_projeto);

        modelBuilder.Entity<Projetos>()
            .HasMany(e => e.Estudos)
            .WithRequired(e => e.Projetos)
            .HasForeignKey(e => e.idProjetos);

        modelBuilder.Entity<TiposEstadoEstudo>()
            .HasMany(e => e.EstadosEstudo)
            .WithRequired(e => e.TiposEstadoEstudo)
            .HasForeignKey(e => e.idEstado);

        modelBuilder.Entity<TiposMensagem>()
            .HasMany(e => e.HistoricoDetalhadoEstudo)
            .WithRequired(e => e.TiposMensagem)
            .HasForeignKey(e => e.idTipoMensagem);

        modelBuilder.Entity<TiposProjeto>()
            .HasMany(e => e.Programas)
            .WithRequired(e => e.TiposProjeto)
            .HasForeignKey(e => e.idTiposProjeto);

        modelBuilder.Entity<TiposProjeto>()
            .HasMany(e => e.Projetos)
            .WithRequired(e => e.TiposProjeto)
            .HasForeignKey(e => e.id_Tipo)
            .WillCascadeOnDelete(false);
    }
}

Now this is the class Estudos:

public partial class Estudos
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Estudos()
    {
        EstadosEstudo = new HashSet<EstadosEstudo>();
        HistoricoDetalhadoEstudo = new HashSet<HistoricoDetalhadoEstudo>();
        PrecedenciasEstudos = new HashSet<PrecedenciasEstudos>();
        PrecedenciasEstudos1 = new HashSet<PrecedenciasEstudos>();
        RegistosCAD = new HashSet<RegistosCAD>();
    }

    public int id { get; set; }
    public int idProjetos { get; set; }
    public int idProgramas { get; set; }

    [Required]
    [StringLength(25)]
    public string NomeEstudo { get; set; }

    [Required]
    [StringLength(5)]
    public string Utilizador { get; set; }

    public DateTime DataInicial { get; set; }

    public DateTime? DataFinal { get; set; }

    public bool Producao { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<EstadosEstudo> EstadosEstudo { get; set; }

    public virtual Programas Programas { get; set; }

    public virtual Projetos Projetos { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<HistoricoDetalhadoEstudo> HistoricoDetalhadoEstudo { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<PrecedenciasEstudos> PrecedenciasEstudos { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<PrecedenciasEstudos> PrecedenciasEstudos1 { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<RegistosCAD> RegistosCAD { get; set; }
}

And the class Projetos:

public partial class Projetos
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Projetos()
    {
        Caracteristicas = new HashSet<Caracteristicas>();
        Estudos = new HashSet<Estudos>();
    }

    public int id { get; set; }

    [Required]
    [StringLength(15)]
    public string projeto { get; set; }

    public int id_Tipo { get; set; }

    [StringLength(50)]
    public string Cliente { get; set; }

    [StringLength(50)]
    public string Designacao { get; set; }

    public int? id_Pais { get; set; }

    public int? CalculoEletrico { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Caracteristicas> Caracteristicas { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Estudos> Estudos { get; set; }

    public virtual Paises Paises { get; set; }

    public virtual TiposProjeto TiposProjeto { get; set; }
}

One thing that I noticed, is when I query the Database for a "Estudo" I don't get the navigation property "Projetos", but when I ask the database for a "Projeto" I get the navigation property "Estudos".

Another thing is that when I query the database for a "Estudo" I don't get the navigation property "Projetos", but if then I query the database for a "Projeto" where id = Estudo.idProjetos, the navigation property "Projetos" in "Estudos" that was null before, automatically fills up with the value of associated "Projeto".

So, anyone have some tips to solve this issue?

Appreciate your help.

Answer 1

In your Estudos entity, can you add ForeignKey attribute to Programas and Projetos properties.

[ForeignKey("idProgramas")]
public virtual Programas Programas { get; set; }

[ForeignKey("idProjetos")]
public virtual Projetos Projetos { get; set; }

And when querying Estudos include Programas and Projetos.

db.Estudos
  .Include(x => x.Programas)
  .Include(x => x.Projetos)
  .ToList();