Linq to sql optimized a group with multiple joins?

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().

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 have a one-to-many relation setup from Person -> PersonPosition, but not the other way. Having trouble adding that relationship, I'm getting "the columns in table PersonPosition do not match any existing primary key or unique constraint". I cant make PersonPosition's PersonID a PK or unique index because it's the intermediate in my many-to-many relationship in Person-PersonPosition-Position, so there will be multiple records with the same PersonID – PeteShack Dec 21 '10 at 4:55 Do you have a primary key on the PersonPosition table at all?

You can either create a surrogate PK (PersonPositionId) or make a composite out of both PersonId and PositionId, but you will need a primary key of some sort for LINQ-to-SQL to handle it. – Kirk Broadhurst Dec 21 '10 at 5:06 Thanks. Yes I have a composite primary key of (PersonID, PositionID) on PersonPositions table, but still get that same error.

I'm allowing nulls for PersonID in both tables, would that cause it? – PeteShack Dec 21 '10 at 5:29 Well that might cause something - SQL doesn't let you have a nullable PK, whether it's a single column or a composite, so I don't know! Check that the PKs are still on the tables, and that they show up on the dbml designer.

The columns should be non nullable in the database, and the dbml should also reflect this (in the properties viewer) – Kirk Broadhurst Dec 21 '10 at 5:38.

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

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.