Entity Framework - Eager loading with filter?

Question

I'm working with a DB schema where records are not overwritten when updated. Rather a new copy of the record is added and marked as "current".

For example:

Id | Current | Name | Owner
1  | false   | Foo  | Bar
1  | false   | Foo  | Bazz
1  | true    | Foo  | Buzz

In my model I have a Blog entity that has many Posts related to it. Each Post has many Comments related to it:

public class Blog
{
    public int Id {get; set};
    public bool Current {get; set};
    public ICollection<Post> Posts {get; set;}
}

public class Post
{
    public int Id {get; set};
    public bool Current {get; set};
    public ICollection<Comment> Comments {get; set;}
}

public class Comment
{
    public int Id {get; set};
    public bool Current {get; set};
}

I would like to eagerly load a Blog with all its Posts and all their Comments much like in this example from MSDN:

using (var context = new BloggingContext()) { // Load all blogs, all related posts, and all related comments var blogs1 = context.Blogs .Include(b => b.Posts.Select(p => p.Comments)) .ToList(); }

However, I would like to only include DB records where Current == true. How can I do this with LINQ-to-EF? Ideally the condition would go into the JOIN's ON clause - is this possible?

Question is not very clear, what about .Include(b => b.Posts.Select(p => p.Comments.Where(comment=> comment.Current))) ?? — A.T., Feb 20 '17 at 06:57
@a-t Thanks. What is not clear? Your suggestion does not extend to Blogs and Posts and gives me: "The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties." — urig, Feb 20 '17 at 09:13

Jonathan Magnan · Accepted Answer · 2018-03-04T14:50:40.690

Disclaimer: I'm the owner of the project Entity Framework Plus

The EF+ Query IncludeFilter allow easily filter included entities.

using (var context = new BloggingContext()) 
{ 
  // Load all blogs, all related posts, and all related comments 
  var blogs1 = context.Blogs 
                     .IncludeFilter(b => b.Posts.Where(x => x.Current))
                     .IncludeFilter(b => b.Posts.Where(x => x.Current).Select(p => p.Comments.Where(x => x.Current)) 
                     .ToList(); 
}

Note: Every path must be included due to some limitations of the library with navigation properties.

Wiki: EF+ Query Include Filter

Answer sub-question

One concern: The SQL emitted is very large.

The SQL is generated by Entity Framework. The SQL is very large due to how they handle relation in projection and include method. Our library doesn't generate this SQL.

You can change the big SQL Generated by using EF+ Query IncludeOptimized to execute multiple statement instead. Using multiple statements often improve the performance.

Example:

using (var context = new BloggingContext()) 
{ 
  // Load all blogs, all related posts, and all related comments 
  var blogs1 = context.Blogs 
                     .IncludeOptimized(b => b.Posts.Where(x => x.Current))
                     .IncludeOptimized(b => b.Posts.Where(x => x.Current).Select(p => p.Comments.Where(x => x.Current)) 
                     .ToList(); 
}

Note: Every path must be included due to some limitations of the library with navigation properties.

Wiki: EF+ Query IncludeOptimized

Thank you for this answer. Due to the caveat in my own answer below, I've opted to use EF+ Include Filter. The results returned are exactly as expected. One concern: The SQL emitted is very large. Contains multiple UNION clauses when it looks like only the last one is actually necessary...? (That's the one where all tables are joined together.) — urig, Feb 26 '17 at 12:39

score 2 · Answer 2 · edited May 23 '17 at 12:25

Found a solution using "out of the box" Entity Framework based on this StackOverflow answer.

The key concept is to add a parent property to each entity and then go "backwards" from the lowest level of the hierarchy up to the top:

var query = context.Comments
    .Include("Post.Blog")
    .Where(comment =>
        comment.Current &&
        comment.Post.Current &&
        comment.Post.Blog.Current )
    .Select(comment => comment.Post.Blog)
    .ToList();

One important caveat that's mentioned in a comment to that SO answer:

... if parents exist that don't have any children that match the filters, those parents won't be in the result set.

urig · Answer 3 · 2017-02-20T13:42:24.577

Filtering when eager loading with .Include() is not currently supported "out of the box" with Entity Framework. You can vote in favor of this feature here and hopefully it makes the cut into EF7.

I've found a partial answer to my question in an open source library called EntityFramework.Include that provides some filtering capabilities at the time of eager loading.

Unfortunately I've only been able to use it for two (out of three) levels of my hierarchy like so:

using (var context = new BloggingContext()) 
{ 
    // Load all blogs and all related posts that are "Current"
    var query = context.Blogs
        .Where(b => b.Current)
        .Include(b => b.Posts, b => b.Posts.Where(p => p.Current).ToList());
    var list = query.ToListWithInclude();
}

Still trying to figure out how to go one level deeper into Comments.

There's also [EntityFramework.Filters](https://github.com/jbogard/EntityFramework.Filters/) but unfortunately its filters are not applied to `.Include()`. — urig, Feb 20 '17 at 13:36

Entity Framework - Eager loading with filter?

3 Answers3