Relationship of Fluentapi Tables

Question

I am trying to make a relationship of tables, using Fluentapi with Entity Framework, in which my purpose are 3 entities:

public class Empresa
{
    public Empresa() 
    {
        this.Gerentes = new HashSet<Gerente>();
    }

    public int EmpresaId { get; set; }
    public string EmpresaNome { get; set; }

    public virtual ICollection<Gerente> Gerentes { get; set; }
}

public class Gerente
{
    public Gerente()
    {
        this.Empresas = new HashSet<Empresa>();
    }

    public int GerenteId { get; set; }
    public string GerenteNome { get; set; }

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

public class Projeto
{
    public int ProjetoId { get; set; }
    public string ProjetoNome { get; set; }
}

Problem:

A manager can be a manager in more than one company, but a project only belongs to a manager in the specific company.

About the relationship Company x Manager, I reached this result:

modelBuilder.Entity<Empresa>()
    .HasMany<Gerente>(s => s.Gerentes)
    .WithMany(c => c.Empresas)
    .Map(cs =>
            {
                cs.MapLeftKey("EmpresaId");
                cs.MapRightKey("GerenteId");
                cs.ToTable("EmpresaGerente");
            });

Now my problem is how to relate the project to the manager in that company.

Answer 1

If the project belongs to a manager in a specific company, I would do it:

public class Projeto
{
    public int ProjetoId { get; set; }
    public string ProjetoNome { get; set; }
    public int EmpresaId { get; set; }
    public int GerenteId { get; set; }
    public virtual Empresa Empresa { get; set; }
    public virtual Gerente Gerente { get; set; }
}

modelBuilder.Entity<Projeto>()
    .HasRequired(p => p.Gerente)
    .WithMany()
    .HasForeignKey(p => p.GerenteId);

modelBuilder.Entity<Projeto>()
    .HasRequired(p => p.Empresa)
    .WithMany()
    .HasForeignKey(p => p.EmpresaId);

I omitted the navigation Property in the methods WithMany() to simplify, but probably you would create a public virtual ICollection<Projeto> in class Gerente and in class Empresa and would include the lambda expression according to both WithMany(), although this is optional.

However, this in itself does not prevent you from assigning a company that is not linked to the manager of that project. This control should be in your application (or in your class, depending on how you work). A simple example just to give you an idea:

public class CadastraProjetoRequest
{
    public string ProjetoNome { get; set; }
    public int EmpresaId { get; set; }
    public int GerenteId { get; set; }
}

public void CadastraProjeto(CadastraProjetoRequest request)
{
    using(var ctx = new MeuDbContext())
    {
        bool gerenteDaEmpresa = ctx.Empresas.Any(e => e.EmpresaId == request.EmpresaId && e.Gerentes.Any(g => g.GerenteId == request.GerenteId));

        if (!gerenteDaEmpresa)
            throw new InvalidOperationException("Essa empresa não possui esse gerente!");

        // criar e salvar o projeto...

    }
}

Of course, if you are allowed to change the manager or company of a project, that rule would have to be validated there as well. If you prefer, you can create a method in the class to assign the manager and company and validate there (then it would be encapsulated in the class, and it would make no difference if it were creation or change). The downside of this is that you would have to read the object enterprise of your DbContext making Include(e => e.Gerentes) (or enabling the Lazy load), what would cost reading more database data (as the previous form using Any() right in the DbContext has a better performance), but then it’s up to you to decide.