many to many entity framework + Compose Primary Key

Question

Hi friends I am having problems with a relationship Much to Much with Compose Primary Key.

I have the following:

public class Empleado
{
   [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   [Key, Column(Order = 0)]
   public int Id { get; set; }

   [Required]
   [StringLength(100)]
   public string Nombre { get; set; }

   [Key, Column(Order = 1)]
   public int? IdentificacionId { get; set; }
   public Identificacion Identificacion { get; set; }

   [Required]
   [StringLength(11)]
   [Key, Column(Order = 2)]
   public string NoIdentificacion { get; set; }
}

// Entidad relación
public class EmpleadoNomina
{
   public int EmpleadoId { get; set; }
   public int NominaId { get; set; }
   public decimal Salario { get; set; }
   public int DescuentoLey { get; set; }
   public decimal? SalarioIngresoEgreso { get; set; }

   public Nomina Nomina { get; set; }
   public Empleado Empleado { get; set; }
}

// FluentApi
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
   // Constraint combinado TipoId + NoID
   modelBuilder.Entity<Empleado>().HasKey(x => new { x.IdentificacionId, x.NoIdentificacion });

   // Relación
   modelBuilder.Entity<EmpleadoNomina>().HasKey(k => new { k.NominaId, k.EmpleadoId });
}

The problem arises when the relationship table is created. To this is added the columns Employee_IdentificationId, Employee_NoIdentification. And the EmployeeId column without foreignkey. The other problem is: I can't use .Find(id); example: db.Empleados.Find(15); This gives an error because it requires me to pass the three keys.

I just want to remove the extra columns Employee_IdentificationId, Employee_NoIdentification and only use EmpleadoId.

Steve Greene · Answer 1 · 2018-02-13T15:12:13.803

Don't use a composite key on Empleado - just use ID as its key. Same for Nomina. The composite key is used on the bridge table. Also, since you are already using fluent code you don't need the annotations. Behavior can be odd when you mix.

public class Empleado
{
   // This will be identity key by convention
   public int Id { get; set; }

   // These could be set in fluent code
   [Required]
   [StringLength(100)]
   public string Nombre { get; set; }
   public string NoIdentificacion { get; set; }

   // This will be an optional FK by convention
   public int? IdentificacionId { get; set; }
   public Identificacion Identificacion { get; set; }

   public virtual ICollection<Nomina> Nominas { get; set; }

}

public class Nomina 
{
   // This will be identity key by convention
   public int Id { get; set; }
   public string XXXXXX { get; set; }
   ... etc

   public virtual ICollection<Empleado> Empleados { get; set; }
}

public class EmpleadoNomina
{
   public int EmpleadoId { get; set; }
   public int NominaId { get; set; }
   public decimal Salario { get; set; }
   public int DescuentoLey { get; set; }
   public decimal? SalarioIngresoEgreso { get; set; }

   public Nomina Nomina { get; set; }
   public Empleado Empleado { get; set; }
}

// FluentApi
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Empleado>()
                .HasMany<Nomina>(e => e.Nominas)
                .WithMany(c => c.Empleado)
                .Map(cs =>
                        {
                            cs.MapLeftKey("Id");
                            cs.MapRightKey("Id");
                            cs.ToTable("EmpleadoNomina");
                        });
}

See here

EDIT: OK, If you need to keep the composite key on Empleado, then you will need to reference it with a composite FK. So you need to add the other 2 FK fields:

// Entidad relación
public class EmpleadoNomina
{
   public int EmpleadoId { get; set; }
   public int IdentificacionId { get; set; }
   public string NoIdentificacion { get; set; }

   public int NominaId { get; set; }
   public decimal Salario { get; set; }
   public int DescuentoLey { get; set; }
   public decimal? SalarioIngresoEgreso { get; set; }

   public Nomina Nomina { get; set; }
   public Empleado Empleado { get; set; }
}

Then the fluent code:

modelBuilder.Entity<EmpleadoNomina>()
    .HasRequired(en => en.Empleado)
    .WithMany()
    .HasForeignKey(en => new {en.EmpleadoId, en.IdentificacionId , en.NoIdentificacion });

Also, I am not sure IdentificacionId can be nullable. See here.

I understand but i need composite key on IdentificacionId and NoIdentificacion and i need extra columns in relation table — Hector Lara, Feb 13 '18 at 14:42
Well, that is going to be fairly complex. See my edit. I would reconsider the design if possible. — Steve Greene, Feb 13 '18 at 15:12

Hector Lara · Accepted Answer · 2018-02-13T17:43:27.763

I solved it with Index Dataanotations to create the Unique Composited Index instead of a Composited primary key (this was responsible of my problem).

I removed the composite keys from the main class and added a list of EmployeeNomine to the two classes of entities.

I changed everything as shown below and now it is working very well. This what I wanted to do from the beginning.

// Class 2
public class Empleado
{
   [Key]
   public int Id { get; set; }

   [Required]
   [StringLength(100)]
   public string Nombre { get; set; }

   [Index("IX_Identificacion", 1, IsUnique = true)]
   public int? IdentificacionId { get; set; }
   public Identificacion Identificacion { get; set; }

   [Required]
   [StringLength(11)]
   [Index("IX_Identificacion", 2, IsUnique = true)]
   public string NoIdentificacion { get; set; }

   public List<EmpleadoNomina> EmpleadoNominas { get; set; }
}

// Class 1
public class Nomina
{
   [Key]
   public int Id { get; set; }

   [Required]
   [StringLength(200)]
   public string Descripcion { get; set; }

   public int Frecuencia { get; set; }

   public int Dia { get; set; }

   public List<EmpleadoNomina> EmpleadoNominas { get; set; }
}

// Relation Entity (Table)
public class EmpleadoNomina
{
   public int EmpleadoId { get; set; }
   public int NominaId { get; set; }
   public decimal Salario { get; set; }
   public int DescuentoLey { get; set; }
   public decimal? SalarioIngresoEgreso { get; set; }

   public Nomina Nomina { get; set; }
   public Empleado Empleado { get; set; }
}

// FluentApi
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
   // Nominas -> Empleados
   modelBuilder.Entity<EmpleadoNomina>().HasKey(k => new { k.NominaId, k.EmpleadoId });
   modelBuilder.Entity<EmpleadoNomina>().HasRequired(e => e.Empleado).WithMany(n => n.EmpleadoNominas).HasForeignKey(r => r.EmpleadoId);
   modelBuilder.Entity<EmpleadoNomina>().HasRequired(n => n.Nomina).WithMany(n => n.EmpleadoNominas).HasForeignKey(n => n.NominaId);
}

It's I always wanted to do. thanks for everything

many to many entity framework + Compose Primary Key

2 Answers2