Return data from a Linq query to an EF Core repository

Question

I have the Person class that relates to the Personal and Personal Tables in a one-on-one relationship. In my table Person I have the field Personnature where 0 = Person and 1 = Legal Person.

A person can be physical or legal and who defines this is the Personal Fieldnature. There is a way for me to give a include in the Personal table, only if Personnature is equal to 0 and to give a include in the Personal table only for Personnature is equal to 1?

public class PessoaRepository : Repository<Pessoa>, IPessoaRepository
{
        public PessoaRepository(SistemaComercialContext context)
            :base(context)
        {

        }

        public IQueryable<Pessoa> GetAllJoin()
        {
            return DbSet //DbSet Pessoa
                .Include("PessoaFisica")
                .Include("PessoaJuridica")
                .AsNoTracking();
        }        
}

Answer 1

There is no "pretty" way for you to do this, since the Include cannot stand conditions.

One way is to separate it into two queries and then put it together:

return DbSet.Where(p => p.PessoaNatureza == 0)
            .Include("PessoaFisica")
            .Concat(
                   DbSet.Where(p => p.PessoaNatureza == 1)
                        .Include("PessoaJuridica")
            ).AsNoTracking();

That way you keep working at the bank and get the result you expect.

There are other ways, using anonymous objects to return the data and then filter in memory. An example at this link which also contains a code for an extension of IQueryable to this "problem" of Include.

Answer 2

With Entity Framework Core there is a possibility to load the relations and/or the collections subsequently, example:

using (Db db = new Db())
{
    var pessoa = db.Pessoa.Find(1);
    if (pessoa.PessoaNatureza == 0)
    {
        db.Entry(pessoa)
            .Reference(x => x.PessoaFisica)
            .Load(); // Carregando só se for Pessoa Fisica
    }
    else
    {
        db.Entry(pessoa)
            .Reference(x => x.PessoaJuridica)
            .Load(); // Carregando só se for Pessoa Juridica
    }
}

this type of loading is a new feature, which even has to filter the elements that could be loaded, but, is not the case in specific.

In your layer could drive that same code with some changes, example:

public class PessoaRepository : Repository<Pessoa>, IPessoaRepository
{
    public PessoaRepository(SistemaComercialContext context)
        :base(context)
    {

    }

    public List<Pessoa> GetAllJoin()
    {
        var pessoas = context.Pessoa.ToList();
        pessoas.ForEach(x =>
        {                    
            if (x.PessoaNatureza == 0)
            {
                context.Entry(x)
                    .Reference(f => f.PessoaFisica)
                    .Load();
            }
            else
            {
                context.Entry(x)
                    .Reference(j => j.PessoaJuridica)
                    .Load();
            }
        });
        return pessoas;     
    }        
}

About performance, if the registration amount is too large can have low performance, then you have to consider all this, maybe a results pagination would be ideal, but, wanted to show the loading feature of the relations after loading some data.