How can I use a specific record's value that's the many in a 1:many in a query of the parent?

Question

Here's the classes:

public class Event 
{
    public int Id { get; private set; }

    public List<Occurrence>? Occurrences { get; set; }
}

public class Occurrence
{
    public int Id { get; private set; }

    public Event Parent { get; set; }
    public int ParentId { get; private set; }

    public int RecurrenceIndex { get; set; }
}

What I want to have in my Where() clause is something like:

dbContext.Events
    .Where(e => e.Occurrences[o => o.RecurrenceIndex == index])
    .FirstOrDefaultAsync();

where I pass in index to this query. Obviously [o => o.RecurrenceIndex == index] isn't valid. But is there a way to do this?

I can get the Occurrence by doing (writing now so syntax may be wrong):

dbContext.Occurrences
    .Where(o => o.RecurrenceIndex == index)
    .Where(o => o.ParentId == parentId)
    .FirstOrDefaultAsync();

But I'm curious if I can get the parent Event object in a single query.

Answer 1

var event = dbContext.Events
    .Where(e => e.Occurrences.Any(o => o.RecurrenceIndex == index))
    .FirstOrDefaultAsync();

... sounds like it is what you are looking for to get any event that contains a reference to a matching occurrence. If you want the Occurrences then eager load those to ensure they are available:

var event = dbContext.Events
    .Include(e => e.Occurrences)
    .Where(e => e.Occurrences.Any(o => o.RecurrenceIndex == index))
    .FirstOrDefaultAsync();