Questions about relationship and mapping with Fluent API for EF 6

Question

Whenever I have a class with properties that are of the type of other classes, which at the database level represents a foreign key, I will always need the navigation properties?

And look at this example:

Revenda -> ClienteRevenda -> Empresa -> ClienteEmpresa

public class Revenda
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id {get; set;}
    public string Nome {get; set;}
    // Navigation Property
    public ICollection<ClienteRevenda> Clientes {get; set;}
}

public class ClienteRevenda
{
    [Key, Column(0)]
    public int RevendaId {get; set;}
    [ForeignKey("RevendaId")]
    public Empresa Empresa {get; set;}

    [Key, Column(1)]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id {get; set;}
    public string Nome {get; set;}
    // Navigation Property
    public ICollection<Empresa> Empresas {get; set;}
}

public class Empresa
{
    [Key, Column(0)]
    public int RevendaId {get; set;}
    [ForeignKey("RevendaId")]
    public Revenda Revenda {get; set;}

    [Key, Column(1)]
    public int ClienteRevendaId {get; set;}
    [ForeignKey("RevendaId, ClienteRevendaId")]
    public ClienteRevenda ClienteRevenda {get; set;}

    [Key, Column(2)]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id {get; set;}
    public string Nome {get; set;}
    // Navigation Property
    public ICollection<ClienteEmpresa> ClientesEmpresa {get; set;}
}

public class ClienteEmpresa
{
    [Key, Column(0)]
    public int RevendaId {get; set;}
    [ForeignKey("RevendaId")]
    public Revenda Revenda {get; set;}

    [Key, Column(1)]
    public int ClienteRevendaId {get; set;}
    [ForeignKey("RevendaId, ClienteRevendaId")]
    public ClienteRevenda ClienteRevenda {get; set;}

    [Key, Column(2)]
    public int EmpresaId {get; set;}
    [ForeignKey("RevendaId, ClienteRevendaId, EmpresaId")]
    public Empresa Empresa {get; set;}

    [Key, Column(3)]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id {get; set;}
    public string Nome {get; set;}
}

Note that I am making composite keys because I need unique records. In that case, I would need to add in the Resell Properties class to Empresa and ClienteEmpresa also?

What would this mapping look like with the Fluent API?

public class RevendaMap : EntityTypeConfiguration<Revenda>
{
    public RevendaMap()
    {
        HasKey(p => p.Id);
        Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    }
}

public class ClienteRevendaMap : EntityTypeConfiguration<ClienteRevenda>
{
    public ClienteRevendaMap()
    {
        HasKey(p => new { p.RevendaId, p.Id });
        Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        HasRequired(p => p.Revenda).WithMany(r => r.Clientes).HasForeignKey(p => p.RevendaId);
    }
}

public class EmpresaMap : EntityTypeConfiguration<Empresa>
{
    public EmpresaMap()
    {
        HasKey(p => new { p.RevendaId, p.ClienteRevendaId, p.Id });
        Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        HasRequired(p => p.Revenda).WithMany( ??? ).HasForeignKey(p => p.RevendaId);
        HasRequired(p => p.ClienteRevenda).WithMany(cr => cr.Empresas).HasForeignKey(p => p.ClienteRevendaId);
    }
}

public class ClienteEmpresaMap : EntityTypeConfiguration<ClienteEmpresa>
{
    public ClienteEmpresaMap()
    {
        HasKey(p => new { p.RevendaId, p.ClienteRevendaId, p.EmpresaId, p.Id });
        Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        HasRequired(p => p.Revenda).WithMany( ??? ).HasForeignKey(p => p.RevendaId);
        HasRequired(p => p.ClienteRevenda).WithMany( ??? ).HasForeignKey(p => p.ClienteRevendaId);
        HasRequired(p => p.Empresa).WithMany(e => e.ClientesEmpresa).HasForeignKey(p => p.EmpresaId);
    }
}

Note in the last two mapping classes that they have the method WithMany( ??? ). My question whether the class Revenda should have a navigation Property for each of the other classes and also asking on account of these other classes and methods. What should be informed in them?

Either the class structure is wrong, or the mapping is wrong...?

Answer 1

The co-relation between the properties should be something like this

//Revenda
public ICollection<ClienteRevenda> { get; set; }  //Navigation para os ClientesRevendas que possui

//ClienteRevenda
public Revenda Revenda { get; set; } //Navigation para a superior(que pertence)
public ICollection<Empresa> { get; set; }

//Empresa
public ClienteRevenda ClienteRevenda { get; set; } //Navigation para a superior
public ICollection<ClienteEmpresa> { get; set; }

//ClienteEmpresa
public Empresa Empresa{ get; set; } //Navigation para a superior

Answer 2

Chaves Compostas

The idea in no way improves the security or structure, even hindering the implementation of its Controllers and Views. All it takes is one Property as a primary key that other Id’s are accessible through the declared virtual classes. This use may even be considered a bad practice.

Modeling of Entities

public class Revenda
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id {get; set;}
    [Required]
    public string Nome {get; set;}

    public virtual ICollection<ClienteRevenda> Clientes {get; set;}
}

public class ClienteRevenda
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id {get; set;}
    public int RevendaId {get; set;}

    [Required]
    public string Nome {get; set;}

    public virtual Revenda Revenda {get; set;}

    public virtual ICollection<Empresa> Empresas {get; set;}
}

public class Empresa
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id {get; set;}
    public int ClienteRevendaId {get; set;}

    [Required]    
    public string Nome {get; set;}

    public virtual ClienteRevenda ClienteRevenda {get; set;}

    public virtual ICollection<ClienteEmpresa> ClientesEmpresa {get; set;}
}

public class ClienteEmpresa
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id {get; set;}
    public int EmpresaId {get; set;}

    [Required]
    public string Nome {get; set;}

    public virtual Empresa Empresa {get; set;}
}

Mappings

public class RevendaMap : EntityTypeConfiguration<Revenda>
{
    public RevendaMap()
    {
        HasKey(p => p.Id);
        Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    }
}

public class ClienteRevendaMap : EntityTypeConfiguration<ClienteRevenda>
{
    public ClienteRevendaMap()
    {
        HasKey(p => p.Id);
        Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        HasRequired(p => p.Revenda).WithMany(r => r.Clientes).HasForeignKey(p => p.RevendaId);
    }
}

public class EmpresaMap : EntityTypeConfiguration<Empresa>
{
    public EmpresaMap()
    {
        HasKey(p => p.Id);
        Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        HasRequired(p => p.ClienteRevenda).WithMany(cr => cr.Empresas).HasForeignKey(p => p.ClienteRevendaId);
    }
}

public class ClienteEmpresaMap : EntityTypeConfiguration<ClienteEmpresa>
{
    public ClienteEmpresaMap()
    {
        HasKey(p => new p.Id);
        Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        HasRequired(p => p.Empresa).WithMany(e => e.ClientesEmpresa).HasForeignKey(p => p.EmpresaId);
    }
}

Answer 3

@James has not to do with the answer itself of the question, but in another ready that you should pay attention to it, it is the way will work the load of these dependencies...

For example, if you need to access the first ClienteEmpresa of his first Empresa of his first ClienteRevenda of his Revenda in a foreach loop, it would be something more or less like this (in the most common say way of doing).

foreach(var item in ctx.Revendas) {
    var nome = item.Revenda
    .ClienteRevendas().FirstOrDefault()
    .Empresas().FirstOrDefault()
    .ClienteEmpresas().FirstOrDefault().Nome;
    //faz alguma coisa por exemplo
}

Depending on how your list is loaded, it can become very, very expensive for the system. Because for every .ClienteRevendas() and .Empresas() He’ll have to go back and forth from the server all the time.

There are several ways to load, you can give a Include or do the Linq to bring a dry consultation tbm...

In that link have some good practices when working with Entity, take a special look at Items 5,7,8 that involve well what I would like to spend here.