I have entities like below, which has ICollection relations between them. How can I take Sum of MakbuzHareketleri entity's IslemTutari field from Police entity? I think it is necessary to use nested GroupBy, but I couldn't.

public class Police 
{
    public override string Kod { get; set; }
    public EvrakTuru EvrakTuru { get; set; } = EvrakTuru.Police;
    public long AcenteId { get; set; }
    public string Aciklama { get; set; }

    public ICollection<AltPolice> AltPoliceler { get; set; }
}

    public class AltPolice 
{
    public override string Kod { get; set; }
    public long PoliceId { get; set; }
    public PoliceTuru PoliceTuru { get; set; } = PoliceTuru.Police;
    public decimal Prim { get; set; }
    public string Aciklama { get; set; }

    public Police Police { get; set; }
    public virtual ICollection<OdemeBilgileri> OdemeBilgileri { get; set; }
}

    public class OdemeBilgileri
{
    public long AltPoliceId { get; set; }
    public long OdemeTuruId { get; set; }
    public BorcTipi BorcTipi { get; set; } = BorcTipi.MusteriBorcu;
    public DateTime GirisTarihi { get; set; }
    public DateTime Vade { get; set; }        
    public decimal Tutar { get; set; }
    public string Aciklama { get; set; }

    public AltPolice AltPolice { get; set; }
    public virtual ICollection<MakbuzHareketleri> MakbuzHareketleri { get; set; }
}

    public class MakbuzHareketleri
{
    public long MakbuzId { get; set; }
    public int OdemeBilgileriId { get; set; }
    public decimal IslemOncesiTutar { get; set; }
    public decimal IslemTutari { get; set; }
    public DateTime Vade { get; set; }
    public BelgeDurumu BelgeDurumu { get; set; }

    public OdemeBilgileri OdemeBilgileri { get; set; }
}

I can do this in Access as follows:

SELECT Police.PoliceNo, AltPolice.PoliceId, Sum(MakbuzHareketleri.IslemTutari) AS ToplaIslemTutari
FROM Police INNER JOIN ((AltPolice INNER JOIN OdemeBilgileri ON AltPolice.Id = OdemeBilgileri.AltPoliceId) 
INNER JOIN MakbuzHareketleri ON OdemeBilgileri.Id = MakbuzHareketleri.OdemeBilgileriId) ON Police.Id = AltPolice.PoliceId
WHERE (((OdemeBilgileri.BorcTipi)=1))
GROUP BY Police.PoliceNo, AltPolice.PoliceId, MakbuzHareketleri.BelgeDurumu
HAVING (((MakbuzHareketleri.BelgeDurumu)=2 Or (MakbuzHareketleri.BelgeDurumu)=3));

CodePudding user response：

You should start from the MakbuzHareketleri collection and in order to group by multiple columns you should use an anonymous object and for the having clause you should filter the result selection

var result = context.MakbuzHareketleri.Where(r=> r.OdemeBilgileri.BorcTipi == 1)
       .GroupBy(r=> new {r.OdemeBilgileri.AltPolice.PoliceId,r.OdemeBilgileri.AltPolice.Police.PoliceNo,r.BelgeDurumu})
        .Select(r=> new {
                     r.Key.PoliceNo,
                     r.Key.PoliceId,
                     ToplaIslemTutari = r.Where(q=> q.BelgeDurumu == 2 || q.BelgeDurumu == 3)
                                         .Select(r=> r.IslemTutari).Sum()
                }).ToList();    

CodePudding user response：

is there a way to start from top entity, so from Police entity? Is anyone has any idea?