Inheritance in Entity Framework

2

I have the following structure:

Table:

  

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

Table:

  

Service 1 (Service table1) - with service 1 properties

Table:

  

Service 2 (Service table2) - with service 2 properties

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

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

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

    
asked by anonymous 29.06.2017 / 23:44

2 answers

0

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

  • TPH - Table by Hieraquia - You 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% of an abstract class to prevent it from being instantiated.

For example, consider the Servico , Pessoa , and PessoaFisica entities, while it's interesting to have the ability to query all people. It is important that the user always sign a PessoaJuridica or PessoaFisica .

Now I'm going to post the implementation for each strategy.:

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

TPT - 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 TPH attribute of the [Table] and ServicoA entities.

ServicoB - 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 TPC entity is no longer mapped.

Finally, I'll put the script generated by Migrations for each of the above situations:

Servico - 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])

TPT - 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])
)

TPH - 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 TPC strategy is more limited than the other two. For example, if you want to fetch all services, you will have to merge the two collections.

The choice between TPC and TPH , is a trade-off between the number of columns to be retrieved from the database (% with all%) and the cost to TPT (in the case of TPH , you will need JOINs between tables, and this has a cost, possibly an index will become necessary).

Although there is no 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 JOINS , since it will have a superior performance when compared to TPT .

As for your doubt with delete, deleting an entity of Type TPH will erase the records in the TPT and ServicoB tables, and finally, a record in the Servicos table will always have a child record in the ServicosB table or the Servicos table, but never in both.

    
30.06.2017 / 02:32
1

I'm not an EF expert, but for this scenario there's the Table-per-Type (TPT) mapping 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

  

In this way I had thought, but did not have a simplified way? So, to do a delete, add, update ... I would have to do first on "daughter" and then on "mother". To add, you would have to add the "daughter", return the created ID and create the "mother"

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

// Add

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

context.SaveChanges();

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

In the same way, in the case of Update or Delete, the EF knows which column is from which table and does update or Delete for you.

// Delete

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

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

    
30.06.2017 / 00:04