How to get all records from a mixed table generated by class inheritance when using Entityframework 6?

Question

I have a class structure in the following form:

public class Pessoa
{
    public int Id {get; set;}
    public string Nome {get; set;}
}

public class Membro : Pessoa
{
    public int CargoId {get; set;}
    public Cargo Cargo {get; set;}
}

public class MembroTemp : Pessoa { }

My database has the table Pessoas aggregating the data related to the two classes and with this has the field Discriminator.

In a query screen need to bring the two types of data, class Membro and MembroTemp. And for the records like Membro need to get the Cargo and then demonstrate in this consultation.

Then I thought of something like:

var items = repository.Pessoas.Include(x => x.Cargo).OrderBy(x => x.Nome);

But of course it does not, nor does it compile because the class Pessoa does not own the property Cargo, she is from Membro. Not even if I force it with a Include("Cargo") it won’t work, of course.

I tried a unified search instead of doing it separately:

var membros = repository.Membros.Include(x => x.Cargo).OrderBy(x => x.Nome);  
var membrosTemp = repository.MembrosTemp.OrderBy(x => x.Nome);  

Because in my Action realization filters and field ordination. In the case of ordination I do not know how to unify the records but respect the ordination.

If I make separate requests I will end up getting a result like:

+-----------------------------+
|ID | Nome     | Discriminator|
+-----------------------------+
| 1 | Fulano 1 | Membro       |
| 2 | Fulano 2 | Membro       |
| 3 | Fulano 1 | MembroTemp   |
| 4 | Fulano 2 | MembroTemp   |
+-----------------------------+

And that’s not a perfect naming ordination.

How can I handle this situation?

Answer 1

If an inheritance has remained between these entities it is with OfType the solution, and the Dbset will only contain the Super Class Gente. Only reinforcing that in Heritage Oftype is indispensable.

Example: 1 Table Only (Discriminator)

Classes:

public class Gente
{
    public int Id { get; set; }
    public String Nome { get; set; }
}    
public class GenteFisica: Gente
{
    public string Cpf { get; set; }

    public int CargoId {get;set;}        
    [ForeignKey("CargoId")]
    public virtual Cargo Cargo { get; set; }
}    
public class GenteJuridica: Gente
{
    public string Cnpj { get; set; }
    public String Ie { get; set; }
}
[Table("Cargo")]
public class Cargo
{
    public int Id { get; set; }
    public String Nome { get; set; }
}
public class Db : System.Data.Entity.DbContext
{
    public Db()
        : base("Data Source=.\\SqlExpress;initial catalog=generics2;Integrated Security=False;User ID=sa;Password=senha;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False")
    {

    }
    public System.Data.Entity.DbSet<Gente> Gente {get; set;}
    public System.Data.Entity.DbSet<Cargo> Cargo { get; set; }
}

Code:

namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            Db db = new Db();

            Cargo cargo = new Cargo();
            cargo.Nome = "Administrador";

            GenteFisica gentefisica = new GenteFisica();
            gentefisica.Nome = "Fulano Fisica 1";
            gentefisica.Cpf = "12345645600";
            gentefisica.Cargo = cargo;
            gentefisica.CargoId = cargo.Id

            GenteJuridica gentejuridica = new GenteJuridica();
            gentejuridica.Nome = "Fulano Fisica 1";
            gentejuridica.Cnpj = "12345645600-00";
            gentejuridica.Ie = "102030405060";

            db.Cargo.Add(cargo);
            db.Gente.Add(gentefisica);
            db.Gente.Add(gentejuridica);

            db.SaveChanges();

            GenteFisica gentefi = db.Gente.OfType<GenteFisica>().Where(x => x.Id == 1).FirstOrDefault();
            GenteJuridica genteju = db.Gente.OfType<GenteJuridica>().Where(x => x.Id == 2).FirstOrDefault();
            db.Dispose();

        }
    }
}

Generated Base:

Upshot:

---

Example: 3 tables

Add the Table of System.ComponentModel.Dataannotations.Schem that way

[Table("Gente")]
public class Gente
{
    public int Id { get; set; }
    public String Nome { get; set; }
}    
[Table("GenteFisica")]
public class GenteFisica: Gente
{
    public string Cpf { get; set; }

    public int CargoId {get;set;}        
    [ForeignKey("CargoId")]
    public virtual Cargo Cargo { get; set; }
}    
[Table("GenteJuridica")]
public class GenteJuridica: Gente
{
    public string Cnpj { get; set; }
    public String Ie { get; set; }
}    
[Table("Cargo")]
public class Cargo
{
    public int Id { get; set; }
    public String Nome { get; set; }
}    
public class Db : System.Data.Entity.DbContext
{
    public Db()
        : base("Data Source=.\\SqlExpress;initial catalog=generics2;Integrated Security=False;User ID=sa;Password=senha;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False")
    {

    }
    public System.Data.Entity.DbSet<Gente> Gente {get; set;}
    public System.Data.Entity.DbSet<Cargo> Cargo { get; set; }
}

It will create your base with 4 tables:

Note: the encoding is equal to 1 table (Add, Update, Delete and Select) with the DbSet<Gente> for GenteFisica and GenteJuridica

SQL com Order By Nome - Concat - UNION ALL

        **Concat**       
        //Sem o Tipo
        var resultado = db.Gente.OfType<GenteFisica>().Select(x => new
        {                
            x.Id,
            x.Nome
        }).Concat(db.Gente.OfType<GenteJuridica>().Select(g => new
        {                
            g.Id,
            g.Nome                
        })).AsQueryable();

        var resultOrdemNome = resultado.OrderBy(x => x.Nome).ToList();

---

        **Concat**
        //Com o Tipo 
        var resultado = db.Gente.OfType<GenteFisica>().Select(x => new
        {                
            x.Id,
            x.Nome, 
            Gente = "GenteFisica"
        }).Concat(db.Gente.OfType<GenteJuridica>().Select(g => new
        {                
            g.Id,
            g.Nome,
            Gente = "GenteJuridica" 
        })).AsQueryable();
        var resultOrdemNome = resultado.OrderBy(x => x.Nome).ToList();

---

        **Concat**
        //Com tipo e com Cargo quando tem 
        var resultado = db.Gente.OfType<GenteFisica>().Select(x => new
        {                
            x.Id,
            x.Nome, 
            Gente = "GenteFisica",
            Cargo = x.Cargo.Nome
        }).Concat(db.Gente.OfType<GenteJuridica>().Select(g => new
        {                
            g.Id,
            g.Nome,
            Gente = "GenteJuridica",
            Cargo = ""
        })).AsQueryable();
        var resultOrdemNome = resultado.OrderBy(x => x.Nome).ToList();

Answer 2

If LazyLoading is enabled, which is the case by default.

var items = repository.Pessoas.OrderBy(x => x.Nome);  

Returns all table records Pessoas ordered by name.

The only thing needed to gain access to the office is to make a cast for Membro.

Edited according to the Fccdias comment.

var pessoas = items.ToList().Select(i =>
            {
                var membro = i as Membro;
                if(membro != null)
                {
                    return new
                    {
                        membro.Id,
                        membro.Nome,
                        Cargo = membro.Cargo
                    };
                }
                else
                {
                    return new
                    {
                        i.id,
                        i.Nome,
                        Cargo = default(Cargo)
                    };
                }
            });