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.

Use Include in your query to tell it which navigation properties you want populated. — juharr
– juharr, Commented Aug 22, 2020 at 0:59
@juharr, yes, this solve my issue, many thanks. But you can explain me why in this case I need to put the Include to fill up the navigation properties? — Marco Teixeira
– Marco Teixeira, Commented Aug 22, 2020 at 9:52
@MarcoTeixeira Basically EF doesn't populate navigation properties unless you tell it to to avoid a potentially more complex and expensive query. If all I want is the data from one table having it pull all the data from tables it has relations with and the tables that those tables have relations with and so on could make the query take much longer and return much more data than is needed. — juharr
– juharr, Commented Aug 22, 2020 at 14:10

Jeethendra · Accepted Answer · 2020-08-22 02:06:08Z

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();

answered Aug 22, 2020 at 2:06

Jeethendra

3561 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related