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?

Answer 1

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