Linq group by and sum using 3 entitites

Question

I Have some problem with proper translating by Runtime when using linq and group by.

I Have following entities:

Drivers, Settlements, Payments

Drivers has relation one to many to Settlements Payments has realtion one to many to Settlements

I decide as starting point to Group by some driver information from Settlement entity and sum payments with some constrains.

With code something like this linq cannot translate it

var settlementsGrouped = await _databaseContext.Settlements
    .ExcludeDeleted()
    .Where(s => s.DriverId == request.Id && s.DateStart >= request.DateStart && s.DateEnd <= request.DateEnd)
    .GroupBy(s => new {s.Driver.Id, s.Driver.FullName, s.Driver.Address, s.Driver.PostalCode, s.Driver.Town, s.Driver.IdNumber})
    .Select(s => new
    {
        s.Key.FullName,
        s.Key.Address,
        s.Key.PostalCode,
        s.Key.Town,
        s.Key.IdNumber,
        PaymentType = s.Sum(r => r.Payments.Where(p => p.IsActive && p.Type == PaymentEntityTypeEnum.Common).Sum(p => p.Amount)),
        AdvancePaymentType = s.Sum(r => r.Payments.Where(p => p.IsActive && p.Type == PaymentEntityTypeEnum.Advance).Sum(p => p.Amount)),
    }).ToListAsync(cancellationToken);

The LINQ expression 'GroupByShaperExpression:
KeySelector: new { 
    Id = d.Id, 
    FullName = d.FullName, 
    Address = d.Address, 
    PostalCode = d.PostalCode, 
    Town = d.Town, 
    IdNumber = d.IdNumber
 }, 
ElementSelector:EntityShaperExpression: 
    EntityType: Settlement
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False

    .Sum(r => r.Payments
        .Where(p => p.IsActive && (int)p.Type == 1)
        .Sum(p => p.Amount))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Is it possible to get this information by one query?

I need result something like i write on .Select . I know that problem is with sum, but i cant find the way to solve this problem.

Answer 1

Try the following query:

var query = 
    from s in _databaseContext.Settlements
        .ExcludeDeleted()
        .Where(s => s.DriverId == request.Id && s.DateStart >= request.DateStart && s.DateEnd <= request.DateEnd)
    from p in s.Payments
    where p.Isactive
    group p by new { s.Driver.Id, s.Driver.FullName, s.Driver.Address, s.Driver.PostalCode, s.Driver.Town, s.Driver.IdNumber } into g
    select new
    {
        g.Key.FullName,
        g.Key.Address,
        g.Key.PostalCode,
        g.Key.Town,
        g.Key.IdNumber,
        PaymentType = g.Sum(x => x.Type == PaymentEntityTypeEnum.Common ? x.Amount : 0),
        AdvancePaymentType = g.Sum(x => x.Type == PaymentEntityTypeEnum.Common ? x.Amount : 0),
    };

var settlementsGrouped = await query.ToListAsync(cancellationToken);