如何使用 Linq to Entity 重写 SQL 查询

Question

我需要使用 Linq to Entity 重写下面的查询。有人知道如何以最充分的方式做到这一点吗？

   SELECT DISTINCT
        C.ClientId,
        C.CompanyName
    FROM Application A WITH (NOLOCK)
    INNER JOIN
                (
                SELECT ApplicationId
                FROM CAContracts WITH (NOLOCK)
                WHERE ID = 1212 AND CAContractStatusId IN (2,3)
                UNION ALL
                SELECT  OBA.ApplicationId
                FROM OpportunityAssignment OA WITH (NOLOCK)
                INNER JOIN OpportunityByApp OBA WITH (NOLOCK) ON
                    OBA.OpportunityId = OA.OpportunityId
                WHERE OA.ID = 1212
                    AND OA.OpporStatusId IN (5,7)
                ) ACPA ON
        ACPA.ApplicationId = A.Applicationid
    INNER JOIN Client C WITH (NOLOCK) ON
        C.ClientId = A.ClientId
    ORDER BY C.CompanyName

Answer 1

假设 Context 已将所有相关表定义为实体并定义了正确的关系：

Context.Clients
       .Include("Applications")
       .Include("Applications.CAContracts")
       .Include("Applications.OpportunityAssignments")
       .Include("Applications.OpportunityAssignments.OpportunityByApps")
       .Where<Client>(c => (c.Applications
                             .Any<Application>(a => a.CAContracts
                                                     .Any<CAContract>(cac => cac.ID == 1212 && (cac.CAContractStatusId == 2 || cac.CAContractStatusId == 3)))
                        || (c.Applications
                             .Any<Application>(a => a.OpportunityAssignments
                                                     .Any<OpportunityAssignment>(oa => oa.ID == 1212 && (oa.OpporStatusId == 5 || oa.OpporStatusId == 7) && oa.OpportunityByApps.Any<OpportunityByApp>()))))
       .Select(c => new { ClientId = c.ClientId, CompanyName = c.CompanyName})
       .Distinct()
       .OrderBy(c => c.CompanyName);

如果我对架构了解更多，我可能会做得更好一些。您最终会得到具有 ClientId 和 CompanyName 属性的匿名类型的集合；我个人倾向于避免匿名类型，除非相应的对象太大。