Map Many to Many in the Entity Framework

Question

I’m having a problem mapping with the Entity Framework.

I have two models, "project" and "company", where in the "company" there can be several "projects".

 public class Projeto
 {
    [Key]
    public int Id { get; set; }
    public string Nome { get; set; }
    public string Descricao { get; set; }
    public byte[] Versao { get; set; }
 }


public class Empresa
{
    [Key]
    public Int32   Id                              { get;set; }  
    public String  Nome                            { get;set; } 
    public String  Fone                            { get;set; } 
    public String  Contato                         { get;set; } 
    public String  Email                           { get;set; } 
    public virtual ICollection<Projeto>  Projetos  { get;set; } 
}

When, I execute on Package Manager Console of visual studio Add-Migration, for it to create the database Migration file, it generates a column in the project table, to reference the company.

So I put this on, and this answer here at So-PT I created another class to make a mapping Many-to-Many

public class EmpresaProjeto
{
    [Key]
    public Int32 Id { get; set; }
    public virtual Empresa Empresa {get;set;}
    public virtual Projeto Projeto { get; set; }
}

And record the class type in the context

public DbSet<Projeto> Projeto { get; set; }
public DbSet<Empresa> Empresa { get; set; }
public DbSet<EmpresaProjeto> EmpresaProjeto { get; set; }

But it happens that now in my "Company", "Projects" is always empty, how can I do so that in my entity already bring the "Projects" together?

Someone with more experience in EF could help me?

Answer 1

Hello,

In your mapping class try specifying properties that will be Foreign Keys:

• These properties must maintain the type and nomenclature of the parent object.

  public class EmpresaProjeto
  {
      [Key]
      public Int32 Id { get; set; }
      public int EmpresaId {get;set;}
      public virtual Empresa Empresa {get;set;}
      public int ProjetoId { get; set; }
      public virtual Projeto Projeto { get; set; }
  }
  

• Or use Data Annotations:

  public class EmpresaProjeto
  {
     [Key]
     public Int32 Id { get; set; }
     [ForeignKey("EmpresaId")]
     public virtual Empresa Empresa {get;set;}
     [ForeignKey("ProjetoId")]
     public virtual Projeto Projeto { get; set; }
  }
  

I believe I can solve.

Answer 2

From the description of your question, it seems to me you want to build a relationship of 1:N, where a Empresa may have N Projetos and a Projeto may only have 1 Empresa, then you could have the following mapping.:

public class Projeto
{
    [Key]
    public int ProjetoId { get; set; }
    public string Nome { get; set; }
    public string Descricao { get; set; }
    public byte[] Versao { get; set; }
    public int EmpresaId { get; set; }

    [ForeignKey("EmpresaId")]
    public virtual Empresa Empresa { get;set; } 
}


public class Empresa
{
    [Key]
    public Int32 EmpresaId { get;set; }  
    public String Nome { get;set; } 
    public String Fone { get;set; } 
    public String Contato { get;set; } 
    public String Email { get;set; } 
    public virtual ICollection<Projeto>  Projetos  { get;set; } 
}

you can omit the column EmpresaId in the Projeto as follows, however I do not recommend.

public class Projeto
{
    [Key]
    public int ProjetoId { get; set; }
    public string Nome { get; set; }
    public string Descricao { get; set; }
    public byte[] Versao { get; set; }
    public virtual Empresa Empresa { get;set; } 
}


public class Empresa
{
    [Key]
    public Int32 EmpresaId { get;set; }  
    public String Nome { get;set; } 
    public String Fone { get;set; } 
    public String Contato { get;set; } 
    public String Email { get;set; } 
    public virtual ICollection<Projeto>  Projetos  { get;set; } 
}

I do not recommend it for the following fact: if you have the EmpresaId of Empresa and want to update the Projeto, you will have to carry the Empresa in the Contexto, be with a Attach of an incomplete object or searching with a Find.

// Attach do objeto incompleto
var empresa = new Empresa { EmpresaId = empresaId };
context.Empresas.Attach(empresa); 
context.Entry(empresa).State = EntityState.Unchanged; 

projeto.Empresa = empresa;
context.SaveChanges();

// Consultar Objeto
var empresa = context.Empresas.Find(empresaId); 
projeto.Empresa = empresa;
context.SaveChanges();

// Setar o EmpresaId no Projeto
projeto.EmpresaId = empresaId;
context.SaveChanges();

but if you really need a relationship N:M, you are not forced to create an extra Entity.

public class Projeto
{
    [Key]
    public int ProjetoId { get; set; }
    public string Nome { get; set; }
    public string Descricao { get; set; }
    public byte[] Versao { get; set; }
    public virtual ICollection<Empresa> Empresas { get;set; } 
}

public class Empresa
{
    [Key]
    public Int32 EmpresaId { get;set; }  
    public String Nome { get;set; } 
    public String Fone { get;set; } 
    public String Contato { get;set; } 
    public String Email { get;set; } 
    public virtual ICollection<Projeto> Projetos { get;set; } 
}

In the above example, the EF will create the table ProjetoEmpresa in the Database, having a composite key with ProjetoId and EmpresaId.

However if the link between Projeto and Empresa add some information enter, for example the Contract number, in this case it is interpresante add a new entity.

public class Projeto
{
    [Key]
    public int ProjetoId { get; set; }
    public string Nome { get; set; }
    public string Descricao { get; set; }
    public byte[] Versao { get; set; }
    public virtual ICollection<ProjetoEmpresa> Empresas { get;set; } 
}

public class Empresa
{
    [Key]
    public Int32 EmpresaId { get;set; }  
    public String Nome { get;set; } 
    public String Fone { get;set; } 
    public String Contato { get;set; } 
    public String Email { get;set; } 
    public virtual ICollection<ProjetoEmpresa> Projetos { get;set; } 
}

public class ProjetoEmpresa
{
    [Key, Column(Order = 1)]
    public int ProjetoId { get; set; }
    [Key, Column(Order = 2)]
    public int EmpresaId { get; set; }
    public string Contrato { get; set; }        

    [ForeignKey("ProjetoId")]
    public virtual Projeto Projeto { get;set; }     
    [ForeignKey("EmpresaId")]
    public virtual Empresa Empresa { get;set; } 
}

again, you may come to omit the fields EmpresaId and ProjetoId in the ProjetoEmpresa, but do not recommend.

Finally, I see no gain in having a Chave Simples Auto Incremental on the table ProjetoEmpresa, remember, it will usually be used only as a bridge between Projeto and Empresa then it is interesting to have a composite key that reinforces this integrity.

If you choose to have a Chave Simples Auto Incremental, don’t forget to create a Índice Único as suggested by @Ciganomorrisonmendez.

Answer 3

Some things are missing:

public class EmpresaProjeto
{
    [Key]
    public Int32 Id { get; set; }
    [Index("IUQ_EmpresaProjeto_EmpresaId_ProjetoId", IsUnique = true, Order = 1)]
    public int EmpresaId { get; set; }
    [Index("IUQ_EmpresaProjeto_EmpresaId_ProjetoId", IsUnique = true, Order = 2)]
    public int ProjetoId { get; set; }

    public virtual Empresa Empresa {get;set;}
    public virtual Projeto Projeto { get; set; }
}

There was also a lack of inverse relations:

 public class Projeto
 {
    [Key]
    public int Id { get; set; }
    public string Nome { get; set; }
    public string Descricao { get; set; }
    public byte[] Versao { get; set; }

    // Faltou
    public virtual ICollection<EmpresaProjeto> ProjetoEmpresas { get; set; }
 }


public class Empresa
{
    [Key]
    public Int32   Id                              { get;set; }  
    public String  Nome                            { get;set; } 
    public String  Fone                            { get;set; } 
    public String  Contato                         { get;set; } 
    public String  Email                           { get;set; } 
    // Não é mais 1:N, então essa propriedade sai.
    // public virtual ICollection<Projeto>  Projetos  { get;set; } 

    // Entra esta no lugar.
    public virtual ICollection<EmpresaProjeto> EmpresaProjetos { get; set; }
}