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; } }