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

8

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 Pessoas table aggregating the data for the two classes and thus has the Discriminator field.

Onaqueryscreen,Ineedtobringbothdatatypes,ofclassMembroandMembroTemp.And,forrecordsoftypeMembro,IneedtogettheCargotothendemonstrateinthatquery.

SoIthoughtofsomethinglike:

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

But of course it does not give or compile because the Pessoa class does not have the Cargo property, it is Membro . Not even if I force it with a Include("Cargo") will not 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 I do filters and order by field . In the case of sorting I do not know how to unify the records but respect the sorting.

If I make separate requests I will end up with a result of type:

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

And this is not a perfect name ordering.

How can I deal with this situation?

    
asked by anonymous 21.04.2014 / 17:12

2 answers

6

If an inheritance between these entities was left with OfType the solution, and DbSet will only contain Super Class Gente . Only reinforcing that InType inheritance 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:

Result:

Example: 3 Tables

Add in those classes the Table of the System.ComponentModel Table

  • .DataAnnotations .Schem this 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:Theencodingisequalto1table(Add,Update,DeleteandSelect)withDbSet<Gente>toGenteFisicaandGenteJuridica

    SQLwithOrderByName-Concat-UNIONALL

    **Concat**//SemoTipovarresultado=db.Gente.OfType<GenteFisica>().Select(x=>new{x.Id,x.Nome}).Concat(db.Gente.OfType<GenteJuridica>().Select(g=>new{g.Id,g.Nome})).AsQueryable();varresultOrdemNome=resultado.OrderBy(x=>x.Nome).ToList();
    **Concat**//ComoTipovarresultado=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();
    
        
  • 21.04.2014 / 17:31
    2

    If LazyLoading is enabled, which is the default case.

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

    Returns all records in the Pessoas table sorted by name.

    The only thing that is needed to gain access to the job is to make a cast to Membro .

    Edited according to 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)
                        };
                    }
                });
    
        
    21.04.2014 / 20:43