I have these (simplified) models:
public class Participant {
public Guid Id { get; set; }
public string Name { get; set; }
public Guid TeamId { get; set; }
public Team Team { get; set; }
}
public class Team {
...
}
public class Activity {
public Guid Id { get; set; }
public string Name { get; set; }
public int Status { get; set; }
public IList<ActivityAttendee> ActivityAttendees { get; set; }
}
public class ActivityAttendee {
public Guid Id { get; set; }
public Guid ActivityId { get; set; }
public Guid ParticipantId { get; set; }
public int Status { get; set; }
public Participant Participant { get; set; }
}
The participant is identified with this:
Guid? participantId = Guid.TryParse(HttpContext.User.Claims.FirstOrDefault(c => c.Type == "Id")?.Value, out Guid tmp) ? (Guid?)tmp : null;
I query the activities like this:
var activities = await _context.Activities.Include(x => x.ActivityAttendees).ThenInclude(x => x.Participant).Where(x => x.Status == 1).AsNoTracking().ToListAsync();
Now I need to filter Activities where the participant attends. I've tried:
var participantActivities = activities.Where(x => x.ActivityAttendees.ParticipantId == participantId);
var participantActivities = activities.Where(x => x.ActivityAttendees.Contains(y => x.ActivityAttendees.Where(z => z.ParticipantId == participantId)));
var participantActivities = activities.Where(x => x.ActivityAttendees.Any(y => y.ParticipantId == participantId))
How can I accomplish that?
In "normal" SQL I would do something like:
SELECT Id, Name FROM Activities WHERE Id IN (Select ActivityId FROM ActivityAttendees WHERE ParticipantId = participantId))
Next, I also need to filter the activities where the participant doesn't attend but where a team member does (grouped by ActivityAttendee.Participant.TeamId), but I think I can do that when the question above is answered.
The correct way was:
var participantActivities = activities.Where(x => x.ActivityAttendees.Any(y => y.ParticipantId == participantId));