left join with up to one row in the right table in LINQ

Question

Hifriends, I have one customer table and one relationship table where I keep the customers I send survey link to. The same survey may have been sent to the same customer more than once. Or, some customers may not have sent any survey links. my goal is to bring all the customers once, and if some of these customers have been sent a questionnaire, I want to bring only the one which created date is biggest. The number of records must be equal to the number of customers. I wrote the query, but it is very difficult to translate linq or. Can you help.

this is query what I wrote

SELECT *
FROM dbo.Customer c
 LEFT JOIN dbo.SurveyCustomers sc ON sc.SurveyCustomerId =
(
SELECT A.SurveyCustomerId
FROM
(
    SELECT TOP 1 *
    FROM dbo.SurveyCustomers sc1
    WHERE sc1.STATUS = 1
          AND sc1.IsActive = 1
          AND sc1.CustomerId = c.CustomerId
          AND sc1.SurveyId = 1207
          AND sc1.STATUS = 1
          AND sc1.IsActive = 1
    ORDER BY sc1.CreatedDate DESC
) A
)
WHERE c.IsActive = 1
  AND c.STATUS = 1;

Customer table

CREATE TABLE [dbo].[Customer](
[CustomerId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[CustomerTitle] [varchar](500) NOT NULL,
[CustomerEmail] [varchar](500) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[UpdatedDate] [datetime] NOT NULL,
[IsActive] [bit] NOT NULL,
[Status] [bit] NOT NULL)

SurveyCustomer

CREATE TABLE [dbo].[SurveyCustomers](
[SurveyCustomerId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[SurveyId] [int] NOT NULL FOREIGN KEY,
[CustomerId] [int] NOT NULL FOREIGN KEY,
[GuidId] [varchar](500) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[UpdatedDate] [datetime] NOT NULL,
[Status] [bit] NOT NULL,
[IsActive] [bit] NOT NULL)

Answer 1

You can use OUTER APPLY to getting the same result.

SELECT *
FROM dbo.Customer c
 OUTER APPLY 
(
    SELECT TOP 1 *
    FROM dbo.SurveyCustomers sc1
    WHERE sc1.STATUS = 1
          AND sc1.IsActive = 1
          AND sc1.CustomerId = c.CustomerId
          AND sc1.SurveyId = 1207
    ORDER BY sc1.CreatedDate DESC
) A
WHERE c.IsActive = 1
  AND c.STATUS = 1;

and linq equivalent:

Customers.SelectMany(c => SurveyCustomers
                        .Where(sc1 => 
                            sc1.Status == true 
                            && sc1.IsActive == true
                            && sc1.CustomerId == c.CustomerId
                            && sc1.SurveyId == 1207 )
                        .OrderByDescending(sc1 => sc1.CreatedDate)
                        .Take(1).DefaultIfEmpty(), 
                    (c, sc) => new { c, sc })
.ToList()