linq to sql optimized a group with multiple joins

I need help generating a more efficient LINQ query:

Table: Positions
-PositionID
-Name

Table: Person
-PersonID
-Name, etc...

Table: PersonPosition
-PersonID
-PositionID

I need a result set that groups the people assigned to each position:

PositionID  Person
1           John
            Bob
            Frank

2           Bill
            Tom
            Frank, etc...

My first thought was this LINQ query:

from perspos in PersonPositions
join pers in Persons            on perspos.PersonID equals pers.PersonID 
group pers by perspos.PositionID into groups
select new {groups.Key, groups}

Which works great, but produces the following SQL:

SELECT [t0].[PositionID] AS [Key]
FROM [PersonPosition] AS [t0]
INNER JOIN [Person] AS [t1] ON [t0].[PersonID] = [t1].[PersonID]
GROUP BY [t0].[PositionID]
GO

-- Region Parameters
DECLARE @x1 Int = 3
-- EndRegion
SELECT [t1].[PersonID], [t1].[UserID], [t1].[Firstname], [t1].[Lastname], [t1].[Email], [t1].[Phone], [t1].[Mobile], [t1].[Comment], [t1].[Permissions]
FROM [PersonPosition] AS [t0]
INNER JOIN [Person] AS [t1] ON [t0].[PersonID] = [t1].[PersonID]
WHERE @x1 = [t0].[PositionID]
GO

-- Region Parameters
DECLARE @x1 Int = 4
-- EndRegion
SELECT [t1].[PersonID], [t1].[UserID], [t1].[Firstname], [t1].[Lastname], [t1].[Email], [t1].[Phone], [t1].[Mobile], [t1].[Comment], [t1].[Permissions]
FROM [PersonPosition] AS [t0]
INNER JOIN [Person] AS [t1] ON [t0].[PersonID] = [t1].[PersonID]
WHERE @x1 = [t0].[PositionID]
GO

-- Region Parameters
DECLARE @x1 Int = 5
-- EndRegion
SELECT [t1开发者_高级运维].[PersonID], [t1].[UserID], [t1].[Firstname], [t1].[Lastname], [t1].[Email], [t1].[Phone], [t1].[Mobile], [t1].[Comment], [t1].[Permissions]
FROM [PersonPosition] AS [t0]
INNER JOIN [Person] AS [t1] ON [t0].[PersonID] = [t1].[PersonID]
WHERE @x1 = [t0].[PositionID]
GO

on and on...

Is there a better LINQ query that translates to a more efficient SQL statement?

You should already have the relationship defined in your database, and also on your dbml.

Avoid doing joins when you don't have to; they are really tedious. Let LINQ-to-SQL do this for you. Something like this should work:

var data = context.PersonPositions
    .Select(pos => new { pos.PositionID, pos.Person });
return data.GroupBy(pos => pos.PositionID);

or

return context.Positions.Select(pos => 
    new { pos, pos.PersonPositions.Select(pp => pp.Person).ToList() }).ToList();

I'm fairly sure you have to just join the tables and select the result, then call .AsEnumerable(), and group after that:

(from perspos in PersonPositions
 join pers in Persons
 on perspos.PersonID equals pers.PersonID
 select new { perspos.PositionID, Person = pers })
.AsEnumerable().GroupBy(p => p.PositionID, p => p.Person);