1: SELECT
2: [Project3].[EstablishmentId] AS [EstablishmentId],
3: [Project3].[C1] AS [C1],
4: [Project3].[StatusId] AS [StatusId],
5: [Project3].[Description] AS [Description],
6: [Project3].[LastEditTime] AS [LastEditTime],
7: [Project3].[LastEditedBy] AS [LastEditedBy],
8: [Project3].[CareTypeId] AS [CareTypeId],
9: [Project3].[Name] AS [Name],
10: [Project3].[Parameter1] AS [Parameter1],
11: [Project3].[Parameter2] AS [Parameter2],
12: [Project3].[Parameter3] AS [Parameter3],
13: [Project3].[Parameter4] AS [Parameter4],
14: [Project3].[Parameter5] AS [Parameter5],
15: [Project3].[Parameter6] AS [Parameter6],
16: [Project3].[Parameter7] AS [Parameter7],
17: [Project3].[Parameter8] AS [Parameter8],
18: [Project3].[Parameter9] AS [Parameter9],
19: [Project3].[Parameter10] AS [Parameter10],
20: [Project3].[Parameter11] AS [Parameter11],
21: [Project3].[Parameter12] AS [Parameter12],
22: [Project3].[Parameter13] AS [Parameter13],
23: [Project3].[Parameter14] AS [Parameter14],
24: [Project3].[NrChildren] AS [NrChildren],
25: [Project3].[NrHoursPerWeek] AS [NrHoursPerWeek],
26: [Project3].[ArrivingTime] AS [ArrivingTime],
27: [Project3].[LeavingTime] AS [LeavingTime],
28: [Project3].[ArrivingTimeInterruption] AS [ArrivingTimeInterruption],
29: [Project3].[LeavingTimeInterruption] AS [LeavingTimeInterruption],
30: [Project3].[PersonIndex] AS [PersonIndex],
31: [Project3].[HasInterruption] AS [HasInterruption],
32: [Project3].[PersonIndexAtNoon] AS [PersonIndexAtNoon]
33: FROM ( SELECT
34: [Limit1].[EstablishmentId] AS [EstablishmentId],
35: [Project2].[CareTypeId] AS [CareTypeId],
36: [Project2].[StatusId] AS [StatusId],
37: [Project2].[Description] AS [Description],
38: [Project2].[LastEditTime] AS [LastEditTime],
39: [Project2].[LastEditedBy] AS [LastEditedBy],
40: [Project2].[Name] AS [Name],
41: [Project2].[Parameter1] AS [Parameter1],
42: [Project2].[Parameter2] AS [Parameter2],
43: [Project2].[Parameter3] AS [Parameter3],
44: [Project2].[Parameter4] AS [Parameter4],
45: [Project2].[Parameter5] AS [Parameter5],
46: [Project2].[Parameter6] AS [Parameter6],
47: [Project2].[Parameter7] AS [Parameter7],
48: [Project2].[Parameter8] AS [Parameter8],
49: [Project2].[Parameter9] AS [Parameter9],
50: [Project2].[Parameter10] AS [Parameter10],
51: [Project2].[Parameter11] AS [Parameter11],
52: [Project2].[Parameter12] AS [Parameter12],
53: [Project2].[Parameter13] AS [Parameter13],
54: [Project2].[Parameter14] AS [Parameter14],
55: [Project2].[NrChildren] AS [NrChildren],
56: [Project2].[NrHoursPerWeek] AS [NrHoursPerWeek],
57: [Project2].[ArrivingTime] AS [ArrivingTime],
58: [Project2].[LeavingTime] AS [LeavingTime],
59: [Project2].[ArrivingTimeInterruption] AS [ArrivingTimeInterruption],
60: [Project2].[LeavingTimeInterruption] AS [LeavingTimeInterruption],
61: [Project2].[PersonIndex] AS [PersonIndex],
62: [Project2].[HasInterruption] AS [HasInterruption],
63: [Project2].[PersonIndexAtNoon] AS [PersonIndexAtNoon],
64: [Project2].[C1] AS [C1]
65: FROM (SELECT TOP (1)
66: [Extent1].[EstablishmentId] AS [EstablishmentId]
67: FROM [dbo].[Establishments] AS [Extent1]
68: WHERE [Extent1].[EstablishmentId] = '6B555389-3DAC-4B42-9CB4-903A572AED09' ) AS [Limit1]
69: LEFT OUTER JOIN (SELECT
70: [Extent2].[EstablishmentId] AS [EstablishmentId],
71: [Extent3].[CareTypeId] AS [CareTypeId],
72: [Extent3].[StatusId] AS [StatusId],
73: [Extent3].[Description] AS [Description],
74: [Extent3].[LastEditTime] AS [LastEditTime],
75: [Extent3].[LastEditedBy] AS [LastEditedBy],
76: [Extent3].[Name] AS [Name],
77: [Extent3].[Parameter1] AS [Parameter1],
78: [Extent3].[Parameter2] AS [Parameter2],
79: [Extent3].[Parameter3] AS [Parameter3],
80: [Extent3].[Parameter4] AS [Parameter4],
81: [Extent3].[Parameter5] AS [Parameter5],
82: [Extent3].[Parameter6] AS [Parameter6],
83: [Extent3].[Parameter7] AS [Parameter7],
84: [Extent3].[Parameter8] AS [Parameter8],
85: [Extent3].[Parameter9] AS [Parameter9],
86: [Extent3].[Parameter10] AS [Parameter10],
87: [Extent3].[Parameter11] AS [Parameter11],
88: [Extent3].[Parameter12] AS [Parameter12],
89: [Extent3].[Parameter13] AS [Parameter13],
90: [Extent3].[Parameter14] AS [Parameter14],
91: [Extent3].[NrChildren] AS [NrChildren],
92: [Extent3].[NrHoursPerWeek] AS [NrHoursPerWeek],
93: [Extent3].[ArrivingTime] AS [ArrivingTime],
94: [Extent3].[LeavingTime] AS [LeavingTime],
95: [Extent3].[ArrivingTimeInterruption] AS [ArrivingTimeInterruption],
96: [Extent3].[LeavingTimeInterruption] AS [LeavingTimeInterruption],
97: [Extent3].[PersonIndex] AS [PersonIndex],
98: [Extent3].[HasInterruption] AS [HasInterruption],
99: [Extent3].[PersonIndexAtNoon] AS [PersonIndexAtNoon],
100: 1 AS [C1]
101: FROM (SELECT
102: [EstablishmentCareTypes].[EstablishmentId] AS [EstablishmentId],
103: [EstablishmentCareTypes].[CareTypeId] AS [CareTypeId]
104: FROM [dbo].[EstablishmentCareTypes] AS [EstablishmentCareTypes]) AS [Extent2]
105: INNER JOIN [dbo].[CareTypes] AS [Extent3] ON [Extent3].[CareTypeId] = [Extent2].[CareTypeId] ) AS [Project2] ON [Limit1].[EstablishmentId] = [Project2].[EstablishmentId]
106: ) AS [Project3]
107: ORDER BY [Project3].[EstablishmentId] ASC, [Project3].[C1] ASC, [Project3].[Name] ASC
How about :
var careTypes = from e in context.EstablishmentSet
join ct from e.CareTypes on e.CareTypeId equals ct.CareTypeId
where e.EstablishmentId == establishmentId
orderby ct.Name
select ct
?
This is a classic InnerJoin in LINQ.
Actually this won’t work, as described here: http://stackoverflow.com/questions/1097992/linq-to-entities-many-to-many-select-query (see second answer) The join would work in the case that the many-to-many table EstablishmentCareTypes is also accessible in the ObjectContext. In this case it isn’t, because it only contains two foreign keys and EF hides it for the user.