5
I have a question about modeling a Desktopapplication system in C# with Mysql.
Essentially I will have the entities Customer, Supplier, PF and PJ. PF can be a customer or supplier. And PJ can also be a customer or supplier. My goal is to use Entity Framework and my main concern is the performance of queries, record listings and reports. I saw several approaches and discussions about it, but nothing conclusive.
Anyway, I’m in doubt among the following 3 modeling options:
Option 1:
CLIENTE
-id_cliente
FORNECEDOR
-id_fornecedor
PF
-id_pf
-id_cliente (não obrigatório)
-id_fornecedor (não obrigatório)
PJ
-id_pj
-id_cliente (não obrigatório)
-id_fornecedor (não obrigatório)
Option 2:
CLIENTE
-id_cliente
-id_pj (não obrigatório)
-id_pf (não obrigatório)
FORNECEDOR
-id_fornecedor
-id_pj (não obrigatório)
-id_pf (não obrigatório)
PF
-id_pf
PJ
-id_pj
Option 3 (which I used to use):
CLIENTE
-id_cliente
-id_pessoa (obrigatório)
-tipo (para indicar se o id_pessoa é na tabela PF ou PJ)
FORNECEDOR
-id_fornecedor
-id_pessoa (obrigatório)
-tipo (para indicar se o id_pessoa é na tabela PF ou PJ)
PF
-id_pf
PJ
-id_pj
I don’t know what the C# model would look like in this third option. I know that at the bank level only, it would be more efficient, but what would the models look like in C#? I don’t think he’d understand this way.
Could someone give an orientation to resolve this impasse? If you can give examples of implementing queries for the best suggestion I will be immensely grateful!
Based on the excellent answers you gave me, I assembled the model as follows:
However, when generating the models they remained this way:
[Table("pessoa")]
public partial class pessoa
{
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
public pessoa()
{
cliente = new HashSet<cliente>();
fornecedor = new HashSet<fornecedor>();
pessoafisica = new HashSet<pessoafisica>();
pessoajuridica = new HashSet<pessoajuridica>();
}
[Key]
public int idPessoa { get; set; }
[Required]
[StringLength(90)]
public string nomeRazaoSocial { get; set; }
public bool pf { get; set; }
}
[Table("pessoafisica")]
public partial class pessoafisica
{
[Key]
public int idPessoaFisica { get; set; }
[StringLength(15)]
public string cpf { get; set; }
public int idPessoa { get; set; }
public virtual pessoa pessoa { get; set; }
}
[Table("pessoajuridica")]
public partial class pessoajuridica
{
[Key]
public int idPessoaJuridica { get; set; }
[StringLength(45)]
public string cnpj { get; set; }
public int idPessoa { get; set; }
public virtual pessoa pessoa { get; set; }
}
That is, there was no specialization at code level.
Speaking in practical terms! If I want to put together a list of all people, with the id_person columns, nameRazaoSocial, CPF/CNPJ.
In this case I would have to use if Else to know if it is PF or PJ and know if I seek the information on personal or personal object?
Is there any strategy in the Entity for the person object to know where it should take this information?
What would be the high-level solution to this issue?
Once again I thank you!
Thanks for the guidance, Gypsy... But I couldn’t map the database models the way you suggested... I edited the question with more information! I really appreciate any knowledge you can share... Hugs!
– Felipe Bulle
I updated the answer. See if it fits you.
– Leonel Sanches da Silva
Gypsy, fantastic! I was really complicating something that is much simpler! I have done several tests using your considerations and I was impressed how functional, clean and intelligible the code was. Can I ask for one last help? Does the View that will receive the list of all people (physical or legal) should receive which model to work? By default it looked like @model Ienumerable<Pessoa>, but then it doesn’t work because the list I’m sending has the Cpfcnpj field that doesn’t have the person model. I thought about creating a model just for the view, but it still didn’t work. What do I do? Fight
– Felipe Bulle
Create a
PessoaViewModel
. Instead of doing.Select(pf => new { ...
, use:.Select(pf => new PessoaViewModel { ...
. Then you can type the View with@model IEnumerable<PessoaViewModel>
– Leonel Sanches da Silva
Ah... now I’ve done it! Thanks a lot, Gypsy! Note 1000!
– Felipe Bulle
I edited again. See I changed the example.
– Leonel Sanches da Silva
Gypsy, I have drawn on another matter... if you can help me you are at http://answall.com/questions/117567/herança-x-composition-mysql-c-entity Thank you!
– Felipe Bulle