score:1

Accepted answer

try this

var query = _memberRepository.Query()
.Where(x => x.IsActive == true && x.OrgId == orgId)
.Include(x => x.BillDetails.Where(y => y.IsActive == true))
.AsNoTracking()
.ToList();

var memberBills=new List<MemberBill>();

foreach(var m in query)
{
memberBills.Add( new MemberBill 
                  {
                   MemberId= m.Id, 
                   BillAmount=m.BillDetails.Sum(i=> i.Amount)
                  });
}

score:1

Leaving here performant solution. Current accepted answer uses a lot of memory and server resources.

var query = 
    from m in _memberRepository.Query()
    where m => m.IsActive == true && m.OrgId == orgId
    from bd in m.BillDetails.Where(bd => bd.IsActive == true)
    group bd by new { m.Id } into g
    select new MemberBill
    {
        MemberId = g.Key.Id,
        BillAmount = g.Sum(x => x.Amount
    };

var memberBills = query.ToList();

Related Articles