Get all Parents, but only one son and one grandson for a condition

Question

I have this structure:

Topics.SubTopics.Events
Topics.Users
Users.Topics

Topics and Users is a many-to-many relationship.

I want to retrieve all topics, subtopics and events where a PremiereDateTimeInUtc (property of an event) is equal or greater then yesterday and where the Topics.Users.Id == userId. But only 1 event should be retrieved (the first one from yesterday) for each topic. And only the subtopic which is the parent of the event. But Topic must all be returned even the ones which don't have and subtopic and event.

I tried this so far:

return context.Topics
.Include(
    t => t.SubTopics.Select(
        s => s.Events
        .Any(e => e.PremiereDateTimeInUtc >= DateTime.Today.AddDays(-1))
    )
)
.Where(t => t.Users.Any(u => u.Id == userId)).ToList();

But this doesn't work. I get this exception: "The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties. Parameter name: path".

Answer 1

var yesterday =DateTime.Today.AddDays(-1);

var events = context.Events.Where(x=>x.PremiereDateTimeInUtc>=yesterday)
    .GroupBy(x=>x.Subtopic.TopicId, (k,g)=>new{
        TopicId=k,
        Event=g.OrderBy(e=>e.PremiereDateTimeInUtc).FirstOrDefault()
    });

var topics = context.Topics
    .Where(t=>t.Users.Any(u=>u.Id==userId))
    .GroupJoin(events, t=>t.Id, e=>e.TopicId,(t,g)=>new{
        Topic=t,
        FirstEvent=g.FirstOrDefault(),
        Subtopic=g.FirstOrDefault().Subtopic
    });