How to create composite key with Entity Framework 6

Asked

Viewed 6,761 times

4

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?

  • In the SQL example you passed, cliente_id are two fields in two different tables, but in your code the inheritance makes it only 1 Clientid. I particularly did not understand what you want, you drew a command of Foreign key, this command would be in the table Subgroup? Does Table Base exist? A precise quality response of more details than you want to achieve, Template and Table are separate things, leave more explicit what you want, you used a table name that does not match the models shown, did not indicate in which table you placed this Foreign key.

  • Ola @Malkaviano, the in SQL is in postgress, the former of the models the generation is via code first, the ultimate purpose is the protection of a multitenant APP that shares the same dB, a vuln ex would be I change the value of a group that is in another tenant and save in my subgroup... In SQL I know how to protect, but I don’t know when to generate Sb with codefirst

  • Base is concrete and may have physical representation or it is an Abstract class?

  • Only Abstract, for all models to be generated with its properties...id, createdate, clienteid

5 answers

2

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
                            }
                        }));

1


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;}

   ...
}
  • using the "Base" class is not possible ? I have numerous classes that inherit, just of not having many properties within it. Taking advantage of this comment, what are you using to generate pdf? rs itextsharp is now agpl, i.e., non-commercial =/

  • To class Base, GrupoId would need to be in Base also.

  • It uses itextsharp, is with the version that is GPL or AGPL license ?

  • License Apache, actually. https://github.com/DesignLiquido/RazorPDF2/blob/master/License.txt

1

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.

0

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
);

-1

@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 });

        }
    }

Browser other questions tagged

You are not signed in. Login or sign up in order to post.