Fluent API and Migration

Question

I have the following scenario:

public abstract class Pessoa
{

   /* Propriedades do Pessoa*/
   public Guid IdPessoa { get; set; }
}

public class Cliente : Pessoa
{
    public Cliente()
    {
       IdPessoa = Guid.NewGuid();
       Enderecos = new List<Endereco>()
    }
    /* Propriedades do Cliente */
    public virtual string CPF {get; private set; }
    public virtual ICollection<Endereco> Enderecos { get; set; }
}

public class Fornecedor : Pessoa
{
    public Fornecedor()
    {
       IdPessoa = Guid.NewGuid();
       Enderecos = new List<Endereco>()
    }

    public virtual string CNPJ {get; private set; }
    public virtual ICollection<Endereco> Enderecos { get; set; }
}

What I’d like to do:

1º - Use the Migration to create the basis, but in my test it generates the table pessoa with all attributes and classes extending Pessoa only with the IdPessoa and the particular property of each. I would like to know if I can use or if I have to map in the nail?

2º - In the entity Endereco it will possess only number, complement, zip code and the IdPessoa. But I don’t know if this is possible. If it’s not I can have in the same entity (endereco) attributes like IdCliente, IdFornecedor ... Id .... as needed and only filled out according to the entity persisted (in my view, I’m almost sure that this would be a gambiarra), which would be the alternative: a table for each entity type ClienteEndereco, FornecedorEndereco and if that’s what my classes and mapping would look like?

Thank you.

Answer 1

When we talk about mapping classes that use hierarchies for tables, regardless of ORM, we have 3 known patterns:

Table by Hierarchy (TPH)

The total denormalization of tables occurs, that is, the transformation of all fields defined in all classes in the same table.

When this happens, all daughter class properties turn to NULL fields and the Entity Framework creates a "Discriminator" field to know which daughter entity that record belongs to.

Table by Type (TPT)

The parent type and all its children earn their own table each, where the abstract class has the primary key and each child has the primary key only the foreign parent key.

When this happens, we have a fully normalized scenario, as no field repeats.

Table by Concrete Type (TPC)

When your goal is to have a separate table for each daughter class, no matter the repetition of fields in the abstract class, this is the desired type.

Implementing this type of model usually gives an extra job, because each entity must generate its own Id, while in class modeling, they have the same identity field in the abstract class.

1º - Use Migration to create the base, but in my test it generates the person table with all attributes and classes that extend Person only with the Idpessoa and the particular property of each one. I wonder if I can use it or if I have to map in my fingernail ?

From the way you described it in the question, it seems that the TPT pattern is occurring for Pessoa but you want a TPC. In your case, as Pessoa has no fields of its own and the Idpessoa that each child carries is a Guid independent of each other, the TPC seems to be the best solution for their abstraction.

Considering your classes above, we have the following configuration for the Person classes, regardless of the default:

public DbSet<Pessoa> Pessoas { get; set; }

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    // mapeamento específico conforme o padrão

    // mapeamento comum das classes
    modelBuilder.Entity<Pessoa>().HasKey(x => x.IdPessoa);
    modelBuilder.Entity<Pessoa>().Property(x => x.IdPessoa)
                                 .IsRequired()
                                 .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

    modelBuilder.Entity<Cliente>().Property(x => x.CPF)
                                  .IsRequired()
                                  .IsFixedLength()
                                  .IsUnicode(false)
                                  .HasColumnType("char")
                                  .HasMaxLength(11);

    modelBuilder.Entity<Fornecedor>().Property(x => x.CNPJ)
                                     .IsRequired()
                                     .IsFixedLength()
                                     .IsUnicode(false)
                                     .HasColumnType("char")
                                     .HasMaxLength(14);

    base.OnModelCreating(modelBuilder);
}

An example of base creation and records for each type of person:

Database.SetInitializer<Contexto>(new DropCreateDatabaseAlways<Contexto>());

using (var contexto = new Contexto())
{
    var cliente = new Cliente { CPF = "74182476808" }; // gerado aleatoriamente
    contexto.Pessoas.Add(cliente);

    var fornecedor = new Fornecedor { CNPJ = "89205398000106" }; // gerado aleatoriamente
    contexto.Pessoas.Add(fornecedor);

    contexto.SaveChanges();
}

Whereas it seems that you have a TPT case, your specific mapping should have something like:

modelBuilder.Entity<Cliente>().ToTable("Cliente");
modelBuilder.Entity<Fornecedor>().ToTable("Fornecedor");

And consequently, your tables should be created as:

By your question, you would need a mapping like the below to have a TPC:

modelBuilder.Entity<Cliente>().Map(m =>
{
    m.MapInheritedProperties(); // necessário para indicar que Pessoas deve estar contido em Cliente
    m.ToTable("Cliente");
});

modelBuilder.Entity<Fornecedor>().Map(m =>
{
    m.MapInheritedProperties(); // necessário para indicar que Pessoas deve estar contido em Fornecedor
    m.ToTable("Fornecedor");
});

Thus, their tables would be created as:

In this case, as for the Entity Framework we have only the Person type, to get only Customers you would need to do something like:

var clientes = contexto.Pessoas.OfType<Cliente>().ToList();

What should also be considered is that, by the nature of its class mapping, making a context command.People.Tolist() would always generate a UNION ALL query among all child type tables. Because of this, the recommended is to check very well the type of querys you intend to make for this type of mapping.

2º - In the entity Endereco it will have only number, complement, zip code and the Idpessoa. However I do not know if this is possible. If it is not I can have in the same entity ( address ) attributes as Idcustomer, Idsupplier ... Id .... as needed and only filling according to the entity persisted ( In my view , I’m almost sure that this would be a gambiarra ), what would be the alternative a table for each entity type Clienteaddressee, Supplierseco and if that’s what my classes and mapping would look like ?

If in your Address entity the fields are always equal, it will relate to Pessoa and will only have the Pessoaid you defined in the abstract class.

The entities would look something like:

public abstract class Pessoa
{
    public Guid IdPessoa { get; set; }
    public virtual ICollection<Endereco> Enderecos { get; set; }
}

public class Cliente : Pessoa
{
    public Cliente()
    {
        IdPessoa = Guid.NewGuid();
        //Enderecos = new List<Endereco>();
    }

    public string CPF { get; set; }
}

public class Fornecedor : Pessoa
{
    public Fornecedor()
    {
        IdPessoa = Guid.NewGuid();
        //Enderecos = new List<Endereco>();
    }

    public string CNPJ { get; set; }
}

public class Endereco
{
    public int IdEndereco { get; set; }
    public string Numero { get; set; }
    public string Complemento { get; set; }
    public string Cep { get; set; }

    public Guid IdPessoa { get; set; }
    public virtual Pessoa Pessoa { get; set; }
}

And Endereco’s mapping would be:

modelBuilder.Entity<Endereco>().HasKey(x => x.IdEndereco);
modelBuilder.Entity<Endereco>().Property(x => x.IdEndereco)
                               .IsRequired()
                               .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

modelBuilder.Entity<Endereco>().Property(x => x.Numero)
                               .IsRequired()
                               .IsUnicode(false)
                               .HasColumnType("varchar")
                               .HasMaxLength(5);

modelBuilder.Entity<Endereco>().Property(x => x.Complemento)
                               .IsRequired()
                               .IsUnicode(false)
                               .HasColumnType("varchar")
                               .HasMaxLength(100);

modelBuilder.Entity<Endereco>().Property(x => x.Cep)
                               .IsRequired()
                               .IsFixedLength()
                               .IsUnicode(false)
                               .HasColumnType("char")
                               .HasMaxLength(8);

modelBuilder.Entity<Endereco>().Property(x => x.IdPessoa)
                               .IsRequired();

modelBuilder.Entity<Pessoa>().HasMany<Endereco>(pessoa => pessoa.Enderecos)
                             .WithRequired(endereco => endereco.Pessoa)
                             .HasForeignKey(endereco => endereco.IdPessoa);

Whereas we are adding 3 addresses as below:

using (var contexto = new Contexto())
{
    var cliente = new Cliente { CPF = "74182476808" }; // gerado aleatoriamente
    contexto.Pessoas.Add(cliente);

    var fornecedor = new Fornecedor { CNPJ = "89205398000106" }; // gerado aleatoriamente
    contexto.Pessoas.Add(fornecedor);

    var enderecoCliente1 = new Endereco { Numero = "1", Cep = "00000000", Complemento = "Teste endereço cliente 1", Pessoa = cliente };
    contexto.Enderecos.Add(enderecoCliente1);

    var enderecoCliente2 = new Endereco { Numero = "2", Cep = "00000000", Complemento = "Teste endereço cliente 2 ", Pessoa = cliente };
    contexto.Enderecos.Add(enderecoCliente2);

    var enderecoFornecedor = new Endereco { Numero = "3", Cep = "00000000", Complemento = "Teste endereço fornecedor", Pessoa = fornecedor };
    contexto.Enderecos.Add(enderecoFornecedor);

    contexto.SaveChanges();
}

That way, regardless of whether you are using TPT or TPC and you want, for example, only the clients and their respective addresses, you would do:

var clientes = contexto.Pessoas.Include("Enderecos").OfType<Cliente>().ToList();

This would generate a query like:

SELECT 
    [Project1].[C2] AS [C1], 
    [Project1].[C1] AS [C2], 
    [Project1].[IdPessoa] AS [IdPessoa], 
    [Project1].[CPF] AS [CPF], 
    [Project1].[C3] AS [C3], 
    [Project1].[IdEndereco] AS [IdEndereco], 
    [Project1].[Numero] AS [Numero], 
    [Project1].[Complemento] AS [Complemento], 
    [Project1].[Cep] AS [Cep], 
    [Project1].[IdPessoa1] AS [IdPessoa1]
    FROM ( SELECT 
        [Extent1].[IdPessoa] AS [IdPessoa], 
        [Extent1].[CPF] AS [CPF], 
        '0X0X' AS [C1], 
        1 AS [C2], 
        [Extent2].[IdEndereco] AS [IdEndereco], 
        [Extent2].[Numero] AS [Numero], 
        [Extent2].[Complemento] AS [Complemento], 
        [Extent2].[Cep] AS [Cep], 
        [Extent2].[IdPessoa] AS [IdPessoa1], 
        CASE WHEN ([Extent2].[IdEndereco] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C3]
        FROM  [dbo].[Cliente] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Enderecoes] AS [Extent2] ON [Extent1].[IdPessoa] = [Extent2].[IdPessoa]
    )  AS [Project1]
    ORDER BY [Project1].[IdPessoa] ASC, [Project1].[C3] ASC                            

And finally, the result would be:

Important: as we are talking about Customer and Supplier as different and unrelated tables, the "Idpessoa" field of the Address table will never have a FK.

If this key is crucial to your case, it is recommended to use TPT for Person or, in the worst case, leave the list of Addresses in each Person and keep N NULL fields in the Address table to deal with each of Person’s children.

Answer 2

1º - Use Migration to create the base, but in my test it generates the person table with all attributes and classes that extend Person only with the Idpessoa and the particular property of each one. I wonder if I can use it or if I have to map in my fingernail?

This is because you mapped Pessoa with a DbSet in context. In this case, the Entity Framework understands that Cliente and Fornecedor extend Pessoa and put all fields in the same table, which is correct, since you can use it like this:

var pessoas = db.Pessoas.ToList();

This consultation brings all people, independent of being customers or suppliers. If you want to keep customers and suppliers separate, remove the DbSet of Pessoa and manages the database again.

2º - In the entity Endereco it will have only number, complement, zip code and the Idpessoa. However I do not know if this is possible. If it is not I can have in the same entity ( address ) attributes as Idcustomer, Idsupplier ... Id .... as needed and only filling according to the entity persisted ( In my view , I’m almost sure that this would be a gambiarra ), what would be the alternative a table for each entity type Clienteaddressee, Supplierseco and if that’s what my classes and mapping would look like?

Solving point 1 is automatically solved by design point 2, that is, it does not need to exist ClienteEndereco and FornecedorEndereco if you use the unified table structure with inheritance. Only Endereco already solves well what you need.

Now, if the intention is to leave Cliente and Fornecedor in separate tables, you will need to perform the mapping of ClienteEndereco and FornecedorEndereco, respectively, and may not necessarily use IdPessoa. Can use IdCliente or IdFornecedor, merely indicating to the Entity Framework the correct browsing properties, i.e., to ClienteEndereco:

public int ClienteId { get; set; }
public virtual Cliente Cliente { get; set; }

And to Fornecedor:

public int FornecedorId { get; set; }
public virtual Fornecedor Fornecedor { get; set; }

As said in comment, if you want to unify the addresses, but specializing by Customer and Supplier, you will also have to put the inheritance in Endereco:

public abstract class Endereco { ... }
public class ClienteEndereco : Endereco { ... }
public class FornecedorEndereco { ... }