c# - 如何在 Entity Framework 投影中有效地获取远距离关系

Question

我正在尝试编写一个 LINQ/Entity Framework 投影，它将从远距离关系中拉回两个字段。对于这个问题的所有目的，我有这些结构:

玩过的游戏

• int Id (PK)
• List<PlayerGameResult> PlayerGameResults

播放器

• int Id (PK)
• string Name

PlayerGameResult

• PlayedGame PlayedGame (FK 到 PlayedGame)
• Player Player (FK 到玩家)

对于给定的已玩游戏，我想获取该已玩游戏中任意一位玩家的姓名和玩家 ID。实际上，我的例子有点复杂，但我省略了细节，因为它们会分散注意力。

以下是我能想到的最好的预测:

 var result = dataContext.GetQueryable<PlayedGame>()
    .Where(playedGame => playedGame.Id == somePlayedGameId)
    .Select(x => new
        {
            Name = x.PlayerGameResults.FirstOrDefault() != null ? x.PlayerGameResults.FirstOrDefault().Player.Name : null,
            Id = x.PlayerGameResults.FirstOrDefault() != null ? x.PlayerGameResults.FirstOrDefault().Player.Id : 0
    })
    .FirstOrDefault();

...但是生成的底层 SQL 对我来说有点可怕:

SELECT
    [Limit5].[Id] AS [Id], 
    [Limit5].[C1] AS [C1], 
    [Limit5].[C2] AS [C2]
FROM 
    (SELECT TOP (1) 
         [Project11].[Id] AS [Id], 
         CASE WHEN ([Project11].[C1] IS NOT NULL) THEN [Project11].[Name] END AS [C1], 
         CASE WHEN ([Project11].[C2] IS NOT NULL) THEN [Project11].[C3] ELSE 0 END AS [C2]
     FROM 
         (SELECT 
        [Project9].[Id] AS [Id], 
        [Project9].[Name] AS [Name], 
        [Project9].[C1] AS [C1], 
        [Project9].[C2] AS [C2], 
        (SELECT TOP (1) 
            [Extent6].[PlayerId] AS [PlayerId]
            FROM [dbo].[PlayerGameResult] AS [Extent6]
            WHERE [Project9].[Id] = [Extent6].[PlayedGameId]) AS [C3]
        FROM ( SELECT 
            [Project8].[Id] AS [Id], 
            [Project8].[Name] AS [Name], 
            [Project8].[C1] AS [C1], 
            [Project8].[C2] AS [C2]
            FROM ( SELECT 
                [Project6].[Id] AS [Id], 
                [Project6].[Name] AS [Name], 
                [Project6].[C1] AS [C1], 
                (SELECT TOP (1) 
                    [Extent5].[Id] AS [Id]
                    FROM [dbo].[PlayerGameResult] AS [Extent5]
                    WHERE [Project6].[Id] = [Extent5].[PlayedGameId]) AS [C2]
                FROM ( SELECT 
                    [Project5].[Id] AS [Id], 
                    [Extent4].[Name] AS [Name], 
                    [Project5].[C1] AS [C1]
                    FROM   (SELECT 
                        [Project3].[Id] AS [Id], 
                        [Project3].[C1] AS [C1], 
                        (SELECT TOP (1) 
                            [Extent3].[PlayerId] AS [PlayerId]
                            FROM [dbo].[PlayerGameResult] AS [Extent3]
                            WHERE [Project3].[Id] = [Extent3].[PlayedGameId]) AS [C2]
                        FROM ( SELECT 
                            [Project2].[Id] AS [Id], 
                            [Project2].[C1] AS [C1]
                            FROM ( SELECT 
                                [Extent1].[Id] AS [Id], 
                                (SELECT TOP (1) 
                                    [Extent2].[Id] AS [Id]
                                    FROM [dbo].[PlayerGameResult] AS [Extent2]
                                    WHERE [Extent1].[Id] = [Extent2].[PlayedGameId]) AS [C1]
                                FROM [dbo].[PlayedGame] AS [Extent1]
                                WHERE [Extent1].[Id] = @p__linq__0
                            )  AS [Project2]
                        )  AS [Project3] ) AS [Project5]
                    LEFT OUTER JOIN [dbo].[Player] AS [Extent4] ON [Project5].[C2] = [Extent4].[Id]
                )  AS [Project6]
            )  AS [Project8]
        )  AS [Project9]
    )  AS [Project11]
)  AS [Limit5]

有没有“更好”的方式来写这个投影？换句话说，我如何在 LINQ/Entity Framework 中编写此查询以仅以产生体面/理智查询的方式从 Player 表中拉回这两个字段？我不会假装确定上面的查询很糟糕——但它对我来说似乎不在大概范围内。

期待您的想法!

Answer 1

整个 LINQ 语句被翻译成 SQL，这意味着您不必担心空检查。 SQL 没有空引用的概念(我们可以说，它从一开始就有空传播!)。所以你的陈述可以简化为:

var result = dataContext.GetQueryable<PlayedGame>()
    .Where(player => player.Id == testPlayerWithNoPlayedGames.Id)
    .Select(x => new
    {
        Name = x.PlayerGameResults.FirstOrDefault().Player.Name,
        Id = (int?)x.PlayerGameResults.FirstOrDefault().Player.Id
    }).FirstOrDefault();

查询语法可以让它看起来不那么复杂:

var result = (from playerGame in dataContext.GetQueryable<PlayedGame>()
              where playerGame.Id == testPlayerWithNoPlayedGames.Id
              let player = x.PlayerGameResults.FirstOrDefault().Player
              select new
              {
                  Name = player.Name,
                  Id = (int?)player.Id
              }).FirstOrDefault();