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