c# - 强制内部加入多对多关系 Entity Framework

Question

我的数据库中有一个多对多关系设置，如下所示:

User
-------
Id (PK, Identity)
First
Last
...various other fields

Skill
-------
Id (PK, Identity)
Description

UserSkill
-----------
UserId (PK, FK on User.Id)
SkillId (PK, FK On Skill.Id)

当我在 DbContext 上运行此 LINQ 查询时:

from u in Users 
from s in u.Skills 
where s.Id == 5 
select new 
{
    u.Id,
    s.Description
})

生成的 SQL 包含我想要的所有内部联接:

SELECT 
[Extent1].[UserId] AS [UserId], 
[Extent2].[Description] AS [Description]
FROM  [dbo].[UserSkill] AS [Extent1]
INNER JOIN [dbo].[Skill] AS [Extent2] ON [Extent1].[SkillId] = [Extent2].[Id]
WHERE 5 = [Extent2].[Id]

但是，当我添加一个简单的额外 where 子句时:

from u in Users 
from s in u.Skills 
where s.Id == 5 
    && u.Last == "test"
select new 
{
    u.Id,
    s.Description
})

现在生成的 SQL 使用子查询:

[Extent1].[Id] AS [Id], 
[Filter1].[Description] AS [Description]
FROM  [dbo].[User] AS [Extent1]
INNER JOIN  (SELECT [Extent2].[UserId] AS [UserId], [Extent3].[Description] AS [Description]
    FROM  [dbo].[UserSkill] AS [Extent2]
    INNER JOIN [dbo].[Skill] AS [Extent3] ON [Extent3].[Id] = [Extent2].[SkillId]
    WHERE 5 = [Extent3].[Id] ) AS [Filter1] ON [Extent1].[Id] = [Filter1].[UserId]
WHERE 'test' = [Extent1].[Last]

也许我遗漏了一些东西，但我认为 EF 会为此查询将另一个连接添加回 User 表，并且能够在 User.Last 上执行 where 而不是执行子查询。有没有办法强制这种行为？难道我做错了什么？

谢谢。

---

更新

Cosmin，我希望查询结果如下:

SELECT u.Id, s.Description
FROM [User] u INNER JOIN
        [UserSkill] us ON u.Id = us.UserId INNER JOIN
        [Skill] s ON us.SkillId = s.Id
WHERE s.Id = 2 AND u.Last = 'test'

Answer 1

看起来这是 EF 目前没有做的优化。就个人而言，除非性能成为问题，否则我会坚持使用它生成的子查询。

但是如果您愿意失去 User 和 Skill 的直接导航属性，您可以对中间表进行建模以获取您要查找的查询。

public class User
{
    public int Id { get; set; }
    public string First { get; set; }
    public string Last { get; set; }

    public virtual ICollection<UserSkill> UserSkills { get; set; }
}

public class UserSkill
{
    public int Id { get; set; }
    [Required]
    public User User { get; set; }
    [Required]
    public Skill Skill { get; set; }

}

public class Skill
{
    public int Id { get; set; }
    public string Description { get; set; }

    public virtual ICollection<UserSkill> UserSkills { get; set; }
}

然后下面的查询将产生一个连接而不是子查询

 from x in db.UserSkills 
 where x.Skill.Id == 5 && x.User.Last == "test"
 select new {x.User.Id, x.Skill.Description};