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.

CodePudding user response：

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);

CodePudding user response：

Yes, your code above works, thank you very much :). Also i provide another way to achieve this solution and translate your to method style

            var driverInfo = await _databaseContext.Payments
            .Where(p => !p.Settlement.Driver.IsDeleted)
            .Where(p => p.Settlement.DateStart >= request.DateStart && p.Settlement.DateEnd <= request.DateEnd)
            .Where(p => p.IsActive)
            .Where(p => p.Settlement.DriverId == request.Id)
            .GroupBy(p => new
            {
                p.Settlement.Driver.Id, p.Settlement.Driver.FullName, p.Settlement.Driver.Address,
                p.Settlement.Driver.PostalCode, p.Settlement.Driver.Town, p.Settlement.Driver.IdNumber
            })
            .Select(p => new
            {
                p.Key.FullName,
                p.Key.Address,
                p.Key.PostalCode,
                p.Key.Town,
                p.Key.IdNumber,
                PaymentsSum = p.Where(r => r.Type == PaymentEntityTypeEnum.Common).Sum(r => r.Amount),
                AdvancePaymentsSum = p.Where(r => r.Type == PaymentEntityTypeEnum.Advance).Sum(r => r.Amount),
            }).FirstOrDefaultAsync(cancellationToken);


        var test2 = await _databaseContext.Settlements
            .ExcludeDeleted()
            .Where(s => s.DriverId == request.Id && s.DateStart >= request.DateStart &&
                        s.DateEnd <= request.DateEnd)
            .SelectMany(s => s.Payments, (settlement, payment) => new { settlement, payment })
            .Where(x => x.payment.IsActive)
            .GroupBy(x => new
            {
                x.settlement.Driver.Id,
                x.settlement.Driver.FullName,
                x.settlement.Driver.Address,
                x.settlement.Driver.PostalCode,
                x.settlement.Driver.Town,
                x.settlement.Driver.IdNumber
            }, x => x.payment)
            .Select(x => new
            {
                x.Key.FullName,
                x.Key.Address,
                x.Key.PostalCode,
                x.Key.Town,
                x.Key.IdNumber,
                PaymentType = x.Sum(r => r.Type == PaymentEntityTypeEnum.Common ? r.Amount : 0M),
                AdvancePaymentType = x.Sum(r => r.Type == PaymentEntityTypeEnum.Advance ? r.Amount : 0M),
            }).FirstOrDefaultAsync(cancellationToken);

Could you explain me why we need this selectMany function to achieve the result?
And also i understand that linq selectMany does not materialize query until FirstOrDefault is called?