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.