I have my base model that all classes inherit from it:
public class Base
{
public int Id {get;set;}
public int ClienteId {get;set;}
}
And an example 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 this?
By Attributes there could be no inheritance. It would look like this:
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;}
...
}
I do via Fluent Api
As an example, it is not possible to include a city with the same name for the same state, so my index
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
}
}));
Just complementing @CiganoMorrisonMendez's answer, because I believe it's the best answer.
You can keep your structure and add a ForeignKey to an inherited property, but 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 do not think it's worth it.
I edited for another option at the end.
If I get it right, the group sends the subgroup, ie the subgroup is tied to the group. The ClientId left out, because the foreign key is in the wrong entity and ClientId will never be the primary key candidate 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 ClientId EF will complain about CASCADE cyclic because it falls on both Group and Subgroup classes, in which case you can either warn EF not to do CASCADE or move the ClientId to Group.
There are polymorphic means of doing this in the DB, but stay on the other occasion.
I see restrictions this 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; }
}
What generates 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 it 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 primary composite 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
);
@Rod reading your question better, I believe what you are trying to do is as follows.
In the example I created two entities, Pessoa and Pedido , where my Pessoa entity has a primary key consisting of columns ClienteId and Cpf , this entity being a 1 to many relationship with Pedido being necessary to have the ClienteId and Cpf properties in our Pedido entity.
Getting the code 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 });
}
}