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
?– novic
It is the Entityframeworkcore
– Nicola Bogar
If you only load the offers and after the charge of the interface it would not be simpler?
– novic
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.
– Nicola Bogar
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!– novic
Loss of performance is your expression that does a lot of things and cause ai yes slowness...
– novic
@Virgilionovic, could you give me an example of how to perform this Join and filter on Join?
– Nicola Bogar
Explain to me what you want to bring offers and ??? explains me the result.
– novic
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
 OFERTA.ID_PAC = OFERTACARTAO.ID_PAC 
 AND
 OFERTA.ID_SEGM_PROSP = OFERTACARTAO.ID_SEGM_PROSP
 AND 
 OFERTACARTAO.IC_SIT_OFER_CRTO = 1
 WHERE
 OFERTA.IC_SIT_OFER = 1
– Nicola Bogar
uses Fromsql is best in your case ... and you have a junction with multiple filters inside ...
– novic