Mysql and C#inheritance modeling

Asked

Viewed 2,096 times

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:

inserir a descrição da imagem aqui

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!

2 answers

7


In this answer, I explain how to make inheritance for Legal and Physical Persons.

Having this, Cliente and Fornecedor would be compositions of a Pessoa. See that in the answer quoted I talk about inheritance and composition. So, the Models would look like this:

public class Cliente
{
    [Key, ForeignKey("Pessoa")]
    public int PessoaId { get; set; }

    // Coloque os demais campos aqui.

    public virtual Pessoa Pessoa { get; set; }
}

public class Fornecedor
{
    [Key, ForeignKey("Pessoa")]
    public int PessoaId { get; set; }

    // Coloque os demais campos aqui.

    public virtual Pessoa Pessoa { get; set; }
}

Pessoa would receive two more navigation properties:

public class Pessoa
{
    [Key]
    public int PessoaId { get; set; }

    [Required]
    public String NomeOuRazaoSocial { get; set; }

    // As duas propriedades de navegação vão aqui
    public virtual Cliente Cliente { get; set; }
    public virtual Fornecedor Fornecedor { get; set; }
}

That is, a Pessoa can be a Cliente, one Fornecedor, both or neither.


Your modeling is ok, but it needs some adjustments, mainly because you omitted one of the best features of the Entity Framework, which is precisely the specialization at the level of code:

[Table("pessoa")]
// Não há motivo para usar partial classes em Models. 
// Você pode retirar a declaração.
public class pessoa
{
    // Construtores **nunca** devem ser usados para inicializar 
    // propriedades de navegação, então estou comentando.
    /* [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; }

    // Essa propriedade não precisa.
    // public bool pf { get; set; }
}


[Table("pessoafisica")]
public class pessoafisica : pessoa
{
    // A chave já está em pessoa, então não precisa estar aqui.
    // [Key]
    // public int idPessoaFisica { get; set; }

    [StringLength(15)]
    public string cpf { get; set; }

    // Mesma coisa do comentário anterior.
    // public int idPessoa { get; set; }
    // public virtual pessoa pessoa { get; set; }
}


[Table("pessoajuridica")]
public class pessoajuridica : pessoa
{
    // Idem
    // [Key]
    // public int idPessoaJuridica { get; set; }

    [StringLength(45)]
    public string cnpj { get; set; }

    // Idem
    // public int idPessoa { get; set; }    
    // public virtual pessoa pessoa { get; set; }
}

If I want to compile a list of all people, with the columns id_person, nameRazaoSocial, CPF/CNPJ.

All people:

var pessoas = db.Pessoas.ToList(); // Sem CPF/CNPJ

All people with CPF and CNPJ:

var pessoas = db.PessoasFisicas
                .Select(pf => new { 
                              NomeOuRazaoSocial = pf.nomeRazaoSocial, 
                              CpfCnpj = pf.cpf
                       })
                .ToList()
                .Concat(db.PessoasJuridicas
                          .Select(pj => new { 
                             NomeOuRazaoSocial = pj.nomeRazaoSocial,
                             CpfCnpj = pj.cnpj
                          })
                          .ToList());

Using a Viewmodel:

var pessoas = db.PessoasFisicas
                .Select(pf => new PessoaViewModel { 
                              NomeOuRazaoSocial = pf.nomeRazaoSocial, 
                              CpfCnpj = pf.cpf
                       })
                .ToList()
                .Concat(db.PessoasJuridicas
                          .Select(pj => new PessoaViewModel { 
                             NomeOuRazaoSocial = pj.nomeRazaoSocial,
                             CpfCnpj = pj.cnpj
                          })
                          .ToList());

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?

You don’t need any of that. Just map 3 contexts and select for each of them:

public DbSet<Pessoa> Pessoas { get; set; }
public DbSet<PessoaFisica> PessoasFisicas { get; set; }
public DbSet<PessoaJuridica> PessoasJuridicas { get; set; }

Is there any strategy in the Entity for the person object to know where it should take this information?

Checking the object:

var pessoa = db.Pessoas.First(p => p.Id == 1);
if (pessoa is PessoaFisica) { /* Faça alguma coisa */ }
if (pessoa is PessoaJuridica) { /* Faça outra coisa */ }
  • 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!

  • 1

    I updated the answer. See if it fits you.

  • 1

    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

  • Create a PessoaViewModel. Instead of doing .Select(pf => new { ..., use: .Select(pf => new PessoaViewModel { .... Then you can type the View with @model IEnumerable<PessoaViewModel>

  • 1

    Ah... now I’ve done it! Thanks a lot, Gypsy! Note 1000!

  • I edited again. See I changed the example.

  • 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!

Show 2 more comments

3

I solved this by creating a People table People have a field type [Physics, Legal]

A Customer, points to a Person An Employee, points to a person etc. ... inserir a descrição da imagem aqui

  • As I am developing, a PERSON can be a CUSTOMER, SELLER, EMPLOYEE, both physical and legal :-)

  • The idea is to use Entity Framework. What this would look like in the Entity Framework?

  • Ué, I’m using Entity Framework, this is for a Win32 application

  • But it can perfectly be used for ASP, I see no problem. I even use a Unitofwork, which makes data access transparent to the visual layer you choose, Web, Mobile, OS

  • Thank you, duardbr! I absolutely agree with the model you suggested! My question is more in the models of the system... I completed the question with more information! Thank you so much for any knowledge you can share! A hug!

  • Click the arrow up, when you agree to the answer.

  • Clicked! Thanks for the help! I commented on the reply of the gypsy, if you have any consideration, please! Hug!

  • I can’t comment there on your post, it requires 50 points, but I think a Boolean field is not elegant, for definition of Person Type, public Types.Typetype { get; set; } , public Enum Typename : byte { Physics = 1, Juridica = 2 }

Show 3 more comments

Browser other questions tagged

You are not signed in. Login or sign up in order to post.