Questions about relationship and mapping with Fluent API for EF 6

2

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

And see 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 this case, would I need to add in the Reseller class the Navigation Properties for Empresa and ClienteEmpresa too?

How would this mapping be 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 WithMany( ??? ) method. My question is if the Revenda class should have a navigation property for each of the other classes and also asking for these other classes and methods. What should be informed about them?

Or is it the class structure that is wrong, or the mapping that is wrong ...?

    
asked by anonymous 19.03.2014 / 18:43

3 answers

4

The relationship between the properties should look 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
    
19.03.2014 / 19:51
2

Composite Keys

The idea in no way improves the security or structure, making it difficult even to implement your Controllers and Views . Just one property as the primary key is that the other Id's are accessible through the declared virtual classes. This use may even be considered a bad practice.

Entity Modeling

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);
    }
}
    
19.03.2014 / 20:21
1

@Tiago does not have to do with the answer itself of the question, but in another, you should pay attention to this, it is the way the load of these dependencies will work ...

For example, if you need to access the first ClienteEmpresa of your first Empresa of your first ClienteRevenda of your Revenda within a foreach loop, it would be something like this (in the do).

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 loads, it can become very, very, but very costly for the system. Because for every .ClienteRevendas() and .Empresas() it will have to stay on and off the server at all times.

There are several ways to load, you can give Include or make Linq to bring a shorter query tbm ...

In this link has 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.

    
19.03.2014 / 21:42