how do update this query of EF

Question

i have two table Employeestatus and EmployeeCategory
i am using following query to get my data, its working ok but i need modification in it . new query IN EF

 var sectorEmployees = from ec in entities.EmployeeCategories.Where(x => x.Category_Id == 1 || x.Category_Id == 2
 && x.Sector_Id == sectorId)                             
          join es in entities.EmployeeStatus.Where(x => x.EmployeeStatusType_Id == 1)
 on ec.Employee_Id equals es.Employee_Id                                 
      select es;

BUT problem is that there are multiple record against one employee but i want last records of that employee from the table, i was using following query that was fullfilling my needs

var sectorEmployees = _service.GetEmployeeStatusBySector_Id(sectorId).OrderByDescending(x => x.EndDate)
.GroupBy(x => x.Employee_Id).Select(x => x.LastOrDefault()).ToList();

how can i can modify 1st query to full fill my needs that is done by 2nd query.

Sajid Hussain · Answer

this is my solution that full fill all requirement var sectorEmployees = entities.EmployeeCategories.Where(x => x.Category_Id == 1 || x.Category_Id == 2 && x.Sector_Id == sectorId); var sectorEmployeesByStatuses = sectorEmployees .Join( entities.EmployeeStatus //table to join to sEmployees=>sEmployees.Employee_Id //source tables join value statuses=>statuses.Employee_Id //join tables join value (sEmployeesstatuses)=> new {sEmployeesstatuses}) //selector (create an anon type for now. Select the bits you want later .GroupBy(anonType => anonType.sEmployees) // group by employee .Select(group=> new {group.keygroup.OrderByDescending(anonType => anonType.statuses.EndDate).FirstOrDefault().statuses}) //new anon pairing only latest status .Where(anonType=>anonType.statuses.EmployeeStatusType_Id == 1 ) //trim out the employees that don't have this type status currently (or latest) .Select(anonType=>anonType.sEmployees).ToList(); //select the employee types so we get a neat List entities.employeecategory >

Ramesh Maruthi · Answer

Hi Saas Please see the below query : var sectorEmployees = (from ec in entities.Employee join es in entities.EmployeeStatus on ec.Employee_Id equals es.Employee_Id where(ec.Category_Id == 1 || ec.Category_Id == 2 && ec.Sector_Id == sectorId) &&(es.EmployeeStatusType_Id == 1) Orderby es.EndDate descending group es by es.Employee_id into g select g.LastOrDefault());