Entity Framework Include With Where Clause
How to Include with Where clause?
To retrieve some information from the database and also want to include related entities conditionally. For example, if we have a simple model containing two entities, Customers, and Invoices. Now to retrieve any customer information and also include all the invoices of that customer generated in last seven days.
using (var context = new EntityContext()) { var fromDate = DateTime.Now.AddDays(-7); var customer = context.Customers.Where(c => c.CustomerID == 1) .Include(c => c.Invoices) .Where(c => c.Invoices.Any(i => i.Date >= fromDate)) .FirstOrDefault(); }
Now when you execute the above example, you will see that it will retrieve the customer with id equal to 1 and will include all the invoices. That is because the where clause is just acting on the customer but not on Invoices.
StackOverflow Related Questions
Answer
There are different ways to solve this issue, let's use the projection query.
using (var context = new EntityContext()) { var fromDate = DateTime.Now.AddDays(-7); var customer = context.Customers.Where(c => c.Id == 1) .Where(c => c.Invoices.Any(i => i.Date >= fromDate)) .Select(c => new { c, Invoices = c.Invoices.Where(i => i.Date >= fromDate) }) .FirstOrDefault(); }
Now you will see that you have an anonymous type which has two properties, c, and Invoices.
A property called c is of type Customer, and it has all the invoices, the other property Invoices will have only those generated in the last seven days.
Third Part Library
Entity Framework Plus
Entity Framework Plus Query IncludeFilter feature allow filtering related entities. This library makes this a lot easier.
using (var context = new EntityContext()) { var fromDate = DateTime.Now.AddDays(-7); var customer = context.Customers.Where(c => c.CustomerID == 1) .IncludeFilter(c => c.Invoices.Where(i => i.Date >= fromDate)) .FirstOrDefault(); }
The IncludeFilter method works the same as Include method but lets you use LINQ Queryable extension methods as part of the query to filter related entities.
ZZZ Projects