Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Conditional Include() in Entity Framework [duplicate]

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; } } 
like image 686
grayson Avatar asked Sep 24 '15 00:09

grayson


People also ask

How do you avoid duplicates in Entity Framework?

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.

What is include in LINQ query C#?

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.

What is lazy loading EF core?

Lazy loading means that the related data is transparently loaded from the database when the navigation property is accessed.


2 Answers

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

like image 150
vivek nuna Avatar answered Oct 13 '22 12:10

vivek nuna


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(); 
like image 36
Hopeless Avatar answered Oct 13 '22 12:10

Hopeless