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
);
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.
– Malkaviano
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
– Rod
Base is concrete and may have physical representation or it is an Abstract class?
– Malkaviano
Only Abstract, for all models to be generated with its properties...id, createdate, clienteid
– Rod