如何优化EF Core中基于两表关联分组的LINQ查询效率？

我正在使用Entity Framework Core，需要编写从SccmTable和Sailpoint两张表获取数据的LINQ查询，需满足以下条件：

• Sailpoint表中lcstate字段值为deleted
• Sailpoint表的UserName与SccmTable表的Username相等
• 仅选择机器名（MachineName）唯一的行

我尝试了以下两种写法，均可正常运行但效率较低：

写法一：

var result = (from r in DBContext.SccmTable
              join s in DBContext.Sailpoint on r.UserName equals s.Username
              where s.LcState == "DELETED"
              select r).ToList() 
                       .GroupBy(r => r.MachineName)
                       .Select(g => g.First());

写法二：

var result = DBContext.SccmTable
                      .Where(x => DBContext.Sailpoint
                      .Where(y => y.LcState == "DELETED")
                      .Select(y => y.Username)
                      .Contains(x.UserName))
                      .Select(x => x)
                      .Distinct().ToList();

希望了解如何提升该查询的性能。

1. 先加数据库索引（最关键） #

索引是提升查询速度的核心，直接在数据库端创建以下索引：

• 给Sailpoint表建复合索引：CREATE INDEX IX_Sailpoint_LcState_Username ON Sailpoint(LcState, Username);，这样筛选LcState='DELETED'并匹配用户名时能快速定位数据。
• 给SccmTable表的UserName字段建索引：CREATE INDEX IX_SccmTable_UserName ON SccmTable(UserName);，加速两表关联时的匹配效率。
• 给SccmTable表的MachineName字段建索引：CREATE INDEX IX_SccmTable_MachineName ON SccmTable(MachineName);，帮助快速分组或去重。

2. 避免客户端内存处理数据 #

你的写法一先调用ToList()把所有符合条件的数据加载到内存，再做分组，数据量大时性能崩得快。把分组逻辑推到数据库执行，修改后：

var result = (from r in DBContext.SccmTable
              join s in DBContext.Sailpoint on r.UserName equals s.Username
              where s.LcState == "DELETED"
              group r by r.MachineName into g
              select g.FirstOrDefault()).ToList();

这样EF Core会把GROUP BY和取第一条的逻辑翻译成SQL在数据库端跑，减少内存占用和数据传输量。

3. 用Any替代Contains提升关联性能 #

写法二的Contains会生成IN子查询，当符合条件的用户名数量多的时候，IN的性能不如EXISTS。改用Any生成EXISTS查询，性能更稳定：

var result = DBContext.SccmTable
                      .Where(x => DBContext.Sailpoint
                                          .Any(y => y.LcState == "DELETED" && y.Username == x.UserName))
                      .GroupBy(x => x.MachineName)
                      .Select(g => g.FirstOrDefault())
                      .ToList();

4. 只查需要的字段 #

如果不需要SccmTable的所有字段，只选必要的列能大幅减少数据传输量，比如：

var result = (from r in DBContext.SccmTable
              join s in DBContext.Sailpoint on r.UserName equals s.Username
              where s.LcState == "DELETED"
              group r by r.MachineName into g
              select g.Select(x => new { x.MachineName, x.UserName }).FirstOrDefault()).ToList();

5. 检查生成的SQL和执行计划 #

打开EF Core的日志功能查看生成的SQL语句，比如通过日志提供者输出到控制台，然后把SQL拿到数据库里看执行计划，定位有没有冗余关联或者全表扫描的情况，针对性优化。

内容的提问来源于stack exchange，提问作者Sapri s