How to get one record from list with condition of Date Descending

Question

Dear all I have a table with the following columns , Now I have two three rows with Department

Employee Table

Emp_ID	First_Name	Last_Name
1	NEW	OLD
2	FOG	HOP

Employee Detail table

Table_ID	Emp_ID	Department	Salary	Visit_Date
1	1	HR	1200	5/5/2021
2	2	HR	1300	8-8-2022
3	3	ICT	1400	01-01-2022

Now I want to get the employee detail with name Department,salary,Visit_Date I want to get only HR department employee and from HR only one Row whose date is latest date like 8-8-2022

below is my current query.

var MyQuery=(from nn in db.EmployeeDetail

from vv in db.Employee

where vv.Emp_ID=nn.Emp_ID

order by vv.department

select new EmployeeDetail

{

visit_date=nn.Visit_Date,

Salary=nn.Salary).FirstOrDefault(),

}).ToList();

but it return two rows of HR i need one row with latest visit Date.

}

Vishal Yelve · Answer

Hi Mark Please find your expected result public class Employee { public int EmpID { get; set; } public string FName { get; set; } public string LName { get; set; } } public class EmployeeDetail { public int Id { get; set; } public int EmpID { get; set; } public string Department { get; set; } public int Salary { get; set; } public string VisitDate { get; set; } } List employees = new List (); employees.Add(new Employee() { EmpID = 1 FName = New LName = OLD }); employees.Add(new Employee() { EmpID = 2 FName = FOG LName = HOP }); List employeesDetails = new List (); employeesDetails.Add(new EmployeeDetail() { Id = 1 EmpID = 1 Department = HR Salary = 1200 VisitDate = 05/05/2021 }); employeesDetails.Add(new EmployeeDetail() { Id = 2 EmpID = 2 Department = HR Salary = 1300 VisitDate = 08/08/2022 }); employeesDetails.Add(new EmployeeDetail() { Id = 3 EmpID = 3 Department = ICT Salary = 1400 VisitDate = 01/01/2022 }); var result = (from ed in employeesDetails join e in employees on ed.EmpID equals e.EmpID orderby ed.VisitDate descending where ed.Department == HR select new { Name = e.FName +  + e.LName Department = ed.Department Salary = ed.Salary VisitDate = ed.VisitDate }).FirstOrDefault();

Sachin Singh · Answer

The better select the latest HR row using order by var MyQuery=(from nn in db.EmployeeDetail join vv in db.Employee on vv.Emp_ID=nn.Emp_ID where nn.Department= 'HR' order by nn.VisitDate desc select new EmployeeDetail { visit_date=nn.Visit_Date Salary=nn.Salary }).FirstOrDefault();

Mark Tabor · Answer

NONE OF THEM RETURN ONE RESULT THEY ALL RETURN BOTH RESULTS  sECONLY sachin MAX is not a function

Mark Tabor · Answer

on max it gives error

Vishal Yelve · Answer

Try this var q = from n in db.employeedetails group n by n.department into g select g.OrderByDescending(t=>t.Visit_Date).FirstOrDefault();

Sachin Singh · Answer

test this var MyQuery=(from nn in db.EmployeeDetail from vv in db.Employee where vv.Emp_ID=nn.Emp_ID and nn.VisitDate=Max(nn.VisitDate) order by vv.department select new EmployeeDetail { visit_date=nn.Visit_Date Salary=nn.Salary }).ToList();