//------------------------------ Entity Dashboard Query ------------------------------// public static IQueryable <EntityGroup> GetEntityDashboard() { var careers = db.Careers.Include(e => e.Employee.EmployeeStatus.Select(c => c.EmployeeStatus)) .Where(e => //Select all active employees (e.Employee.EmployeeStatus.GroupBy(x => x.EmployeeID) .Select(y => y.OrderByDescending(i => i.EffectiveDate) .FirstOrDefault()).Select(c => c.EmployeeStatus.Name).FirstOrDefault() == "Active") || //OR all contract employees (e.Employee.EmployeeStatus.GroupBy(x => x.EmployeeID) .Select(y => y.OrderByDescending(i => i.EffectiveDate) .FirstOrDefault()).Select(c => c.EmployeeStatus.Name).FirstOrDefault() == "Contractor")) // AND Select most recent career records .Include(x => x.Employee) .GroupBy(x => x.EmployeeID) .Select(y => y.OrderByDescending(i => i.EffectiveDate) .FirstOrDefault()); IQueryable <EntityGroup> data = from employee in careers group employee by new { employee.BudgetArea.Abbreviation, employee.Department.Name } into EntityGroup select new EntityGroup() { BudgetArea = EntityGroup.Key.Abbreviation, Department = EntityGroup.Key.Name, BAreaCount = EntityGroup.Count(), DeptCount = EntityGroup.Count(), Employees = careers.Where(e => e.Department.Name.Equals(EntityGroup.Key.Name)).Select(e => e.Employee).ToList() }; return(data); }
public static IQueryable<EntityGroup> GetDepartmentDashboard() { IQueryable<EntityGroup> data = from employee in db.Careers .Include(e => e.Employee.EmployeeStatus.Select(c => c.EmployeeStatus)) .Where(e => (e.Employee.EmployeeStatus.GroupBy(x => x.EmployeeID) .Select(y => y.OrderByDescending(i => i.EffectiveDate) .FirstOrDefault()).Select(c => c.EmployeeStatus.Name).FirstOrDefault() == "Active") || (e.Employee.EmployeeStatus.GroupBy(x => x.EmployeeID) .Select(y => y.OrderByDescending(i => i.EffectiveDate) .FirstOrDefault()).Select(c => c.EmployeeStatus.Name).FirstOrDefault() == "Contractor")) .Include(x => x.Employee) .GroupBy(x => x.EmployeeID) .Select(y => y.OrderByDescending(i => i.EffectiveDate) .FirstOrDefault()) group employee by employee.Department.Name into EntityGroup select new EntityGroup() { Department = EntityGroup.Key, DeptCount = EntityGroup.Count() }; return data; }