How to create composite key with Entity Framework 6

Question

I have my model base that all classes inherit from it:

public class Base 
{
   public int Id {get;set;}
   public int ClienteId {get;set;}
}

And an example of model:

public class Grupo: Base
{
   public string Nome {get;set;}
}

And a subgroup

public class SubGrupo:Base
{
  public int GrupoId {get;set;}
  public string Nome {get;set;}
}

I would like you to create a composite key, for example:

foreign key (grupo_id, cliente_id) references cliente (id, cliente_id)

How do I do that?

Answer 1

I do see Fluent Api

an example, it is not possible to include a city with the same name for the same state, thus keeping my

this.Property(a => a.Descricao)
                .HasColumnAnnotation("Index",
                    new IndexAnnotation(new[]
                        {
                            new IndexAttribute("IX_Cidade_Descricao_Unique")
                            {
                                IsUnique = true, 
                                Order=0
                            }
                        }));
            this.Property(a => a.EstadoId)
                .HasColumnAnnotation("Index",
                    new IndexAnnotation(new[]
                        {
                            new IndexAttribute("IX_Cidade_Descricao_Unique")
                            {
                                IsUnique = true, 
                                Order=1
                            }
                        }));

Answer 2

For Attributes there could be no inheritance. I would stay so:

public class Subgrupo 
{
   [Key]
   public int Id {get;set;}
   [ForeignKey("Cliente"), Column(Order = 0)]
   public int GrupoId {get;set;}
   [ForeignKey("Cliente"), Column(Order = 1)]
   public int ClienteId {get;set;}

   public string Nome {get;set;}

   public virtual Cliente Cliente { get; set; }
}

public class Grupo 
{
   [Key, Column(Order = 0)]
   public int Id {get;set;}
   [Key, Column(Order = 1)]
   public int ClienteId {get;set;}

   ...
}

Answer 3

Just complementing the reply of @Ciganomorrisonmendez, because I believe it is the best answer.

You can maintain your structure and add a Foreignkey to an inherited property, however for this you will have to use a Metadatype.

public class Base 
{
    [Key, Column(Order = 0)]
    public int Id {get;set;}

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

public class Grupo: Base
{
    public string Nome {get;set;}
}

[MetadataType(typeof(FK_Grupo_SubGrupo))]
public class SubGrupo:Base
{
    public int GrupoId {get;set;}
    public string Nome {get;set;}
}

public class FK_Grupo_SubGrupo
{
    [ForeignKey("Grupo"), Column(Order = 0)]
    public int GrupoId { get; set; }

    [ForeignKey("Grupo"), Column(Order = 1)]
    public int ClienteId { get; set; }
}

But honestly, I don’t think it’s worth it.

Answer 4

I edited for one more option at the end.

If I understand correctly, Group rules in Subgroup, IE, the subgroup is tied to the group. Clienteid left out, because the foreign key is in the wrong entity and Clientid will never be a candidate for the primary key in another entity, read the options below.

    public abstract class Base
    {
        [Key]
        public int Id { get; set; }

        public int ClienteId { get; set; }
    }

    public class Grupo : Base
    {
        public string Nome { get; set; }
    }

    public class SubGrupo : Base
    {        
        public int GrupoId { get; set; }

        public string Nome { get; set; }

        [ForeignKey("GrupoId")]
        public Grupo Grupo { get; set; }
    }

Note that if you include foreing key in Clienteid the EF will complain of cyclic CASCADE, because it falls on both Group and Subgroup classes, in which case you can either warn the EF not to do CASCADE or move the Clienteid to Group.

There are polymorphic ways to do this in BD, but for another occasion.

I see restrictions that way

public abstract class Base
    {
        [Key]
        public int Id { get; set; }        
    }

    public class Grupo : Base
    {
        public string Nome { get; set; }

        public int ClienteId { get; set; }

        [ForeignKey("ClienteId")]
        public Cliente Cliente { get; set; }
    }

    public class SubGrupo : Base
    {        
        public int GrupoId { get; set; }

        public string Nome { get; set; }

        [ForeignKey("GrupoId")]
        public Grupo Grupo { get; set; }
    }

    public class Cliente
    {
        [Key]
        public int Id { get; set; }
    }

That generate these schemes:

CREATE TABLE [dbo].[Grupoes] (
    [Id]        INT            IDENTITY (1, 1) NOT NULL,
    [Nome]      NVARCHAR (MAX) NULL,
    [ClienteId] INT            NOT NULL,
    CONSTRAINT [PK_dbo.Grupoes] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_dbo.Grupoes_dbo.Clientes_ClienteId] FOREIGN KEY ([ClienteId]) REFERENCES [dbo].[Clientes] ([Id]) ON DELETE CASCADE
);

CREATE TABLE [dbo].[SubGrupoes] (
    [Id]      INT            IDENTITY (1, 1) NOT NULL,
    [GrupoId] INT            NOT NULL,
    [Nome]    NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_dbo.SubGrupoes] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_dbo.SubGrupoes_dbo.Grupoes_GrupoId] FOREIGN KEY ([GrupoId]) REFERENCES [dbo].[Grupoes] ([Id]) ON DELETE CASCADE
);

The rest is JOIN.

I’ll include a Many-To-Many option to make more complete.

public abstract class Base
    {
        [Key]
        public int Id { get; set; }        
    }

    public class Grupo : Base
    {
        public string Nome { get; set; }

        public ICollection<Cliente> Clientes { get; set; }

        public Grupo()
        {
            Clientes = new Collection<Cliente>();
        }
    }

    public class SubGrupo : Base
    {        
        public int GrupoId { get; set; }

        public string Nome { get; set; }

        [ForeignKey("GrupoId")]
        public Grupo Grupo { get; set; }
    }

    public class Cliente
    {
        [Key]
        public int Id { get; set; }

        public ICollection<Grupo> Grupos { get; set; }

        public Cliente()
        {
            Grupos = new Collection<Grupo>();
        }
    }

EF creates the intermediate table with the Foreign and composite primary keys:

CREATE TABLE [dbo].[GrupoClientes] (
    [Grupo_Id]   INT NOT NULL,
    [Cliente_Id] INT NOT NULL,
    CONSTRAINT [PK_dbo.GrupoClientes] PRIMARY KEY CLUSTERED ([Grupo_Id] ASC, [Cliente_Id] ASC),
    CONSTRAINT [FK_dbo.GrupoClientes_dbo.Grupoes_Grupo_Id] FOREIGN KEY ([Grupo_Id]) REFERENCES [dbo].[Grupoes] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.GrupoClientes_dbo.Clientes_Cliente_Id] FOREIGN KEY ([Cliente_Id]) REFERENCES [dbo].[Clientes] ([Id]) ON DELETE CASCADE
);

Answer 5

@Rod reading your question better, I believe what you’re trying to do is the following.

In the example I created two entities, Pessoa and Pedido, where my entity Pessoa has primary key composed by columns ClienteId and Cpf, being that entity a 1-to-many relationship with Pedido it is necessary to have the properties ClienteId and Cpf in our entity Pedido.

The code being as follows:

public partial class Cliente
    {
        public Cliente()
        {
            this.Pedidoes = new List<Pedido>();
        }

        public int ClienteId { get; set; }
        public string Cpf { get; set; }
        public string Nome { get; set; }
        public virtual ICollection<Pedido> Pedidoes { get; set; }
    }

public partial class Pedido
    {
        public int PedidoId { get; set; }
        public Nullable<int> ClienteId { get; set; }
        public string Cpf { get; set; }
        public Nullable<int> Numero { get; set; }
        public virtual Cliente Cliente { get; set; }
    }

public class ClienteMap : EntityTypeConfiguration<Cliente>
    {
        public ClienteMap()
        {
            // Primary Key
            this.HasKey(t => new { t.ClienteId, t.Cpf });

            // Properties
            this.Property(t => t.ClienteId)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

            this.Property(t => t.Cpf)
                .IsRequired()
                .HasMaxLength(50);

            this.Property(t => t.Nome)
                .IsFixedLength()
                .HasMaxLength(10);

            // Table & Column Mappings
            this.ToTable("Cliente");
            this.Property(t => t.ClienteId).HasColumnName("ClienteId");
            this.Property(t => t.Cpf).HasColumnName("Cpf");
            this.Property(t => t.Nome).HasColumnName("Nome");
        }
    }

public class PedidoMap : EntityTypeConfiguration<Pedido>
    {
        public PedidoMap()
        {
            // Primary Key
            this.HasKey(t => t.PedidoId);

            // Properties
            this.Property(t => t.PedidoId)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

            this.Property(t => t.Cpf)
                .HasMaxLength(50);

            // Table & Column Mappings
            this.ToTable("Pedido");
            this.Property(t => t.PedidoId).HasColumnName("PedidoId");
            this.Property(t => t.ClienteId).HasColumnName("ClienteId");
            this.Property(t => t.Cpf).HasColumnName("Cpf");
            this.Property(t => t.Numero).HasColumnName("Numero");

            // Relationships
            this.HasOptional(t => t.Cliente)
                .WithMany(t => t.Pedidoes)
                .HasForeignKey(d => new { d.ClienteId, d.Cpf });

        }
    }