C# - Filter Include - Context

Question

In the method below, I return some records of my entity Consultintramexrules, where it has relation with the . INCLUDE ("Emails and Parameters"). Aware that EF does not allow filtering in . INCLUDE, I would like to check how to filter these entities?

return ctx.ConsultaIntramexRegras
                .Include("Emails")
                .Include("Parametros")                    
                .FirstOrDefault(p => p.Id == id);

I need it filtered as follows:

var regras = ctx.ConsultaIntramexRegras.FirstOrDefault(p => p.Id == id);
            var emails = ctx.ConsultaIntramexRegrasEmails.FirstOrDefault(p => p.Id == id && p.Status);
            var parametros = ctx.ConsultaIntramexRegrasParametros.FirstOrDefault(p => p.Id == id && p.Status == 1);

The Models:

  public class ConsultaIntramexRegras : EntityBase<int, string>
{        
    public string Descricao { get; set; }
    public int Periodicidade { get; set; }
    public int DiaSemanaMes { get; set; }
    public int Horario { get; set; }        
    public bool Status { get; set; }
    public int CodigoConsulta { get; set; }

    public virtual ICollection<ConsultaIntramexRegrasEmails> Emails{ get; set; }
    public virtual ICollection<ConsultaIntramexRegrasParamentros> Parametros { get; set; }
}

   public class ConsultaIntramexRegrasEmails : EntityBase<int, string>
{
    public string Emails { get; set; }
    public bool Status { get; set; }
    public int RegrasID { get; set; }

    public virtual ConsultaIntramexRegras Regras { get; set; }
}
  public class ConsultaIntramexRegrasParamentros : EntityBase<int, string>
{        
    public int RegrasId { get; set; }
    public string Descricao { get; set; }
    public int ParametroId { get; set; }
    public int SequenciaParametro { get; set; }
    public int Tipo { get; set; }
    public string Valor { get; set; }
    public string TrataData { get; set; }
    public int Status { get; set; }

    public virtual ConsultaIntramexRegras Regras { get; set; }
}

Answer 1

I made a model here that has the option "where" in the 3 models and returns ConsultaIntramexRegras.

public ConsultaIntramexRegras obterRegraConsultaIntramexPorId(int id)
{
    ConsultaIntramexRegras regra;
    using (var ctx = new IntramexContext())
    {
        regra = ctx.Set<ConsultaIntramexRegrasParamentros>()
            .Where(p => p.ParametroId == 1)//Where em ConsultaIntramexRegrasParamentros
            .Include(p => p.Regras)
            .Select(p => p.Regras)
            .Where(p => p.Periodicidade == 1)//Where em ConsultaIntramexRegras
            .Include(p => p.Emails)
            .SelectMany(p => p.Emails)
            .Where(p => p.Emails == "")//Where em ConsultaIntramexRegrasEmails
            .Select(p => p.Regras)
            .FirstOrDefault();
    }

    return regra;
}

Answer 2

I resolved it as follows. I avoided the use of third party libraries. It was not the best way, I believe there are other more effective. I will be in search and return if I find simpler solution!

    public ConsultaIntramexRegras obterRegraConsultaIntramexPorId(int id)
    {

        List<ConsultaIntramexRegras> regra;

        using (var ctx = new IntramexContext())
        {
            regra = ctx.ConsultaIntramexRegras
                .Include(p => p.Emails)
                .Include(p => p.Parametros)
                .Where(p => p.Id == id).ToList();
        }

        return regra.Select(p => new ConsultaIntramexRegras
                    {
                        CodigoConsulta = p.CodigoConsulta,
                        DataRegistro = p.DataRegistro,
                        Descricao = p.Descricao,
                        Id = p.Id,
                        Parametros = p.Parametros.Where(s => s.Status == 1).ToList(),
                        Emails = p.Emails.Where(s => s.Status == true).ToList(),
                        Status = p.Status,
                        OperadorRegistroId = p.OperadorRegistroId,
                        DiaSemanaMes = p.DiaSemanaMes,
                        Horario = p.Horario,
                        Periodicidade = p.Periodicidade
                    }).FirstOrDefault();
    }