CODEWITHSUNDEEP
c#entity-framework
Lucas Ribeiro
Lucas Ribeiro

Reputation: 125

How to perform a conditional include in Entity Framework

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.

Upvotes: 5

Views: 3365

Answers (2)

bto.rdz
bto.rdz

Reputation: 6720

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

Upvotes: 2

Seb
Seb

Reputation: 1230

try to replace 

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

by :

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

Upvotes: 1

Related Questions