LINQ to Entities、Cross Apply 和 Left Outer Join

引言

我越使用 Entity Framework 和 LINQ to Entities，就学得越多。在我开始使用像 Entity Framework 这样的对象关系映射（O/RM）工具之前，我很久就开始编写存储过程了，所以有时我会玩弄让我的 LINQ 代码生成我想要的精确查询语法。在本文中，我将分享一些我发现的模式。

模式与问题

你们中的许多人都熟悉这里呈现的数据库结构——它是一种相当标准的模式，许多开发人员在职业生涯中都以某种形式实施过。我的实现包括一个 Person 表、一个 PersonLogonName 表中的登录名集合以及一个 User 表。User 可能（也可能不）是 Person，而 Person 可能（也可能不）是 User。

虽然 User 表和 PersonLogonName 表都存储登录名，但 User 表只关心当前用于访问系统的登录名，而 PersonLogonName 表更关注将登录名历史记录与特定人员关联起来。User 表是处理用户偏好在某个网格中显示多少行的设置等信息的代码模块的一部分，而 Person 表实际上是将人员（可能是用户，也可能不是）与其他系统对象关联起来。由于这两个表用于完全不同的目的，并且登录名可能存在于其中一个表而不存在于另一个表中，因此不存在外键。这种外键的缺失稍后将变得很重要。

目标是检索活动登录名的列表，用于数据录入。我决定从活动的 Person 开始，这很容易

SELECT PersonLogonName
FROM Person INNER JOIN PersonLogonName
  ON Person.PersonId = PersonLogonName.PersonId
WHERE Person.IsActive = 1

使用 LINQ to Entities 创建此查询也相对容易。在基于数据库架构生成数据模型（并复数化实体名称）后，这是编写此代码的一种方法。

var query = entities.People
  .Include(
     personEntity => personEntity.PeopleToLogonNames)
  .Where(personEntity => personEntity.IsActive);

解决问题

尽管上述查询很简单，但它返回了所有登录名的完整历史记录，而我只想获取最新的、活动的、未过期的登录名。检索“每组一个”的子对象是“每组一个”模式，我过去曾用子查询中的 SELECT MAX 来实现这一点。SQL Server 2005 引入了 CROSS APPLY 运算符，它是实现此模式的绝佳工具，可以与排序的 SELECT TOP 1 子查询结合使用，如下所示：

SELECT PersonLogonName
FROM Person
CROSS APPLY (SELECT TOP (1) PersonLogonName
             FROM PersonLogonName
             WHERE Person.PersonId = PersonLogonName.PersonId
               AND PersonLogonName.ActiveDate <= SysDateTime()
               AND (PersonLogonName.ExpirationDate IS NULL
                 OR PersonLogonName.ExpirationDate > SysDateTime())
             ORDER BY PersonLogonName.ActiveDate DESC) AS LatestLogonName
WHERE Person.IsActive = 1

我最初认为 LINQ to Entities 版本会很简单，并从这种简单的投影技术开始

var query = entities.People
                .Where(personEntity => personEntity.IsActive)
                .Select(personEntity => personEntity.PeopleToLogonNames
                    .Where(personLogonNameEntity =>
                           personLogonNameEntity.ActiveDate <= DateTime.Now
                        && (personLogonNameEntity.ExpirationDate == null
                         || personLogonNameEntity.ExpirationDate > DateTime.Now))
                    .OrderByDescending(personLogonNameEntity => personLogonNameEntity.ActiveDate)
                    .FirstOrDefault());

这导致生成了一个 OUTER APPLY，它给了我一些 NULL 登录名（并非所有 Person 在我的系统中都有登录名）。我需要的是 CROSS APPLY，而我让 LINQ to Entities 生成 CROSS APPLY 运算符的唯一方法是添加一个额外的 WHERE 子句，如下所示：

var query = entities.People
                .Where(personEntity => personEntity.IsActive)
                .Select(personEntity => personEntity.PeopleToLogonNames
                    .Where(personLogonNameEntity =>
                           personLogonNameEntity.ActiveDate <= DateTime.Now
                        && (personLogonNameEntity.ExpirationDate == null
                         || personLogonNameEntity.ExpirationDate > DateTime.Now))
                    .OrderByDescending(personLogonNameEntity => personLogonNameEntity.ActiveDate)
                    .FirstOrDefault())
                .Where(entity => entity.LogonName != null);

这可能看起来很奇怪，但 Entity Framework 实际上足够智能，可以知道消除结果中的 NULL 值的最佳方法是使用 CROSS APPLY 而不是 OUTER APPLY。EF 仍会在生成的 SQL 中呈现 WHERE 子句，但在查询编译时会被查询处理器忽略。

接下来，我需要包含 User 表中的数据，这带来了自己的问题。如前所述，User 表和 PersonLogonName 表之间没有外键，这排除了使用 Include 方法或 Entity Framework 自动生成的任何导航属性。此外，Entity Framework 不支持 T-SQL FULL OUTER JOIN 运算符（这是可以理解的，因为并非所有数据库系统都支持此运算符）。因此，我决定使用 UNION 运算符，如下所示：

var query = entities.People
                .Where(personEntity => personEntity.IsActive)
                .Select(personEntity => personEntity.PeopleToLogonNames
                    .Where(personLogonNameEntity =>
                           personLogonNameEntity.ActiveDate <= DateTime.Now
                        && (personLogonNameEntity.ExpirationDate == null
                         || personLogonNameEntity.ExpirationDate > DateTime.Now))
                    .OrderByDescending(personLogonNameEntity => personLogonNameEntity.ActiveDate)
                    .FirstOrDefault())
                .Where(entity => entity.LogonName != null)
                .Select(entity => new { entity.LogonName })
                .Union(entities.Users
                               .Select(userEntity => new { userEntity.LogonName }))
                .OrderBy(entity => entity.LogonName);

注意使用了 Select 投影来限制两个结果集中的列列表，并确保列表完全相同。否则将导致编译错误。这效果很好，但导致在 PersonLogonName 表中标记为过期的登录名显示出来，因为它们也存在于 User 表中。我需要一种方法来过滤掉它们，我认为在我的 UNION 的第二个 SELECT 上进行 LEFT OUTER JOIN 是最好的方法。因此，我希望生成类似以下的 SQL：

SELECT PersonLogonName
FROM Person
CROSS APPLY (SELECT TOP (1) PersonLogonName
             FROM PersonLogonName
             WHERE Person.PersonId = PersonLogonName.PersonId
               AND PersonLogonName.ActiveDate <= SysDateTime()
               AND (PersonLogonName.ExpirationDate IS NULL
                 OR PersonLogonName.ExpirationDate > SysDateTime())
             ORDER BY PersonLogonName.ActiveDate DESC) AS LatestLogonName
WHERE Person.IsActive = 1
UNION
SELECT [User].UserLogonName
FROM [User] LEFT OUTER JOIN PersonLogonName
  ON [User].UserLogonName = PersonLogonName.PersonLogonName
WHERE PersonLogonName.ExpirationDate IS NULL
   OR PersonLogonName.ExpirationDate > SysDateTime()
ORDER BY PersonLogonName

由于没有可用的导航属性，我别无选择，只能使用 LINQ to Entities 中的 Join 方法。我最初尝试了以下方法：

var query = entities.People
                .Where(personEntity => personEntity.IsActive)
                .Select(personEntity => personEntity.PeopleToLogonNames
                    .Where(personLogonNameEntity =>
                           personLogonNameEntity.ActiveDate <= DateTime.Now
                        && (personLogonNameEntity.ExpirationDate == null
                         || personLogonNameEntity.ExpirationDate > DateTime.Now))
                    .OrderByDescending(personLogonNameEntity => personLogonNameEntity.ActiveDate)
                    .FirstOrDefault())
                .Where(entity => entity.LogonName != null)
                .Select(entity => new { entity.LogonName })
                .Union(entities.Users
                               .Join(entities.PersonLogonNames,
                                     outerEntity => outerEntity.LogonName,
                                     innerEntity => innerEntity.LogonName,
                                     (userEntity, innerEntity) => new
                                     {
                                         LogonName = userEntity.LogonName,
                                         ExpirationDate = innerEntity.ExpirationDate
                                     })
                               .Where(entity => entity.ExpirationDate == null
                                             || entity.ExpirationDate > DateTime.Now)
                               .Select(entity => new { entity.LogonName }))
                .OrderBy(entity => entity.LogonName);

注意添加了 Join 方法。被连接的表和用于生成 ON 子句的两个操作数都在前三个参数中表示。第四个参数指定了结果集的结构，因此此方法还执行了投影。看起来很完美，但生成的 SQL 始终是 INNER JOIN，而不是 LEFT OUTER JOIN。

最终解决方案

我尝试使用 DefaultIfEmpty 方法调用和 NULL 比较的许多不同组合修改上述代码，但未能说服 Entity Framework 生成 LEFT OUTER JOIN。我研究了此行为，发现其他人也遇到了同样的问题，并看到 GroupJoin 方法会产生我想要的 LEFT OUTER JOIN。不幸的是，GroupJoin 方法的目的是创建包含多个子记录（以父子树的形式）的结果集，因此我需要使用 SelectMany 方法来展平此结果集。这是最终的代码：

var query = entities.People
                .Where(personEntity => personEntity.IsActive)
                .Select(personEntity => personEntity.PeopleToLogonNames
                    .Where(personLogonNameEntity =>
                           personLogonNameEntity.ActiveDate <= DateTime.Now
                        && (personLogonNameEntity.ExpirationDate == null
                         || personLogonNameEntity.ExpirationDate > DateTime.Now))
                    .OrderByDescending(personLogonNameEntity => personLogonNameEntity.ActiveDate)
                    .FirstOrDefault())
                .Where(entity => entity.LogonName != null)
                .Select(entity => new { entity.LogonName })
                .Union(entities.Users
                           .GroupJoin(entities.PersonLogonNames,
                                      outerEntity => outerEntity.LogonName,
                                      innerEntity => innerEntity.LogonName,
                                      (innerEntity, outerEntities) => new
                                      {
                                          LogonName = innerEntity.LogonName,
                                          PersonLogonNames = outerEntities
                                      })
                           .SelectMany(personLogonNameEntities =>
                                       personLogonNameEntities.PersonLogonNames
                                                              .DefaultIfEmpty(),
                                       (combinedUserEntity, personLogonNameEntity) => new
                                       {
                                           combinedUserEntity.LogonName,
                                           personLogonNameEntity.ExpirationDate
                                       })
                           .Where(combinedUserEntity =>
                                  combinedUserEntity.ExpirationDate == null
                               || combinedUserEntity.ExpirationDate > DateTime.Now)
                           .Select(combinedUserEntity => new { combinedUserEntity.LogonName }))
                .OrderBy(entity => entity.LogonName);

我承认，对于如此简单的事情来说，这看起来像是很多复杂的代码，但它生成的 SQL 正是我最初想要的，并且执行速度非常快。这是生成的 SQL：

SELECT
  [Distinct1].[C1] AS [C1],
  [Distinct1].[C2] AS [C2]
FROM (SELECT DISTINCT
        [UnionAll1].[C1] AS [C1],
        [UnionAll1].[PersonLogonName] AS [C2]
      FROM (SELECT
              1 AS [C1],
              [Limit1].[PersonLogonName] AS [PersonLogonName]
            FROM (SELECT
                    [Extent1].[PersonId] AS [PersonId]
                  FROM [dbo].[Person] AS [Extent1]
                  WHERE [Extent1].[IsActive] = 1 ) AS [Filter1]
            CROSS APPLY (SELECT TOP (1)
                           [Project1].[PersonLogonName] AS [PersonLogonName]
                         FROM (SELECT
                                 [Extent2].[PersonLogonName] AS [PersonLogonName],
                                 [Extent2].[ActiveDate] AS [ActiveDate]
                               FROM [dbo].[PersonLogonName] AS [Extent2]
                               WHERE ([Filter1].[PersonId] = [Extent2].[PersonId])
                                 AND ([Extent2].[ActiveDate] <= SysDateTime())
                                 AND (([Extent2].[ExpirationDate] IS NULL)
                                   OR ([Extent2].[ExpirationDate] > SysDateTime()))
                              ) AS [Project1]
                         ORDER BY [Project1].[ActiveDate] DESC) AS [Limit1]
            WHERE [Limit1].[PersonLogonName] IS NOT NULL
            UNION ALL
            SELECT
              1 AS [C1],
              [Extent3].[UserLogonName] AS [UserLogonName]
            FROM [dbo].[User] AS [Extent3]
            LEFT OUTER JOIN [dbo].[PersonLogonName] AS [Extent4]
              ON [Extent3].[UserLogonName] = [Extent4].[PersonLogonName]
            WHERE ([Extent4].[ExpirationDate] IS NULL)
               OR ([Extent4].[ExpirationDate] > SysDateTime())) AS [UnionAll1]
    ) AS [Distinct1]
    ORDER BY [Distinct1].[C2] ASC

关注点

通过细致的故障排除过程，将问题分解成更小的部分并单独关注每个部分，我最终能够用出乎意料的代码解决了更大的问题。这是一次有趣的旅程，结果令人满意。

本文使用了 Entity Framework 版本 6.1.3。

历史

• 2017 年 5 月 12 日：初版