Need help in understanding Select Within Select with Where

Question

I have the following code in C# LINQ and I am working on migrating to SQL. I don't have any experience with C# LINQ but for past few weeks I had been able to convert some of the functions.

But I hit upon the following code which I am trying to decipher the way I would implement it in SQL.

1. In the line 'qu.Category == Categories.History' - Is this filter applied before the join with Quiz or after? Join in terms of SQL.

2. How would I implement firstOrDefault in SQL - Just select Top 1 based on an order by column?

   var workflows = this.context.Quiz
        .AsSingleQuery()
        .Where(q => !q.Deleted)
        .Select(q => new
        {
            q,
            ValidResponse = q.Questions
                .Where(qu => qu.Category == Categories.History)
                .SelectMany(qu => qu.Responses)
                .Where(r => !r.Deleted)
                .Select(r => r.QuestionResponse).FirstOrDefault()
        })

If you could give me a converted SQL query for this, it would help my understanding!

Answer 1

This should be the sql used:

SELECT [q].*, 
       (SELECT TOP 1 [r].[QuestionResponse]
        FROM [Questions] AS [qu]
        INNER JOIN [Responses] AS [r] ON [qu].[Id] = [r].[QuestionId]
        WHERE [qu].[Category] = 'History' AND [r].[Deleted] = 0 AND [qu].[QuizId] = [q].[Id]
        ORDER BY [r].[Id]) AS [ValidResponse]
FROM [Quiz] AS [q]
WHERE [q].[Deleted] = 0

Guessing the FK columns and the Categories.History value