Hey there! I see you've got a solid SQL query that does exactly what you need, and you're looking to convert it to LINQ. Let's break this down and get you sorted.

First, let's recap your requirements to make sure we're on the same page:

• Fetch all active customers (where IsActive = 1 and Status = 1)
• For each customer, if they have any matching SurveyCustomer records (for SurveyId 1207, active and enabled), only join the most recent one (sorted by CreatedDate DESC)
• Ensure the total number of results matches the number of active customers (so left join behavior is preserved)

Here's your original SQL for reference:

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 
            ORDER BY sc1.CreatedDate DESC 
        ) A 
    ) 
WHERE c.IsActive = 1 AND c.STATUS = 1;

And your table structures:

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 Table #

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
)

Now, let's convert this to LINQ. There are a couple of clean ways to do this, depending on whether you prefer method syntax or query syntax. Both will produce the same underlying logic as your SQL.

Method Syntax (EF Core)

var targetSurveyId = 1207;

var query = dbContext.Customers
    // Filter to only active customers
    .Where(c => c.IsActive && c.Status)
    // Left join with the latest matching SurveyCustomer per customer
    .GroupJoin(
        // First get the latest valid SurveyCustomer for each customer
        dbContext.SurveyCustomers
            .Where(sc => sc.Status && sc.IsActive && sc.SurveyId == targetSurveyId)
            .GroupBy(sc => sc.CustomerId)
            .Select(g => g.OrderByDescending(sc => sc.CreatedDate).FirstOrDefault()),
        // Join keys
        customer => customer.CustomerId,
        latestSurvey => latestSurvey?.CustomerId,
        // Project the result
        (customer, latestSurveys) => new 
        {
            // Include all customer fields (or map to a DTO if needed)
            Customer = customer,
            // Get the single latest survey (or null if none exists)
            LatestSurveyCustomer = latestSurveys.FirstOrDefault()
        }
    );

// Execute the query
var result = query.ToList();

Query Syntax (EF Core)

If you prefer query syntax (which reads more like SQL for some folks), here's an equivalent version:

var targetSurveyId = 1207;

// First, get the latest valid SurveyCustomer for each customer
var latestSurveyCustomers = from sc in dbContext.SurveyCustomers
                           where sc.Status && sc.IsActive && sc.SurveyId == targetSurveyId
                           group sc by sc.CustomerId into customerSurveyGroup
                           select customerSurveyGroup.OrderByDescending(sc => sc.CreatedDate).FirstOrDefault();

// Now left join this with active customers
var query = from customer in dbContext.Customers
            where customer.IsActive && customer.Status
            join latestSc in latestSurveyCustomers 
                on customer.CustomerId equals latestSc.CustomerId into surveyGroup
            from latestSurvey in surveyGroup.DefaultIfEmpty()
            select new 
            {
                Customer = customer,
                LatestSurveyCustomer = latestSurvey
            };

var result = query.ToList();

Key Notes: #

• Both approaches first filter and group the SurveyCustomers to get only the latest valid entry per customer, then perform a left join with active customers. This ensures we don't duplicate customer records even if they have multiple survey entries.
• If you're using a DTO (Data Transfer Object) instead of anonymous types, just replace the anonymous object with your DTO class and map the fields accordingly.
• The targetSurveyId is extracted as a variable for reusability and readability, which matches the 1207 hardcoded in your SQL.

内容的提问来源于stack exchange，提问作者user6172721