Conditional inclusion () in Entity Framework

Question

I saw several answers to similar questions, but I can’t figure out how to apply the answer to my problem.

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 downloaded by the Author (type Author) or Contributor (type Contributor). I want to make only attachments in which the application owner is of type Author.

I know that this does not work and gives an error:

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

I read about the Filtered Projection here

EDIT - link to the 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 around it.

I do not want to include a filter in final where, because I want ALL messages, but I only want to receive attachments for those messages that belong to the author.

EDIT 2: - requested requested 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; } } 

Answer 1

From the link you posted, I can confirm that the trick works, but only for one-to-many (or many-to-one) relationships. In this case, your Post-Attachment should be a one-to-many relationship, so it is fully 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 => ee).ToList(); 

Answer 2

You can use this implementation of the extension method (for example.) Include2() . After that you can call:

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

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

Answer 3

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

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

First method: issue two separate queries: one for messages, one for attachments and let the interdependence 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() 

Locking links means you can access these filtered attachments through the Post navigation property

The second method: one query to the database, followed by a query in memory:

 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 an anonymous type here

 var postsWithAuthoredAttachments = query.ToList() 

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

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

Or, if you really want to expand the messages:

 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

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

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

Your condition does not make sense to me, because Include() means join , and you will do it or not. And not conditionally.

How do you write this in raw SQL?

---

Why not just:

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

?

Answer 5

Assuming that the "type" is of type "YourType", conditonal include can be resolved using a method extension, for example.

 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 as you use .Include for example

 bool yourCondition; .ConditionalInclude(yourCondition)