52

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; }
}
Improve this question
3
  • Can you show us the Posts schema please? Commented Sep 24, 2015 at 0:26
  • @DarkKnight - see edit Commented Sep 24, 2015 at 0:32
  • @grayson What you're asking to do is not possible. Linq2Sql will convert your code into raw SQL, and it'll return child rows via a join. You can't do this kind of conditional join in SQL. Your only option is to remove .Include(aa => aa.Attachments), and have a second query which returns attachments depending on whether or not the owner is an author/contributor. Commented Sep 24, 2015 at 1:57

7 Answers 7

Reset to default
69

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

22

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();
Improve this answer

6 Comments

do I need to add "_context.Configuration.LazyLoadingEnabled = true;" to renable lazy loading after this call?
@grayson yes, if you want to re-enable that.
@Hopeless know this question is old, but my recent questions was marked as duplicate to this one, my requirement is when Attachments has additional child properties they are not being available in result. Link to my question stackoverflow.com/questions/58347487/…
@Krtti I'm not so sure if your issue can be solved with what answered here, actually I don't understand your issue well (from what you described in the linked question, there are at least 2 ways of understanding). Also someone added an answer there and looks like it's correct for one way of understanding the issue. The idea in my answer is just simple enough to apply, when it's not working maybe it's not applicable. Finally it's a long time I've not been working with EF and Linq-to-entity, so I may not help you much.
@Hopeless no worries, my question was answered and thanks for taking time to inform
|
4

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()
Improve this answer

Comments

3

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.

Improve this answer

1 Comment

Hi, this doesn't appear to work when there are no Attachments for a post. A, I missing something
2

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.

Improve this answer

1 Comment

This i not working cause you try to use explicit loading on a list of posts. But the .Entry() can only be used on a single entity.
1

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();
Improve this answer

Comments

-5

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)
Improve this answer

3 Comments

is this a joke or something?
Thats genius..!
This helped me!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.