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.

possible duplicate of [Entity Framework .Where nested in .Include](http://stackoverflow.com/questions/18709386/entity-framework-where-nested-in-include) — DavidG, Sep 02 '15 at 14:02
use left outer join.refer to this link:http://stackoverflow.com/questions/3404975/left-outer-join-in-linq — Arash, Sep 02 '15 at 14:05

bto.rdz · Answer 1 · 2015-09-02T15:22:39.963

2

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));

edited Sep 02 '15 at 15:22

answered Sep 02 '15 at 14:06

bto.rdz

6,636
4
35
52

1

This would almost solve my problem, but I intend to iterate through the `Employee` DbSet. Using your answer, I wouldn't be able to retrieve an Employee that had no Orders. Thanks for your help :) – Lucas Ribeiro Sep 02 '15 at 14:21

score 1 · Answer 2 · answered Sep 02 '15 at 14:16

1

try to replace

myQuery.Include(x=>x.Y).Load();

by :

myQuery.Load();
myQuery.SelectMany(x=>x.y).Where( ....).Load();

answered Sep 02 '15 at 14:16

Seb

1,230
11
19

How to perform a conditional include in Entity Framework

2 Answers2