How would I write this dynamic linq query?

Question

Hi all,

Basically I want to write a linq query to order the number of days they were present. But I have got these six time filters- Today,Yesterday,current month,previous month,current year,previous year.So now I have this queries which I have simplified but before these queries below, I actually order these employees on different aspects and after ordering it as you can see I assign rank and then at the same time find out his count(which may or may not be used to rank them later)-

var result=datacontext.Employee(c=>c.Company.Id==companyId).Select((k, index) => new EmployeeDTO()
{
  EmployeeId=k.Employee.Id,
  CompanyId=Employee.Company.Id
  PresentCount=(from e in employeeAttendance
                 where d.RecNum == k.recnum
                 && d.date_present.Year == DateTime.Today.Year
                 && d.date_present.Month == DateTime.Today.Month
                 && d.date_present.Day == DateTime.Today.Day
                 select d).Count()  
}

So now when the filter is say previous year I have -

var result=datacontext.Employee(c=>c.Company.Id==companyId).Select((k, index) => new EmployeeDTO()
{
  Position=
  EmployeeId=k.Employee.Id,
  CompanyId=Employee.Company.Id
  PresentCount=(from e in employeeAttendance
                 where d.RecNum == k.recnum
                 && d.date_present.Year == (DateTime.Today.Year-1)

}

and if have it on Current Month I have -

var result=datacontext.Employee(c=>c.Company.Id==companyId).Select((k, index) => new EmployeeDTO()
{
  EmployeeId=k.Employee.Id,
  CompanyId=Employee.Company.Id
  PresentCount=(from e in employeeAttendance
                 where d.RecNum == k.recnum
                 && d.date_present.Month == DateTime.Today.Month
                 && d.date_present.Year == DateTime.Today.Year

}

I basically want to combine all these in one query with basically having like a dynamic clause for finding out the present count ?

Answer 1

Just create a simple wrapper. Eg:

IQueryable<EmployeeDTO> GetEmployeeCount(Expression<Func<DateTime, bool>> pred)
{
  var result=datacontext.Employee(c=>c.Company.Id==companyId).
     Select((k, index) => new EmployeeDTO()
  {
    EmployeeId=k.Employee.Id,
    CompanyId=Employee.Company.Id,
    PresentCount=(from e in employeeAttendance
                  where d.RecNum == k.recnum && pred(d.date_present)
                  select d).Count()  
  });
  return result;
}

Usage:

var r = GetEmployeeCount(d => d.Year == (DateTime.Today.Year-1));

var r = GetEmployeeCount(
   d => d.Month == DateTime.Today.Month && d.Year == DateTime.Today.Year);