Include With Where Clause

Ever thought about writing the following query in Entity Framework?

xxxxxxxxxx

Select * From Employees e

left join Employees_Attendance ea on e.id=ea.employee_id and ea.date between {{startdate}} and {{enddate}}

In the above query, {{startdate}} and {{enddate}} are the parameters. This query will list all of the employees, but only the attendance that are between startdate and enddate. 

While working on a project, it seems very hard to do this using the standard EF Core library, so I found an online solution available I thought worth sharing.

A library has a lot of useful functionalities, including the one we are trying to solve, that can be downloaded from https://entityframework-plus.net/download.

While using that library's extension method IncludeFilter, the above query can be written in EF Core as follows:

xxxxxxxxxx

_dbContext.Employees

    .IncludeFilter(x => x.EmployeeAttendance.Where(y => y.Date >= {{StartDate}} && y.Date <= {{EndDate}}))

StartDate and EndDate are the parameters that can be replaced by the parameter in your solutions.

Thanks for reading. Happy coding!

Further Reading

Consider Using [NOT] EXISTS Instead of [NOT] IN