如何在Entity Framework Core中实现T-SQL CROSS APPLY查询

看起来你已经搞定了大部分的INNER JOIN逻辑，就差把最后那个关联换成CROSS APPLY来取最新的状态记录了——我来帮你搞定这个问题！

核心思路是：EF Core可以通过**导航属性结合SelectMany+Take(1)**的方式生成CROSS APPLY语句，刚好匹配你要为每个CommunicationOutputChannel获取最新（按createdOn降序）且状态为to process的CommunicationStatusOutputChannel的需求。

修正后的LINQ查询 #

comCommunicationContext 
    .Communication 
    .AsNoTracking() 
    .Where(c => c.Label.InternalName == labelName 
             && c.Businesstransaction.DocumentType.DocumentTypeName == documentType 
             && c.Businesstransaction.SourceSystem.SourceSystemName == sourceSystem) 
    .Join(comCommunicationContext.CommunicationOutputChannel, 
          communicationEntity => communicationEntity.CommunicationId, 
          communicationOutputChannelEntity => communicationOutputChannelEntity.CommunicationId, 
          (communicationEntity, communicationOutputChannelEntity) => new 
          { 
              Communication = communicationEntity, 
              CommunicationOutputChannel = communicationOutputChannelEntity 
          }) 
    .Where(y => y.CommunicationOutputChannel.OutputChannel == outputChannel)
    // 替换原INNER JOIN为SelectMany+Take(1)，自动生成CROSS APPLY
    .SelectMany(outer => outer.CommunicationOutputChannel.CommunicationStatusOutputChannels
                                .Where(x => x.Status == "to process")
                                .OrderByDescending(x => x.createdOn)
                                .Take(1),
                (outer, statusRecord) => new Communication() 
                { 
                    CommunicationDataEnriched = outer.Communication.CommunicationDataEnriched 
                })

为什么这个写法能生成CROSS APPLY？ #

EF Core会把这种“对每个外层实体执行子查询并取第一条”的逻辑翻译为CROSS APPLY：

• 对于每一条外层的CommunicationOutputChannel记录，EF会执行子查询筛选出状态为to process的CommunicationStatusOutputChannel，按createdOn倒序排序后取第一条
• 这完全贴合你期望的SQL逻辑，只会保留那些存在符合条件的最新状态记录的Communication

额外优化：用导航属性简化查询 #

如果你的实体模型已经正确配置了导航关系（比如Communication有ICollection<CommunicationOutputChannel>类型的导航属性），可以去掉显式的Join，让EF自动处理关联，代码更简洁易读：

comCommunicationContext 
    .Communication 
    .AsNoTracking() 
    .Where(c => c.Label.InternalName == labelName 
             && c.Businesstransaction.DocumentType.DocumentTypeName == documentType 
             && c.Businesstransaction.SourceSystem.SourceSystemName == sourceSystem)
    .SelectMany(communication => communication.CommunicationOutputChannels
                        .Where(outputChannel => outputChannel.OutputChannel == outputChannel)
                        .SelectMany(outputChannel => outputChannel.CommunicationStatusOutputChannels
                                            .Where(status => status.Status == "to process")
                                            .OrderByDescending(status => status.createdOn)
                                            .Take(1)),
                (communication, _) => new Communication() 
                { 
                    CommunicationDataEnriched = communication.CommunicationDataEnriched 
                })

这个版本生成的SQL和之前的完全一致，但代码更符合EF Core的使用习惯。

验证结果 #

执行上述任意一个查询后，EF Core都会生成你期望的包含CROSS APPLY的T-SQL语句，替换掉原来的INNER JOIN，确保只获取每个CommunicationOutputChannel对应的最新状态记录。

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