CODEWITHSUNDEEP
c#entity-frameworklinqeager-loading
urig
urig

Reputation: 16831

Entity Framework - Eager loading with filter?

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?

Upvotes: 3

Views: 3126

Answers (3)

Jonathan Magnan
Jonathan Magnan

Reputation: 11327

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

Upvotes: 3

urig
urig

Reputation: 16831

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.

Upvotes: 2

urig
urig

Reputation: 16831

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.

Upvotes: 0

Related Questions