使用多对多关系实体框架强制内部联接 [英] Force inner join with many-to-many relationship entity framework

问题描述

我在数据库中建立了多对多关系，就像这样:

I have a many-to-many relationship setup in my database like so:

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查询时:

When I run this LINQ query on the DbContext:

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

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

The SQL generated contains all inner joins which is what I want:

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子句时:

However, when I add a simple extra where clause:

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

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

The SQL generated now uses a sub-query:

[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操作，而不是执行子查询.有什么办法可以强迫这种行为?难道我做错了什么?

Maybe I am missing something, but I would think EF would just add another join back to the User table for this query and be able to do a where on User.Last instead of doing a sub-query. Is there any way to force this kind of behavior? Am I doing something wrong?

谢谢.

更新

Cosmin，我希望这样发出查询:

Cosmin, I am wanting the query to come out like this:

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'

推荐答案

看起来这是EF当前不做的优化.就个人而言，除非性能出现问题，否则我会坚持使用它生成的子查询.

Looks like this is an optimization that EF does not currently do. Personally, I'd stick with the sub query it generates unless performance becomes a problem.

但是，如果您愿意丢失用户和技能"的直接导航属性，则可以对中间表进行建模以获得所需的查询.

But if you are willing to lose the direct navigation properties for User and Skill, you can model the intermediate table to get the query you are looking for.

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; }
}

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

Then the following query will produce a join instead of subquery

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

这篇关于使用多对多关系实体框架强制内部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！