c# - 使用 Entity Framework Core 的 LEFT JOIN 查询

Question

我做了很多研究并阅读了一些类似的问题和答案，但仍然不知道如何编写 EF Core 代码来创建以下 SQL 服务器查询。有人可以帮忙吗。我看过教程解释如何使用 LEFT JOIN 连接 2 个表，但找不到包含 3 个或更多表的任何内容。

SELECT std.DocID, std.ServiceCode, 
    CASE std.Description
        WHEN 'Car Wash' 
        THEN 'Misc' 
        ELSE std.Description 
    END AS 'Description', 
    std.StandardRate, std.StandardHours,
    det.ParticipantCount, det.TotalHours, det.TotalFees
FROM StandardServices std 
INNER JOIN Sorted srt 
    ON std.ServiceCode = srt.ServiceCode 
LEFT OUTER JOIN ServiceDetails det
    ON det.ParentRecordUNID='00000000-0000-0000-0000-000000000000' AND det.ServiceTypeUNID=std.DocID
WHERE std.IsDeleted='N' AND std.TopService = 'Y' 
ORDER BY srt.SortOrder

以下是接近上述内容的内容（出于隐私考虑，我已经替换了一些文本，因此可能不完全匹配）。但由于某些原因，它选择了所有列而不是提到的列。此外，它在末尾添加了一个 (SELECT 1)。

    var results=await (from sso in context.StandardServices
                       join mos in context.Sorted on sso.ServiceCode equals mos.ServiceCode
                       join mad in context.ServiceDetails
                       on new { key1 = sso.DocID, key2 = parentId } equals new { key1 = (Guid)mad.ServiceTypeUnid, key2 = mad.ParentRecordUnid }
                                                      into jointable
                         where sso.IsDeleted == "N" && sso.TopService =="Y" 
                         orderby mos.SortOrder
                         from mad1 in jointable.DefaultIfEmpty()
                         select new ServiceRowDetails()
                         {
                             DocID = mad1.DocID,
                             ParentRecordUnid = mad1.ParentRecordUnid,
                             ServiceTypeUnid = sso.DocID,
                             ServiceType = sso.Description,
                             ParticipantCount = mad1.ParticipantCount ?? 0,
                             StandardFees = sso.StandardRate ?? 0,
                             StandardHours = sso.StandardHours ?? 0,
                             TotalFees = mad1.TotalFees ?? 0,
                             TotalHours = mad1.TotalHours ?? 0
                         }).ToListAsync();

更新：正如@IvanStoev 在下面评论的那样进行了更改，效果很好。

    var results=await (from sso in context.StandardServices
                           join mos in context.Sorted on sso.ServiceCode equals mos.ServiceCode
                           join mad in context.ServiceDetails
                           on new { key1 = sso.DocID, key2 = parentId } equals new { key1 = (Guid)mad.ServiceTypeUnid, key2 = mad.ParentRecordUnid }
                                                          into jointable
 from mad1 in jointable.DefaultIfEmpty()
                             where sso.IsDeleted == "N" && sso.TopService =="Y" 
                             orderby mos.SortOrder

                             select new ServiceRowDetails()
                             {
                                 DocID = mad1.DocID,
                                 ParentRecordUnid = mad1.ParentRecordUnid,
                                 ServiceTypeUnid = sso.DocID,
                                 ServiceType = sso.Description,
                                 ParticipantCount = mad1.ParticipantCount ?? 0,
                                 StandardFees = sso.StandardRate ?? 0,
                                 StandardHours = sso.StandardHours ?? 0,
                                 TotalFees = mad1.TotalFees ?? 0,
                                 TotalHours = mad1.TotalHours ?? 0
                             }).ToListAsync();

标签： c#entity-framework-core