I encountered an error when using the left join with three queryables.

[denisding]

I encountered an error when using the left join with three queryables. The first three queryables work fine on their own with await ToListAsync(), but when I use them in the final query, it results in an error, and I'm not sure where the issue lies."

var taskCountQuery = from taskStep in _taskStepRepository.DetachedEntities
                                 where taskStep.HumanStep &&
                                       (taskStep.FinishAt == null || taskStep.SelAction == SelActionEnum.Approve.ToString() || taskStep.SelAction == SelActionEnum.Return.ToString() )
                                 group taskStep by taskStep.OwnerAccount into grouped1
                                 let count = grouped1.Count()
                                 select new TaskCountModel
                                 {
                                     OwnerAccount = grouped1.Key,
                                     TaskCount = count
                                 };
var taskDurationQuery = from taskStep in _taskStepRepository.DetachedEntities
                        where taskStep.HumanStep && taskStep.FinishAt != null &&
                              (taskStep.SelAction == SelActionEnum.Approve.ToString() || taskStep.SelAction == SelActionEnum.Return.ToString())
                        let processingDuration = taskStep.FinishAt - taskStep.ReceiveAt
                        group processingDuration by taskStep.OwnerAccount into grouped2
                        let count = grouped2.Count()
                        select new AvgDurationModel
                        {
                            OwnerAccount = grouped2.Key,
                            AvgDuration = count > 0 ? grouped2.Sum(duration => duration.Value.TotalSeconds) / count : 0
                        };
var userQuery = from user in _userRepository.DetachedEntities
                join staff in _staffRepository.DetachedEntities on user.HRID equals staff.HRID
                join roleMember in _roleMemberRepository.DetachedEntities
                      on new { user.SID, SIDType = SIDTypeEnum.UserSID.ToString() }
                      equals new { roleMember.SID, roleMember.SIDType }
                join role in _roleRepository.DetachedEntities on roleMember.RoleCode equals role.RoleCode
                join roleGroupMember in _roleGroupMemberRepository.DetachedEntities on role.SID equals roleGroupMember.SID
                join roleGroup in _roleGroupRepository.DetachedEntities on roleGroupMember.GroupCode equals roleGroup.GroupCode
                select new MonthWorkload
                {
                    GroupCode = roleGroup.GroupCode,
                    GroupName = roleGroup.GroupName,
                    UserAccount = user.Account,
                    UserName = staff.Name
                };

var list = await (from user in userQuery
                  join taskCountRow in taskCountQuery on user.UserAccount equals taskCountRow.OwnerAccount into taskCountJoin
                  from taskCountRowEnd in taskCountJoin.DefaultIfEmpty() // 左连接任务计数  
                  join taskDurationRow in taskDurationQuery on user.UserAccount equals taskDurationRow.OwnerAccount into taskDurationJoin
                  from taskDurationRowEnd in taskDurationJoin.DefaultIfEmpty() // 左连接任务持续时间  
                  select new MonthWorkload
                  {
                      GroupCode = user.GroupCode,
                      GroupName = user.GroupName,
                      UserAccount = user.UserAccount,
                      UserName = user.UserName,
                      TaskCount = taskCountRowEnd.TaskCount,
                      AvgDuration = taskDurationRowEnd.AvgDuration
                  }).ToListAsync();

The LINQ expression 'DbSet<BizTaskStep>()
    .Where(taskStep => taskStep.HumanStep && taskStep.FinishAt == null || taskStep.SelAction == "Approve" || taskStep.SelAction == "Return")
    .GroupBy(taskStep => taskStep.OwnerAccount)
    .Select(grouped1 => new { 
        grouped1 = grouped1, 
        count = grouped1
            .AsQueryable()
            .Count()
     })' could not be translated. Additional information: Translation of 'Select' which contains grouping parameter without composition is not supported. 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.

[maumar]

@denisding please provide the full standalone repro. We need to know your model configuration in order to investigate this.

[denisding]

Sorry, there are some issues with my code:

It seems that the same table alias taskStep cannot be used in the JOIN. Calculating the time difference cannot be directly done with grouped2.Sum(duration => duration.Value.TotalSeconds) because I'm using Oracle. A custom method is needed.