C# - LINQ - IncludeFilter

LINQ –  IncludeFilter

EF, "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();

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

Query IncludeFilter - Load one level

// using Z.EntityFramework.Plus; // Don't forget to include this.

var ctx = new EntitiesContext();

// LOAD blogs and related active posts.

var blogs = ctx.Blogs.IncludeFilter(x => x.Posts.Where(y => !y.IsSoftDeleted)).ToList();

Query IncludeFilter - Load multiple levels

// using Z.EntityFramework.Plus; // Don't forget to include this.

var ctx = new EntitiesContext();

// LOAD blogs and related active posts and comments.

var blogs = ctx.Blogs.IncludeFilter(x => x.Posts.Where(y => !y.IsSoftDeleted))

                     .IncludeFilter(x => x.Posts.Where(y => !y.IsSoftDeleted)

                                                .Select(y => y.Comments

                                                              .Where(z => !z.IsSoftDeleted)))

                     .ToList();