How to filter rows in an Include and ThenInclude statements in LINQ and C#

1 day ago 3
ARTICLE AD BOX

I'm trying to populate an object with two collections of type ChatSession.
ChatSession contains two types of sessions, Open and Solved.

Edit: Query changed to fit solution from Charlieface.

Add<ISmartieHub, SmartieHub>(this.DbContext.GetDbSet<SmartieHub>() .Include(h => h.AllowedOrigins) .Include(h => h.OpenSessions.Where(s => !s.Feedbacks.Any(h => h.Solved))) .Include(h => h.SolvedSessions.Where(s => s.Feedbacks.Any(h => h.Solved))));

Edit: Will this work as expected (I can't test against the DB right now)?

Add<ISmartieHub, SmartieHub>(this.DbContext.GetDbSet<SmartieHub>() .Include(h => h.AllowedOrigins) .Include(h => h.OpenSessions) .ThenInclude(s => s.Feedbacks.Where(f => f.Solved == false)) .Include(h => h.SolvedSessions) .ThenInclude(s => s.Feedbacks.Where(f => f.Solved == true)));

Edit, possible solution 2:

Add<ISmartieHub, SmartieHub>(this.DbContext.GetDbSet<SmartieHub>() .Include(h => h.AllowedOrigins) .Include(h => h.OpenSessions) .ThenInclude(s => !s.Feedbacks.Any(f => f.Solved)) .Include(h => h.SolvedSessions) .ThenInclude(s => s.Feedbacks.Any(f => f.Solved)));

For Solved sessions, I want to select any ChatSessions that contain a ChatFeedback with Solved set to true.

Add<ISmartieHub, SmartieHub>(this.DbContext.GetDbSet<SmartieHub>() .Include(h => h.AllowedOrigins) .Include(h => h.OpenSessions) .ThenInclude(s => s.Feedbacks) .Include(h => h.SolvedSessions) .ThenInclude(s => s.Feedbacks));

SmartieHub.cs:

public record SmartieHub : AssetsDb, ISmartieHub { public required int AppId { get; set; } public DateTime DateLastUsed { get; set; } = DateTime.Now; public required int ApplicationAllowedOriginId { get; set; } public virtual required IEnumerable<ApplicationAllowedOrigin> AllowedOrigins { get; set; } = DbUtility.Hash<ApplicationAllowedOrigin>(); public virtual required IEnumerable<ChatSession> OpenSessions { get; set; } = DbUtility.Hash<ChatSession>(); public virtual required IEnumerable<ChatSession> SolvedSessions { get; set; } = DbUtility.Hash<ChatSession>(); }

ChatFeedback.cs:

public sealed record ChatFeedback : AssetsDb, IChatFeedback { public required int ChatSessionId { get; set; } public bool Solved { get; set; } public string? Comment { get; set; } }

ChatSession.cs:

public record ChatSession : AssetsDb, IChatSession { public required int SmartieAppId { get; set; } public virtual required IEnumerable<ChatFeedback> Feedbacks { get; set; } = DbUtility.Hash<ChatFeedback>(); public string? UserAgent { get; set; } public string? Origin { get; set; } }
Read Entire Article