Fluent API and Migration

1

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:

1st - Use migration to create the base, but in my test it generates the table pessoa with all attributes and classes that extend Pessoa only with IdPessoa e the private property of each. Would you like to know if I can use or if I have to map on the nail?

2º - In the entity Endereco it will only have number, complement, ZIP code and IdPessoa . But I do not know if that is possible. If it is not, I can have attributes like endereco , IdCliente ... Id .... as needed and only filling according to the persisted entity (in my view, I have almost I would like to know if there is a way to do this:

Thank you.

    
asked by anonymous 21.01.2016 / 22:35

2 answers

5

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 into a single table.

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

Table by Type (TPT)

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

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

Table by Type Concrete (TPC)

When your goal is to have a separate table for each child class, no matter the field repetition in the abstract class, this is the type you want.

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

  

1st - Use migration to create the base, but in my test it generates the person table with all attributes and classes that extend Person with only the Person Id and the private property of each. Would you like to know if I can use or if I have to map on the nail?

By the way you described in the question, it seems that the TPT pattern for the Person is occurring but you want a TPC. In your case, since Person does not have its own fields and the Id Person that each child carries is a Guid independent of the other, TPC seems to be the best solution for its abstraction.

Considering your classes above, we have the following configuration for 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();
}

Since it looks like you have a TPT case, your specific mapping should have something like:

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

And therefore, your tables should be being created as:

Foryourquestion,youwouldneedamappingliketheonebelowtohaveaTPC:

modelBuilder.Entity<Cliente>().Map(m=>{m.MapInheritedProperties();//necessárioparaindicarquePessoasdeveestarcontidoemClientem.ToTable("Cliente");
});

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

In this way, your tables would be created as:

Inthiscase,asfortheEntityFrameworkwehaveonlythePersontype,togetonlyCustomersyouwouldneedtodosomethinglike:

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

Whatshouldalsobeconsideredisthat,bythenatureofitsmappingbetweenclasses,makingacontextcommand.People.ToList()wouldalwaysgenerateaquerywithUNIONALLbetweenallchildtypetables.Becauseofthis,itisrecommendedthatthetypeofqueriesyouwanttodoforthistypeofmappingbecheckedverywell.

  

2º-IntheAddressentityitwillhaveonlynumber,complement,cepandtheIdPessoa.ButIdonotknowifthisispossible.IfitisnotIcanhaveinthesameentity(address)attributessuchasCustomerID,CustomerID...Id....asneededandonlyfillingaccordingtothepersistedentity(Inmyview,I'mprettysurethiswouldbeagambiarra),whatwouldbethealternativeatableforeachentitytypeClientEndereco,VendorEnderecoandifthatiswhatmyclassesandthemappingwouldlooklike?

IfinyourAddressentitythefieldsarealwaysthesame,itrelatestoPersonandwillonlyhavethePersonIdyoudefinedintheabstractclass.

Entitieswouldlooksomethinglike:

publicabstractclassPessoa{publicGuidIdPessoa{get;set;}publicvirtualICollection<Endereco>Enderecos{get;set;}}publicclassCliente:Pessoa{publicCliente(){IdPessoa=Guid.NewGuid();//Enderecos=newList<Endereco>();}publicstringCPF{get;set;}}publicclassFornecedor:Pessoa{publicFornecedor(){IdPessoa=Guid.NewGuid();//Enderecos=newList<Endereco>();}publicstringCNPJ{get;set;}}publicclassEndereco{publicintIdEndereco{get;set;}publicstringNumero{get;set;}publicstringComplemento{get;set;}publicstringCep{get;set;}publicGuidIdPessoa{get;set;}publicvirtualPessoaPessoa{get;set;}}

AndthemappingofAddresswouldlooklike:

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);

Considering that 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();
}

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

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

This would generate a query as:

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:AswearetalkingaboutClientandSupplierasdifferenttablesandunrelated,thefield"IdPessoa" of the Address table will never have an FK.

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

    
24.01.2016 / 20:59
3
  

1st - Use migration to create the base, but in my test it generates the person table with all attributes and classes that extend Person with only the Person Id and the private property of each. Would you like to know if I can use or if I have to map on the nail?

This is because you have mapped Pessoa to a corresponding 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 this:

var pessoas = db.Pessoas.ToList();

This query brings all people, regardless of whether they are customers or suppliers. If you want to keep clients and vendors separate, remove DbSet from Pessoa and then regenerate the database.

  

2º - In the Address entity it will have only number, complement, cep and the IdPessoa. But I do not know if this is possible. If it is not I can have in the same entity (address) attributes such as CustomerID, CustomerID ... Id .... as needed and only filling according to the persisted entity (In my view, I'm pretty sure this would be a gambiarra ), what would be the alternative a table for each entity type ClientEndereco, VendorEndereco and if that is what my classes and the mapping would look like?

Solving point 1 will automatically resolve point 2 by design, ie there will be no need to ClienteEndereco and FornecedorEndereco if you use the unified structure of tables with inheritance. Only Endereco already solves well what you need.

Now, if you want to leave Cliente and Fornecedor in separate tables, you will have to map ClienteEndereco and FornecedorEndereco , respectively, and you can use IdPessoa . You can use IdCliente or IdFornecedor , only indicating to the Entity Framework the correct navigation properties, ie for ClienteEndereco :

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

And for Fornecedor :

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

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

public abstract class Endereco { ... }
public class ClienteEndereco : Endereco { ... }
public class FornecedorEndereco { ... }
    
21.01.2016 / 22:42