Dependencies with Entityframework C#

Question

My question is how to use the state-class include for parents, because the city-class has no connection with parents. How to perform this sql by loading the dependencies?

private void FrmCidade_Load(object sender, EventArgs e)
{
    var registros = Contexto.Cidades.Include("ESTADO").Include("PAIS").ToList();
}

Classes

[Serializable]
public class Pais : EntityBase
{
    public override long Handle { get; set; }
    public string Descricao { get; set; }
    public string Sigla { get; set; }
    public override DateTime DataCadastro { get; set; }
    public override DateTime? DataAlteracao { get; set; }
    public virtual ICollection<Estado> Estados { get; set; }
}

[Serializable]
public class Estado : EntityBase
{
    public override long Handle { get; set; }
    public string Descricao { get; set; }
    public string Sigla { get; set; }
    public long PaisHandle { get; set; }
    public virtual Pais Pais { get; set; }
    public virtual ICollection<Cidade> Cidades { get; set; }
    public override DateTime DataCadastro { get; set; }
    public override DateTime? DataAlteracao { get; set; }
}

[Serializable]
public class Cidade : EntityBase
{
    public override long Handle { get; set; }
    public string Descricao { get; set; }
    public string Sigla { get; set; }
    public long EstadoHandle { get; set; }
    public virtual Estado Estado { get; set; }        
    public override DateTime DataCadastro { get; set; }
    public override DateTime? DataAlteracao { get; set; }
}

Mapping with Fluentapi:

public class PaisMap : EntityBaseTypeConfiguration<Pais>
{
    public PaisMap() { }

    public override void ConfigureTableName()
    {
        ToTable("PAIS");
    }

    public override void ConfigurePrimaryKey()
    {
        HasKey(x => x.Handle)
            .Property(x => x.Handle).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.None);
    }

    public override void ConfigureForeingKeys()
    {

    }

    public override void ConfigureProperties()
    {
        Property(p => p.Descricao)
            .IsRequired()
            .HasMaxLength(150)
            .HasColumnName("DESCRICAO");

        Property(p => p.Sigla)
            .IsRequired()
            .HasMaxLength(3)
            .HasColumnName("SIGLA");
    }

    public override void ConfigureHasMany()
    {
        HasMany(p => p.Estados);
    }
}

public class EstadoMap : EntityBaseTypeConfiguration<Estado>
{
    public override void ConfigureTableName()
    {
        ToTable("ESTADO");
    }

    public override void ConfigurePrimaryKey()
    {
        HasKey(x => x.Handle)
            .Property(x => x.Handle).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.None);
    }

    public override void ConfigureForeingKeys()
    {
        HasRequired(p => p.Pais)
            .WithMany(p => p.Estados)
            .HasForeignKey(p => p.PaisHandle);
    }

    public override void ConfigureProperties()
    {
        Property(p => p.PaisHandle)
            .HasColumnName("PAISHANDLE");

        Property(p => p.Descricao)
            .IsRequired()
            .HasMaxLength(150)
            .HasColumnName("DESCRICAO");

        Property(p => p.Sigla)
            .IsRequired()
            .HasMaxLength(2)
            .HasColumnName("SIGLA");
    }

    public override void ConfigureHasMany()
    {
        HasMany(p => p.Cidades);

    }
}

public class CidadeMap : EntityBaseTypeConfiguration<Cidade>
{
    public override void ConfigureTableName()
    {
        ToTable("CIDADE");
    }

    public override void ConfigurePrimaryKey()
    {
        HasKey(x => x.Handle)
            .Property(x => x.Handle).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.None);
    }

    public override void ConfigureForeingKeys()
    {
        HasRequired(p => p.Estado)
            .WithMany(p => p.Cidades)
            .HasForeignKey(p => p.EstadoHandle);
    }

    public override void ConfigureProperties()
    {
        Property(p => p.EstadoHandle)
            .HasColumnName("ESTADOHANDLE");

        Property(p => p.Descricao)
            .IsRequired()
            .HasMaxLength(150)
            .HasColumnName("DESCRICAO");

        Property(p => p.Sigla)
            .IsRequired()
            .HasMaxLength(3)
            .HasColumnName("SIGLA");
    }

    public override void ConfigureHasMany()
    {

    }
}

Context class

public class ProjetoContext : DbContext
{
    public ProjetoContext()
        : base("ProjetoContext")
    {
        Configuration.LazyLoadingEnabled = false;            
        Configuration.ProxyCreationEnabled = false;
    }

    public DbSet<Pais> Paises { get; set; }
    public DbSet<Estado> Estados { get; set; }
    public DbSet<Cidade> Cidades { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {            
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
        modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();

        ConfigureMap(modelBuilder);

        modelBuilder.Properties<string>()
               .Configure(p => p.HasColumnType("varchar"));       
    }

    private void ConfigureMap(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new PaisMap());
        modelBuilder.Configurations.Add(new EstadoMap());
        modelBuilder.Configurations.Add(new CidadeMap());
    }    
}

Answer 1

Do so (Entity Framework 6):

using System.Data.Entity;

private void FrmCidade_Load(object sender, EventArgs e)
{
    var registros = Contexto.Cidades.Include(e => e.Estado.Select(y => y.Pais)).ToList();   
}

Or:

using System.Data.Entity;

private void FrmCidade_Load(object sender, EventArgs e)
{
    var registros = Contexto.Cidades.Include(e => e.Estado.Pais).ToList();   
}