This is an example query from one of my projects. It is written using EFCore and LINQ.

ComplexQueryUsingEFCore.cs

/// <summary>
/// Returns all topic information and returns it to the client.
/// If the user is not a paid member, the video urls and document urls are returned null.
/// </summary>
public async Task<TopicModel> GetTopicClassroomModelAsync(string topicId, string userId)
{
    // Get the topic entity
    var topic = await _db.Topics
        .Include(t => t.Lesson) // To get to the UserCourse

        .Include(t => t.Subtopics)
        .ThenInclude(subtopic => subtopic.LectureVideos)
        .ThenInclude(lectureVideo => lectureVideo.LectureVideoActivities)

        .Include(t => t.Subtopics)
        .ThenInclude(subtopic => subtopic.MiniTestSolutionVideos)
        .ThenInclude(miniTestSolutionVideo => miniTestSolutionVideo.MiniTestSolutionVideoActivities)

        // Add newly added content types here

        .FirstOrDefaultAsync(t => t.Id == topicId);

    if (topic == null)
        return null;

    // Find the full access status
    var userCourse = await _db.UserCourses
        .FirstOrDefaultAsync(uc => uc.UserId == userId && uc.CourseId == topic.Lesson.CourseId);

    // If the userCourse entity doesn't exist, status is unenrolled
    var userCourseStatus = userCourse?.Status ?? UserCourseEnrollmentStatus.Unenrolled;

    // Only subscribed users have full access
    var hasFullAccess = userCourseStatus == UserCourseEnrollmentStatus.Subscribed;

    // Construct the topic model
    var topicClassroomModel = new TopicModel
    {
        Id = topic.Id,
        Name = topic.Name,
        LessonId = topic.LessonId,
        OrderInLesson = topic.OrderInLesson,
        TopicResourcesUrl = topic.TopicResourcesUrl,
        ApproximateStudyTimeInSeconds = topic.ApproximateStudyTimeInSeconds,
        NumberOfVideos = topic.NumberOfVideos,
        SubtopicModels = topic.Subtopics
            .OrderBy(mvpSubtopic => mvpSubtopic.OrderInTopic)
            .Select(subtopic => new SubtopicModel
            {
                Id = subtopic.Id,
                Name = subtopic.Name,
                ApproximateStudyTimeInSeconds = subtopic.ApproximateStudyTimeInSeconds,
                IsFreeForAll = subtopic.IsFreeForAll,
                LectureVideoModels = subtopic.LectureVideos
                    .OrderBy(lectureVideo => lectureVideo.OrderInSubtopic)
                    .Select(lectureVideo =>
                    {
                        var lectureVideoActivity = lectureVideo.LectureVideoActivities
                            // TODO: Decide on how many video watch logs we want to store. We do not need to sort all of them
                            .OrderByDescending(lva => lva.InteractionTime)
                            .FirstOrDefault(lva => lva.UserId == userId);
                        return new LectureVideoModel
                        {
                            Id = lectureVideo.Id,
                            OrderInSubtopic = lectureVideo.OrderInSubtopic,
                            Name = lectureVideo.Name,
                            Description = lectureVideo.Description,
                            ContentLevel = lectureVideo.ContentLevel,
                            Url = hasFullAccess ? lectureVideo.Url : "",
                            DurationInSeconds = lectureVideo.DurationInSeconds,
                            ConsumedByUser = lectureVideoActivity != null,
                            VideoLeftAtMilliseconds = lectureVideoActivity?.LeftAtMilliseconds ?? 0,
                        };
                    }).ToList(),
                MiniTestSolutionVideoModels = subtopic.MiniTestSolutionVideos
                    .OrderBy(miniTestSolutionVideo => miniTestSolutionVideo.OrderInSubtopic)
                    .Select(miniTestSolutionVideo =>
                    {
                        var miniTestVideoActivity = miniTestSolutionVideo.MiniTestSolutionVideoActivities
                            // TODO: Decide on how many video watch logs we want to store.We do not need to sort all of them
                            .OrderByDescending(mtsva => mtsva.InteractionTime)
                            .FirstOrDefault(mtsva => mtsva.UserId == userId);
                        return new MiniTestSolutionVideoModel
                        {
                            Id = miniTestSolutionVideo.Id,
                            OrderInSubtopic = miniTestSolutionVideo.OrderInSubtopic,
                            Name = miniTestSolutionVideo.Name,
                            Description = miniTestSolutionVideo.Description,
                            ContentLevel = miniTestSolutionVideo.ContentLevel,
                            Url = hasFullAccess ? miniTestSolutionVideo.Url : "",
                            DurationInSeconds = miniTestSolutionVideo.DurationInSeconds,
                            ConsumedByUser = miniTestVideoActivity != null,
                            VideoLeftAtMilliseconds = miniTestVideoActivity?.LeftAtMilliseconds ?? 0,
                        };
                    }).ToList()

                // Add newly added content types here

            }).ToList(),
        HasFullAccess = hasFullAccess
    };

    return topicClassroomModel;
}