如何在LINQ to SQL中编写和优化一对多关系的三表连接?

本文关键字：关系 一对多 优化 连接 LINQ to SQL | 更新日期: 2023-09-27 17:53:39

理想类结构

游戏有许多玩家，每个玩家都有许多统计数据。也就是说，每个List<Game>包含一个List<Player>，每个Player包含一个List<Statistic>。

Game -> Player1 -> Statistic1
                   ....
                   Statistic30
        ....
        Player10 -> Statistic1
                    ....
                    Statistic30

基本表模式

Game
----
GameId (int)
Region (nvarchar(4))
Player
------
GameId (int)
Region (nvarchar(4))
AccountId (int)
Statistic
---------
GameId (int)
Region (nvarchar(4))
AccountId (int)

我的尝试
var b = (from g in db.Games
         select new GameDTO()
         {
             GameId = g.GameId,
             Players = (from p in db.PlayerGames
                        where p.GameId == g.GameId && p.Region.Equals(g.Region)
                        select new PlayerGameDTO()
                        {
                            AccountId = p.AccountId,
                            GameId = p.GameId,
                            Region = p.Region,
                            Statistics = (from r in db.Statistics
                                          where r.AccountId == p.AccountId && r.GameId == p.GameId && r.Region.Equals(p.Region)
                                        select r).ToList()
                        }).ToList()
         });

这个解决方案(显然)没有使用Join，很大程度上是因为我不确定如何以正确的顺序执行Join来实现预期的结果。

我应该提到的是，我们每天收集约10万款新游戏，约100万名玩家和约3000万项统计数据。当前的查询每秒可以选择~1.4个游戏，并且使用99%的超线程四核CPU。

如果有任何地方浑浊，请随时要求澄清。

Update # 1 

var d = (from g in db.Games
         join p in db.PlayerGames on new { g.GameId, g.Region } equals new { p.GameId, p.Region }
         join r in db.Statistics on new { p.GameId, p.Region, p.AccountId } equals new { r.GameId, r.Region, r.AccountId }
         select new StatisticsDTO()
         {
             GameId = r.GameId, 
             AccountId = r.AccountId, 
             StatType = r.StatType,
             Value = r.Value
         });

这么简单的东西每秒输出约9K(比原来快22倍)行。很明显，SQL Server使用了90%的CPU来完成所有的工作。然而，与嵌套对象不同，我留下了一个一维查询。

如果你对这次更新有什么建议，我很乐意听到。

如何在LINQ to SQL中编写和优化一对多关系的三表连接?

听起来更适合让数据库处理一些工作负载，特别是如果您只是运行查询而不向数据库写入内容。考虑在数据库中创建一个实现连接的视图。然后，您可以查询视图并避免在客户机上连接。您仍然可以使用实体数据模型和LINQ对视图运行查询。使用这种方法，您应该会看到相当不错的性能提升。

//Possible SQL for creating the view
CREATE VIEW vw_GameData AS 
SELECT g.GameId, g.Region, p.AccountId, etc...
FROM Game g JOIN Player p ON (g.GameId = p.GameId AND g.Region = p.Region)
JOIN Statistic s ON (s.GameId = p.GameId AND s.RegionId = p.RegionId AND s.AccountId = p.AccountId)

首先尝试一个简单的linq连接。

Game
----
GameId (int)
Region (nvarchar(4))
Player
------
GameId (int)
Region (nvarchar(4))
AccountId (int)
Statistic
---------
GameId (int)
Region (nvarchar(4))
AccountId (int)

var b = (from t in db.Games
         join t1 in t.Player on t.GameId equals t1.GameId
         join t2 in t.Statistic on t.GameId equals t2.GameId
         select new PlayerGameDTO
         {
            AccountId = t1.AccountId,
            GameId = t1.GameId,
            Region = t1.Region,
            //RawStats <-- what are you trying to do here?
            //RawStats = (from r in db.RawStats
            //where r.AccountId == p.AccountId && r.GameId == p.GameId && r.Region.Equals(p.Region) select r).ToList()
         }).ToList();
相关文章：