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; } }
Duplicate rows in Entity Framework SQL View Entity Framework auto set fields as entity key for those not null column and return the row that match those entity key that causes the problem. You can set AsNoTracking option directly on your view to resolve this issue.
LINQ Include allows retrieving the related entities to be read from database in same query. By using the Include method we can easily read all related entities from the database in a single query.
Lazy loading means that the related data is transparently loaded from the database when the navigation property is accessed.
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://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-5.0/whatsnew#filtered-include
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();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With