How to get objects stored inside collection that is stored inside object?

Question

I am trying to retrieve posts from database. Post has collection of Connections and Connection has Peer object. I want to include Peer objects where PeerId is equal to passed Id but instead it returns all the peers inside Connection collection. How should query look like?

        var posts = await context.Post.AsQueryable()
            .Include(u => u.Connections).ThenInclude(u => u.Peer)
            .Include(u => u.Connections).ThenInclude(u => u.Issuer).Include(u => u.Location)
            .Include(u => u.Images)
            .Where(c=>c.Creator.UserSettings.ShowMe == true
            &&
            c.Connections.Any(connection => connection.PeerId==id)) //this line
            .OrderBy(item => item.Status)
            .ThenByDescending(d => d.CreatedDate)
            .ToListAsync();

    public class Post
{
    public long Id { get; set; }
    public long CreatorId { get; set; }
    public User Creator { get; set; }
    public long LocationId { get; set; }
    public virtual Location Location { get; set; }
    public ICollection<Connection> Connections { get; set; }
    public ICollection<Image> Images { get; set; }
    public string Status { get; set; }
}
public class Connection
{
    public long Id { get; set; }
    public long IssuerId { get; set; }
    public virtual User Issuer { get; set; }
    public long PeerId { get; set; }
    public virtual User Peer { get; set; }
    public long PostId { get; set; }
    public virtual Post Post { get; set; }
}
public class User
{
    public long Id { get; set; }

}

To get an answer you have to show all classes that you are going to include in the query. At least primary and foreign keys. — Serge, Jan 15 '21 at 14:04
Does this answer your question? [Filtering on Include in EF Core](https://stackoverflow.com/questions/43618096/filtering-on-include-in-ef-core) — Christoph Lütjen, Jan 15 '21 at 14:15

score 0 · Answer 1 · answered Jan 15 '21 at 14:24

You don't need AsQuerable if you get result as ListAsync. This query will be working with ef core 5:

var posts = await context.Post
            .Include(u => u.Location)
            .Include(u => u.Images)
            .Include(u => u.Connections.Where(c=> c.PeerId==id))
            .ThenInclude(u => u.Peer)
            .ThenInclude(u => u.Issuer)
             .Where(c=>c.Creator.UserSettings.ShowMe == true)
             .OrderBy(item => item.Status)
            .ThenByDescending(d => d.CreatedDate)
            .ToListAsync();

How to get objects stored inside collection that is stored inside object?

1 Answers1