Query with Leftjoins using Groupby Includes are not performed EF.Core C#

Asked

Viewed 38 times

0

I need to perform a query with the Entity framework where I have an entity Offer and in it I have several Cards (Entity).

In the query I am performing the card include, and using Selectmany to perform a filter to get only the active offer cards. But when Groupby is performed in the query EF Core is not performing the includes.

Note: As I have two cards in the offer, I need to perform the group by not to repeat the offer, and when I don’t use the group by offer comes with the includes but with the double offer.

Entity (Offer)

public class Oferta : EntityBase
{
    public Oferta()
    {
        Cartoes = new HashSet<OfertaCartao>();     

    }

    public long? OfertaId { get; set; }
    public bool IndicadorSituacao { get; set; }
    public string DescricaoOferta { get; set; }        
    public DateTime DataVigenciaInicial { get; set; }
    public DateTime? DataVigenciaFinal { get; set; }        
    public IdentificacaoEtapaFluxo IdentificacaoPac { get; set; }
    public IdentificacaoSegmentoProspect IdentificacaoSegmentoProspect { get; set; }
    public virtual ICollection<OfertaCartao> Cartoes { get; set; }        

}

Entity (Charter)

public class OfertaCartao : EntityBase
{
    public OfertaCartao()
    {
        OfertaCartaoBeneficios = new HashSet<OfertaCartaoBeneficio>();
    }

    public long CartaoId { get; set; }
    public long? OfertaId { get; set; }
    public virtual Oferta Oferta { get; set; }
    public IdentificacaoEtapaFluxo IdentificacaoPac { get; set; }
    public IdentificacaoSegmentoProspect IdentificacaoSegmentoProspect { get; set; }
    public long? TermoCondicaoId { get; set; }
    public virtual TermoCondicao TermoCondicao { get; set; }
    public string DescricaoCartao { get; set; }
    public string TextoAnuidadeCartao { get; set; }
    public bool IndicadorSituacao { get; set; }
    public DateTime DataVigenciaInicial { get; set; }
    public DateTime? DataVigenciaFinal { get; set; }
    public string IdentificacaoArquivoPoliticaIsencaoDeTarifas { get; set; }        
    public IdentificacaoBandeiraCartao? IdentificacaoBandeiraCartao { get; set; }
    public IdentificacaoModalidadeCartao? IdentificacaoModalidadeCartao { get; set; }
    public virtual ICollection<OfertaCartaoBeneficio> OfertaCartaoBeneficios { get; set; }
}

Query

        var result = _aberturaDeContasContext.Ofertas
            .Include(x => x.Cartoes)                    
            .Where(where)
            .Where(x => x.IndicadorSituacao)
            .WhereInVigencia()
            .SelectMany(sm => sm.Cartoes.Where(w => w.IndicadorSituacao).DefaultIfEmpty(), (key, inner) => key)
            .GroupBy(x => x)                
            .Select(x => x.First());
  • is Entityframeworkcore or the version 6.x?

  • It is the Entityframeworkcore

  • If you only load the offers and after the charge of the interface it would not be simpler?

  • But when it comes to performance, that’s unfeasible, right? This is even a simple question, but I would like to know how to solve this by actually performing a pure SQL with Efcore.

  • Why is it impossible? performance, it is not so expensive that works Efcore, of course you can make the pure SQL it gives you this option, but, I would load Offers and then with Context.Entry(entity).Collection would load relations!!! has no performance loss!

  • 1

    Loss of performance is your expression that does a lot of things and cause ai yes slowness...

  • @Virgilionovic, could you give me an example of how to perform this Join and filter on Join?

  • Explain to me what you want to bring offers and ??? explains me the result.

  • I need a query to find the active offers, and perform left Join with the offered table card (where the offer can have multiple cards) EX: SELECT * FROM ACDTOFE OFFER LEFT ACDTOFC JOIN OFFER CARD OFFER.ID_OFER = OFFERCARD.ID_OFER AND&#xA; OFERTA.ID_PAC = OFERTACARTAO.ID_PAC &#xA; AND&#xA; OFERTA.ID_SEGM_PROSP = OFERTACARTAO.ID_SEGM_PROSP&#xA; AND &#xA; OFERTACARTAO.IC_SIT_OFER_CRTO = 1&#xA; WHERE&#xA; OFERTA.IC_SIT_OFER = 1

  • uses Fromsql is best in your case ... and you have a junction with multiple filters inside ...

Show 5 more comments
No answers

Browser other questions tagged

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