Join, Group by and Sum in Linq-CodePudding

I want to group overtime work hours of employees by employeeId and get data of employeeNames and total monthly overtime work hours of employees. But this Linq query gives translation error.

var results = 
    from overtime in context.Overtimes
    join employeeCredential in context.EmployeeCredentials
        on overtime.EmployeeId equals employeeCredential.id
    join employeeDetail in context.EmployeeDetails
        on employeeCredential.id equals employeeDetail.employeeId
    where overtime.Month == month && overtime.Year == year
    group new { overtime, employeeCredential, employeeDetail } by overtime.EmployeeId into g
    select new MonthlyOvertimeWorkHours
    {
        EmployeeName = g.First().employeeDetail.employeeName,
        TotalWorkHourOfMonth = g.Sum(t => t.overtime.OvertimeWorkHour)
    };

Error

{ "error": employeeCredential\r\n IsNullable: True\r\n , \r\n employeeDetail = EntityShaperExpression: \r\n EntityType: EmployeeDetail\r\n ValueBufferExpression: \r\n ProjectionBindingExpression: employeeDetail\r\n IsNullable: True\r\n }\r\n .Select(s => s.employeeDetail.employeeName)\r\n .First()' 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'. }

CodePudding user response：

Until EF Core 6, you cannot access to records after grouping (which is usually bad). Only aggregates and Key is available.

To fix issue just add employeeName to grouping key.

var results = 
    from overtime in context.Overtimes
    join employeeCredential in context.EmployeeCredentials
        on overtime.EmployeeId equals employeeCredential.id
    join employeeDetail in context.EmployeeDetails
        on employeeCredential.id equals employeeDetail.employeeId
    where overtime.Month == month && overtime.Year == year
    group new { overtime, employeeCredential, employeeDetail } by new { overtime.EmployeeId, employeeDetail.employeeName } into g
    select new MonthlyOvertimeWorkHours
    {
        EmployeeName = g.Key.employeeName,
        TotalWorkHourOfMonth = g.Sum(t => t.overtime.OvertimeWorkHour)
    };