how do update this query of EF

Sajid Hussain

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.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sajid Hussain11y ago

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         (sEmployees,statuses)=>new{sEmployees,statuses}) //selector (create an anon type for now.  Select the bits you want later     .GroupBy(anonType => anonType.sEmployees) // group by employee     .Select(group=>new{group.key,group.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 Maruthi11y ago
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());