Inheritance in Entity Framework

Question

I have the following structure:

Table:

Services (Services table) - with the common properties of all services + Type + Serviceid

Table:

Service 1 (Service table 1) - with service properties 1

Table:

Service 2 (Service table 2) - with service properties 2

However, I need to save, in the Services table, information such as "Service Type" and "Service Id".

Basically, if a Service 2 is registered, a tbm record will be created in the Services table with the "Service Type = Service 2" and "Service Id = Service Id (from the Service 2 table)".

How I can represent this with entities in the Entity Framework?

Answer 1

I am not an EF specialist, but for this scenario there is the map called Table-per-Type (TPT) that looks good with what you need.

Each entity is mapped to a table.

Example:

public class Servico
{
   [Key]
   public int ServicoId { get; set; }
   public string Tipo { get; set; }
}

public class Servico1 : Servico
{
    //com as propriedades do serviço 1
}

public class Servico2: Servico
{
   //com as propriedades do serviço 1
}

Mapping via Code First:

modelBuilder.Entity<Servico>().ToTable("Servico");
modelBuilder.Entity<Servico1>().ToTable("Servico1");
modelBuilder.Entity<Servico2>().ToTable("Servico2");

Editing

That way I would have thought, but isn’t there a simplified way? So, to delete, add, update...I would have to do first in "daughter" and then in "mother". To add, you would have to add the "daughter", return the created ID and create the "mother"

As the tables/objects are associated you do not need to do an operation for each object.

//Add

context.Servico.Add(new Servico1
{
    //com as propriedades do serviço 2
});

context.SaveChanges();

The above EF code will create an Insert for the two tables (Service and Servic1).

Similarly, in the case of Update or Delete, EF knows which column is which table and updates or Delete for you.

//Delete

var primeiroService = context.Service.FirstOrDefault();
if (primeiroService != null)
{
    context.Service.Remove(primeiroService);
    context.SaveChanges();
}

In the above code the EF will create a delete for the two tables (Service and Servic1) because as the tables are related it knows which record to delete.

Answer 2

First a piece of advice, try to study the differences between TPH, TPC and TPT, so you can choose the best option for each scenario.

• TPH - Table by Hieraquia - Will have a single table for all Entities.
• TPC - Table by Concrete - You will have a table for each concrete type.
• TPT - Table by Type - You will have a table for each type (abstract or concrete).

In your specific case, I advise you to make Servico an abstract class, to prevent it from being instantiated.

For example, think of the entities Pessoa, PessoaFisica and PessoaJuridica, while it is interesting to have the ability to consult all people. It is important that the user always register a PessoaFisica or PessoaJuridica.

now I will post the implementation for each strategy.:

TPT - Table by Type

public class MyContext : DbContext
{
    public MyContext()
    {

    }

    public DbSet<Servico> Servicos { get; set; }
    public DbSet<ServicoA> ServicosA { get; set; }
    public DbSet<ServicoB> ServicosB { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
    }
}

[Table("Servicos")]
public abstract class Servico
{
    [Key]
    public Guid ServicoID { get; set; }
    public string Descricao { get; set; }
}

[Table("ServicosA")]
public class ServicoA : Servico
{
    public decimal ValorA { get; set; }
}

[Table("ServicosB")]
public class ServicoB : Servico
{
    public decimal ValorB { get; set; }
}

TPH - Table by Hieraquia

public class MyContext : DbContext
{
    public MyContext()
    {

    }

    public DbSet<Servico> Servicos { get; set; }
    public DbSet<ServicoA> ServicosA { get; set; }
    public DbSet<ServicoB> ServicosB { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
    }
}

[Table("Servicos")]
public abstract class Servico
{
    [Key]
    public Guid ServicoID { get; set; }
    public string Descricao { get; set; }
}

public class ServicoA : Servico
{
    public decimal ValorA { get; set; }
}

public class ServicoB : Servico
{
    public decimal ValorB { get; set; }
}

Note the removal of the attribute [Table] of entities ServicoA and ServicoB.

TPC - Table by Concrete

public class MyContext : DbContext
{
    public MyContext()
    {

    }

    public DbSet<ServicoA> ServicosA { get; set; }
    public DbSet<ServicoB> ServicosB { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
    }
}

public abstract class Servico
{
    [Key]
    public Guid ServicoID { get; set; }
    public string Descricao { get; set; }
}

[Table("ServicosA")]
public class ServicoA : Servico
{
    public decimal ValorA { get; set; }
}

[Table("ServicosB")]
public class ServicoB : Servico
{
    public decimal ValorB { get; set; }
}

Note that the entity Servico is no longer mapped.

Finally, I will put the script generated by Migrations for each of the above situations.:

TPT - Table by Type

CREATE TABLE [dbo].[Servicos] (
    [ServicoID] [uniqueidentifier] NOT NULL,
    [Descricao] [nvarchar](max),
    CONSTRAINT [PK_dbo.Servicos] PRIMARY KEY ([ServicoID])
)
CREATE TABLE [dbo].[ServicosA] (
    [ServicoID] [uniqueidentifier] NOT NULL,
    [ValorA] [decimal](18, 2) NOT NULL,
    CONSTRAINT [PK_dbo.ServicosA] PRIMARY KEY ([ServicoID])
)
CREATE INDEX [IX_ServicoID] ON [dbo].[ServicosA]([ServicoID])
CREATE TABLE [dbo].[ServicosB] (
    [ServicoID] [uniqueidentifier] NOT NULL,
    [ValorB] [decimal](18, 2) NOT NULL,
    CONSTRAINT [PK_dbo.ServicosB] PRIMARY KEY ([ServicoID])
)
CREATE INDEX [IX_ServicoID] ON [dbo].[ServicosB]([ServicoID])
ALTER TABLE [dbo].[ServicosA] ADD CONSTRAINT [FK_dbo.ServicosA_dbo.Servicos_ServicoID] FOREIGN KEY ([ServicoID]) REFERENCES [dbo].[Servicos] ([ServicoID])
ALTER TABLE [dbo].[ServicosB] ADD CONSTRAINT [FK_dbo.ServicosB_dbo.Servicos_ServicoID] FOREIGN KEY ([ServicoID]) REFERENCES [dbo].[Servicos] ([ServicoID])

TPH - Table by Hieraquia

CREATE TABLE [dbo].[Servicos] (
    [ServicoID] [uniqueidentifier] NOT NULL,
    [Descricao] [nvarchar](max),
    [ValorA] [decimal](18, 2),
    [ValorB] [decimal](18, 2),
    [Discriminator] [nvarchar](128) NOT NULL,
    CONSTRAINT [PK_dbo.Servicos] PRIMARY KEY ([ServicoID])
)

TPC - Table by Concrete

CREATE TABLE [dbo].[ServicosA] (
    [ServicoID] [uniqueidentifier] NOT NULL,
    [ValorA] [decimal](18, 2) NOT NULL,
    [Descricao] [nvarchar](max),
    CONSTRAINT [PK_dbo.ServicosA] PRIMARY KEY ([ServicoID])
)
CREATE TABLE [dbo].[ServicosB] (
    [ServicoID] [uniqueidentifier] NOT NULL,
    [ValorB] [decimal](18, 2) NOT NULL,
    [Descricao] [nvarchar](max),
    CONSTRAINT [PK_dbo.ServicosB] PRIMARY KEY ([ServicoID])
)

Finally, note that the strategy TPC is more limited than the other two. For example, if you want to seek out all the services, you have to make a union between the two collections.

Already the choice between TPH and to TPT, is a trade-off between number of columns to be recovered from the bank (TPH brings everything) and the cost to realize JOINS(in the case of TPT, will need Joins between tables, and this has a cost, possibly an index will become necessary).

Although there isn’t a silver bullet for which strategy to choose, if the bank is to be manipulated only by its solution (without direct human intervention in the database), I would say to stay with the TPH, because this will have a superior performance when compared to the TPT.

And as for your doubt with delete, when deleting an entity of the Type ServicoB, records in tables will be deleted Servicos and ServicosB, and finally, a record in the table Servicos, will always have a child record in the table ServicosA or in the table ServicosB, but never both.