Conditional Include() in Entity Framework

Question

I have seen a few answers to similar questions, however I cannot seem to work out how to apply the answer to my issue.

var allposts = _context.Posts
            .Include(p => p.Comments)
            .Include(aa => aa.Attachments)
            .Include(a => a.PostAuthor)
            .Where(t => t.PostAuthor.Id == postAuthorId).ToList();

Attachments can be uploaded by the Author (type Author) or Contributor (type Contributor). What I want to do, is only get the Attachments where the owner of the attachment is of type Author.

I know this doesn't work and gives an error:

.Include(s=>aa.Attachments.Where(o=>o.Owner is Author))

I've read about Filtered Projection here

EDIT - link to article: : http://blogs.msdn.com/b/alexj/archive/2009/10/13/tip-37-how-to-do-a-conditional-include.aspx,

but I just can't get my head around it.

I don't want to include the filter in the final where clause as I want ALL posts, but I only want to retrieve the attachments for those posts that belong to the Author.

EDIT 2: - Post schema requested

public abstract class Post : IPostable
{

    [Key]
    public int Id { get; set; }

    [Required]
    public DateTime PublishDate { get; set; }

    [Required]
    public String Title { get; set; }

    [Required]
    public String Description { get; set; }

    public Person PostAuthor { get; set; }
    public virtual ICollection<Attachment> Attachments { get; set; }
    public List<Comment> Comments { get; set; }
}

Answer 1

EF Core 5.0 is introducing Filtered Include soon.

var blogs = context.Blogs
    .Include(e => e.Posts.Where(p => p.Title.Contains("Cheese")))
    .ToList();

Reference: https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-5.0/whatsnew#filtered-include

Answer 2

From the link you posted I can confirm that trick works but for one-many (or many-one) relationship only. In this case your Post-Attachment should be one-many relationship, so it's totally applicable. Here is the query you should have:

//this should be disabled temporarily
_context.Configuration.LazyLoadingEnabled = false;
var allposts = _context.Posts.Where(t => t.PostAuthor.Id == postAuthorId)
                       .Select(e => new {
                           e,//for later projection
                           e.Comments,//cache Comments
                           //cache filtered Attachments
                           Attachments = e.Attachments.Where(a => a.Owner is Author),
                           e.PostAuthor//cache PostAuthor
                        })
                       .AsEnumerable()
                       .Select(e => e.e).ToList();

Answer 3

Remove the virtual keyword from your Attachments navigation property to prevent lazy loading:

public ICollection<Attachment> Attachments { get; set; }

First method: Issue two separate queries: one for the Posts, one for the Attachments, and let relationship fix-up do the rest:

List<Post> postsWithAuthoredAttachments = _context.Posts
    .Include(p => p.Comments) 
    .Include(p => p.PostAuthor)
    .Where(p => p.PostAuthor.Id == postAuthorId)
    .ToList();

List<Attachment> filteredAttachments = _context.Attachments
    .Where(a => a.Post.PostAuthor.Id == postAuthorId)
    .Where(a => a.Owner is Author)
    .ToList()

Relationship fixup means that you can access these filtered Attachments via a Post's navigation property

Second method: one query to the database followed by an in-memory query:

var query = _context.Posts
    .Include(p => p.Comments) 
    .Include(p => p.PostAuthor)
    .Where(p => p.PostAuthor.Id == postAuthorId)
    .Select(p => new 
        {
            Post = p,
            AuthoredAttachments = p.Attachments
                Where(a => a.Owner is Author)
        }
    );

I would just use the anonymous type here

var postsWithAuthoredAttachments = query.ToList()

or I would create a ViewModel class to avoid the anonymous type:

List<MyDisplayTemplate> postsWithAuthoredAttachments = 
     //query as above but use new PostWithAuthoredAttachments in the Select

Or, if you really want to unwrap the Posts:

List<Post> postsWithAuthoredAttachments = query.//you could "inline" this variable
    .AsEnumerable() //force the database query to run as is - pulling data into memory
    .Select(p => p) //unwrap the Posts from the in-memory results
    .ToList()

Answer 4

You can use this implementation of an extension method (eg.) Include2(). After that, you can call:

_context.Posts.Include2(post => post.Attachments.Where(a => a.OwnerId == 1))

The code above includes only attachments where Attachment.OwnerId == 1.

Answer 5

try this

var allposts = _context.Posts
        .Include(p => p.Comments)
        .Include(a => a.PostAuthor)
        .Where(t => t.PostAuthor.Id == postAuthorId).ToList();

_context.Attachments.Where(o=>o.Owner is Author).ToList();

Answer 6

For net core

https://learn.microsoft.com/ru-ru/ef/core/querying/related-data/explicit

var allposts = _context.Posts
        .Include(p => p.Comments)
        .Include(a => a.PostAuthor)
        .Where(t => t.PostAuthor.Id == postAuthorId).ToList();

_context.Entry(allposts)
        .Collection(e => e.Attachments)
        .Query()
        .Where(e=> e.Owner is Author)
        .Load();

it makes 2 query to sql.

Answer 7

Lambda in Include() may only point to a property:

.Include(a => a.Attachments)
.Include(a => a.Attachments.Owner);

Your condition doesn't makes sense for me because Include() means join and you either do it or not. And not conditionally.

How would you write this in raw SQL?

---

Why not just this:

context.Attachments
       .Where(a => a.Owner.Id == postAuthorId &&
                   a.Owner.Type == authorType);

?

Answer 8

Assuming "a" being of type "YourType", a conditonal include could be solved by using a method extension, e.g.

public static class QueryableExtensions
{
    public static IQueryable<T> ConditionalInclude<T>(this IQueryable<T> source, bool include) where T : YourType
    {
        if (include)
        {
            return source
                .Include(a => a.Attachments)
                .Include(a => a.Attachments.Owner));
        }

        return source;
    }
}

... then just use this like you are using .Include, e.g.

bool yourCondition;

.ConditionalInclude(yourCondition)