How to perform a conditional include in Entity Framework

Question

I'm currently using EF and I have two tables, Employees and Orders, given that an Employee have multiple Orders.

I need to perform a query in EF that brings me a list of all Employees with their related Orders, but only those between two dates. If an Employee didn't perform any Order in the given period, his Orders list will be empty.

I figured that I need to start my query from the Employees DbSet, but how can I assign a condition to the Orders property?

I tried to do this, but obviously it didn't work.

public List<Employee> GetAllByIdListAndDateRange(int[] ids, DateTime fromDate, DateTime toDate)
    {
        var query = _context.Set<Employee>().Where(a => ids.Contains(a.EmployeeID)).Include(a => a.Orders.Where(o.OrderDate <= toDate && o.OrderDate >= fromDate));

        return query.ToList();
    }

Any help? I guess I might be missing out on something really simple here.

Answer 1

You cant use a Where predicate in Include, but you can simplify your query if you start from Orders.

If you think it again what you need to query is orders not Employees.

var query = _context.Set<Orders>()
                    .Where(o => o.OrderDate <= toDate &&
                                o.OrderDate >= fromDate && 
                                ids.Contains(o.EmployeeID))
                    .Include(e => e.Employee));