Force inner join with many-to-many relationship entity framework

Question

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)

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

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]

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

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]

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?

Thanks.

UPDATE

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'

Do you want your query to be something like: SELECT * FROM (SELECT * from Users where ...) Usr inner join Skill where ... ? — Cosmin Vană, Apr 18 '14 at 18:52
Possible Duplicate of http://stackoverflow.com/questions/6176192/inner-join-in-linq-to-entities — Nathan, Apr 26 '14 at 20:31
Nathan, I don't see what is duplicate? That join does not look like a many-to-many as far as I can see. Can you please explain? — ryanulit, Apr 28 '14 at 13:20
i dont have VS here with me: but to me it looks we can try something like this : dbcontext.users.where(x => x.id == 5).include("skills") — qamar, Apr 29 '14 at 16:35
@Nathan Not a duplicate because in many-to-many, there's nothing in the entity model to join on — Ben Aaronson, Apr 29 '14 at 17:18
Have you profiled anything? You're not supposed to care about the SQL generated by your L2E provider, unless there's a performance problem. — user247702, Apr 29 '14 at 21:03
Thanks qamar, but the include made the sql even crazier. I haven't profiled the performance Stijin, and while the difference may in fact be minimal, direct inner joins are usually faster than joining on a sub-query. My goal was to find out if that can be done, and whether I am doing something wrong with my linq query code or SQL DB setup. — ryanulit, Apr 30 '14 at 13:58

score 3 · Accepted Answer · answered Apr 30 '14 at 21:48

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

Thanks Josh. I was hoping to not have to go that route, but it looks like it is either that or just deal with the sub-query. — ryanulit, May 01 '14 at 14:29

score 0 · Answer 2 · answered Apr 30 '14 at 14:38

0

@ryanulit, your issue is valid and it is an issue for all Linq to Entities. Please check the posted MS Forum's URL

MS Forum's URL

answered Apr 30 '14 at 14:38

Shubhojit

121
6

-1 a discussion from September 2008, a month after the initial release of Entity Framework, is irrelevant to a question about EF 5. – user247702 Apr 30 '14 at 18:02
@Stijn, The EF 5 has some advanced features only. This EF 5 still holds issue of adding extra join for every && operation. The answer given by Josh is a workaround to avoid this. – Shubhojit May 06 '14 at 12:33

Force inner join with many-to-many relationship entity framework

2 Answers2

Linked