林克留下了加入和计数-如何

本文关键字：如何 林克 | 更新日期: 2023-09-27 18:21:50

我有以下Linq表达式：

var employeeTypes = from t in DbContext.Set<SetupEmployeeType>().AsNoTracking()
                    join emp in DbContext.Set<Employee>().AsNoTracking() on t.EmployeeTypeId equals emp.EmployeeTypeId into employee
                    from subemp in employee.DefaultIfEmpty()
                    where t.MasterEntity == masterEntity
                    select new Model.SetupEmployeeTypeModel()
                    {
                        EmployeeTypeId = t.EmployeeTypeId,
                        Description = t.Description,
                        AllowProbation = t.AllowProbation,
                        IsActive = t.IsActive,
                        TotalEmployee = (subemp == null ? 0 : subemp.Count) 
                    };

我需要设置自定义模型的TotalEmployee属性。

因此，如果没有与任何员工关联的EmployeeTypeId，则TotalEmployee应为0，否则应为员工计数。

知道怎么做吗？

林克留下了加入和计数-如何

如果我可能没有错，那么考虑使用子查询，比如so-

var q = from empType in DbContext.Set<SetupEmployeeType>().AsNoTracking()
        let empCount =
        (
          from emp in DbContext.Set<Employee>().AsNoTracking()
          where empType.EmployeeTypeId == emp.EmployeeTypeId
          select emp
        ).Count()
        select new Model.SetupEmployeeTypeModel()
        {
            EmployeeTypeId = empType.EmployeeTypeId,
            Description = empType.Description,
            AllowProbation = empType.AllowProbation,
            IsActive = empType.IsActive,
            TotalEmployee = empCount
        };

使用分组依据

var q = from empType in DbContext.Set<SetupEmployeeType>().AsNoTracking()
        join empCnt in 
            (
                from emp in DbContext.Set<Employee>().AsNoTracking()
                group emp by emp.EmployeeTypeId into grp
                select new { EmployeeTypeId = grp.Key, TotalEmp = grp.Count()}
            ) on empType.EmployeeTypeId equals empCnt.EmployeeTypeId into employees
        from subemp in employees.DefaultIfEmpty()
        where t.MasterEntity == masterEntity
        select new Model.SetupEmployeeTypeModel()
        {
            EmployeeTypeId = empType.EmployeeTypeId,
            Description = empType.Description,
            AllowProbation = empType.AllowProbation,
            IsActive = empType.IsActive,
            TotalEmployee = subemp.TotalEmp
        };

我提出了以下解决方案：

 var employeeTypes = from t in DbContext.Set<SetupEmployeeType>().AsNoTracking()
                                join empg in
                                    (
                                        from emp in DbContext.Set<Employee>().AsNoTracking()
                                        group emp by emp.EmployeeTypeId into g
                                        select new { EmployeeTypeId = g.Key, Total = g.Count() }
                                    ) on t.EmployeeTypeId equals empg.EmployeeTypeId into employee
                                from subemp in employee.DefaultIfEmpty()
                                where t.MasterEntity == masterEntity
                                select new Model.SetupEmployeeTypeModel()
                                {
                                    EmployeeTypeId = t.EmployeeTypeId,
                                    Description = t.Description,
                                    AllowProbation = t.AllowProbation,
                                    IsActive = t.IsActive,
                                    TotalEmployee = subemp.Total 
                                };
相关文章：