Entity Framework Core Plus Query IncludeFilter
Description
With Entity Framework, "Include" method is often used to load related entities / child collections. However, the method doesn't let you use LINQ queryable methods like Where to filter entities to include which is a major drawback. Excluding soft deleted records or limiting the result is a frequent real-life scenario which the "Include" method doesn't support.
EF+ Query IncludeFilter lets you filter and perform LINQ queryable methods on related entities.
// using Z.EntityFramework.Plus; // Don't forget to include this. var ctx = new EntitiesContext(); // LOAD orders and the first 10 active related entities. var list = ctx.Orders.IncludeFilter(x => x.Items.Where(y => !y.IsSoftDeleted) .OrderBy(y => y.Date) .Take(10)) .ToList();
EF+ Query IncludeFilter
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.
Real Life Scenarios
Behind the code
Step 1 - Custom Queryable
The first time IncludeFilter is called, a custom Queryable and Provider are created for your query and will be used to append all queries.
Step 2 - Iterate or Execute
When an immediate method is invoked to resolve the query, the initial query is modified to create a new query which includes related entities.
- The iterate method is invoked when it is possible to return multiple results like ToList() and ToArray()
- The execute method is invoked when it is possible to return only one result like First() and Last()
Original Query:
var list = ctx.Orders.IncludeFilter(x => x.OrderItems.Where(y => !y.IsSoftDeleted)) .Take(10) .ToList();
Executed Query:
var q1 = ctx.Orders.Take(10) var p1 = q1.Select(x => x.OrderItems.Where(y => !y.IsSoftDeleted)); var list = q1.Select(x => new { x, p1 }).ToList().Select(x => x.X);
Entity Framework already does all the job by linking related entities to the parent. No future logic is required.
Limitations
- DO NOT work with AsNoTracking
- Cannot be mixed with projection
- Cannot be mixed with Include (Include doesn't support projection)
- Cannot be mixed with IncludeOptimized
- Many to Many relation:
- Not supported yet
- Relationship:
- Entities will contain all previously loaded related entities even if the Query does not return them. It's a limitation due to how Entity Framework relation work.
// using Z.EntityFramework.Plus; // Don't forget to include this. var ctx = new EntitiesContext(); ctx.Comments.ToList(); // The posts automatically contain all comments even without using the "Include" method. ctx.Posts.ToList(); // Trying to load only one comment will obviously not work either. ctx.Posts.IncludeFilter(q => q.Comments.Take(1)).ToList();
Conclusion
Filtering included related entities is one of the most frequently asked questions in forums. EF+ Query IncludeFilter now makes it possible without any learning curve required for a junior developer to understand what the method does.
Need help getting started? info@zzzprojects.com
We welcome all comments, ideas and suggestions to improve our library.