如何让EF Core生成Join+Where而非子查询的SQL语句？

数据库模型 #

用于MySQL的Entity Framework实体类：

public class User
{
    public Guid id { get; set; }
    public string? firstName { get; set; }
    public string? lastName { get; set; }
    public string? email { get; set; }
    public string? displayName { get; set; }
    public virtual List<UserRequest>? userRequests { get; set; }
}

public class UserRequest
{
    public int id { get; set; }
    public string? action { get; set; }
    public string? domain { get; set; }
    public string? pathAccessing { get; set; }
    public string? applicationName { get; set; }
    public string? type { get; set; }
    public DateTime createdDate { get; set; }
    public Guid userId { get; set; }
    public User? user { get; set; }
}

需求说明 #

模型中一个User对应多个UserRequest，需要查询曾访问过指定域名的用户，期望生成的SQL语句如下：

select * from User u 
inner join UserRequest ur on ur.userId = u.id
where ur.domain in ("http://localhost", "http://localhost.dev")

当前实现及问题 #

当前使用的Linq语句：

var domainsList = new string[] {"http://localhost", "http://localhost.dev"};
var result = _DBContext.User
              .Include(q => q.userRequests)             
              .Where(t => t.userRequests.Any(u => domainsList.Contains(u.domain)));

生成的SQL使用了EXISTS子查询而非预期的INNER JOIN结构：

SELECT `u`.`id`, `u`.`displayName`, `u`.`email`, `u`.`firstName`, `u`.`lastName`, `u1`.`id`, `u1`.`action`, `u1`.`applicationName`, `u1`.`createdDate`, `u1`.`domain`, `u1`.`pathAccessing`, `u1`.`type`, `u1`.`userID`
FROM `user` AS `u`
LEFT JOIN `userRequest` AS `u1` ON `u`.`id` = `u1`.`userID`
WHERE EXISTS (
    SELECT 1
    FROM `userRequest` AS `u0`
    WHERE (`u`.`id` = `u0`.`userID`) AND `u0`.`domain` IN ('http://localhost', 'http://localhost.dev'))
ORDER BY `u`.`id`

由于数据库规模小，子查询速度更慢，需要调整Linq语句让EF生成符合预期的INNER JOIN结构。

解决方案 #

可以通过以下两种方式改写Linq，让EF生成接近预期的INNER JOIN SQL：

方法1：从UserRequest出发关联User #

var domainsList = new string[] {"http://localhost", "http://localhost.dev"};
var result = _DBContext.UserRequest
    .Where(ur => domainsList.Contains(ur.domain))
    .Join(_DBContext.User, ur => ur.userId, u => u.id, (ur, u) => u)
    .Distinct()
    .Include(u => u.userRequests);

方法2：使用SelectMany构造关联 #

var domainsList = new string[] {"http://localhost", "http://localhost.dev"};
var result = _DBContext.User
    .SelectMany(u => u.userRequests.Where(ur => domainsList.Contains(ur.domain))
                                   .Select(ur => u))
    .Distinct()
    .Include(u => u.userRequests);

这两种写法都会让EF生成基于INNER JOIN的查询，同时通过Distinct()避免重复的User记录，Include()依然可以加载关联的userRequests数据。

原来的Linq之所以生成EXISTS子查询，是因为Include()会优先用LEFT JOIN加载关联数据，再通过Any()的条件用EXISTS过滤用户；而改写后的查询直接从关联条件出发构建INNER JOIN，更符合你期望的SQL结构。

内容的提问来源于stack exchange，提问作者Chris Sim