Conditional Include() in Entity Framework

c# entity-framework linq linq-to-entities

Question

I've seen a few responses to problems like mine, but I'm having trouble understanding how to apply the solutions to my situation.

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

Neither the Author (type Author) nor the Contributor cannot upload attachments (type Contributor). I only want to retrieve attachments whose owners are of the type Author, for example.

I am aware that this is flawed and produces an error:

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

I read this article regarding filtered projection.

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

yet I'm having trouble comprehending it.

I only want to retrieve the attachments for the posts that belong to the Author, hence I don't want to add the filter in the final where clause because I want ALL posts.

EDIT 2: Request for post-schema

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; }
}
1
20
9/24/2015 12:32:32 AM

Accepted Answer

I can confirm that method works, but only for one-many (or many-one) relationships, according to the source you provided. If applicable, yourPost-Attachment It should be a one-to-many relationship, so it applies perfectly. The question you should ask is this:

//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();
14
9/24/2015 3:54:48 AM

Popular Answer

Delete thevirtual phrase from yourAttachments a navigational setting to stop lazy loading:

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

First method: Send out two distinct queries, one for the posts and one for the attachments, and leave the rest to relationship repair:

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

The navigation attribute of a Post allows you to view these filtered Attachments because of relationship fixup.

A second approach involves doing a database query first, then making 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'll just stick with the anonymous type here.

var postsWithAuthoredAttachments = query.ToList()

Instead, to avoid the anonymous type, I would build a ViewModel class:

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

If you truly want to explore the Posts, you can:

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow