Forcing linq to perform inner joins
Since PersonId is declared NOT NULL
(and I assume it is declared as an FK to People) then I'm not sure how you could have a CompanyPosition with a person that is not assigned; and Linq can't see how you can eiter, which is why as you have observed Linq considers the join redundant.
If you're using LinqToSql, you can use LoadWith similar to this:
var context = new MyDataContext();var options = new DataLoadOptions();options.LoadWith<People>(x => x.CompanyPositions);context.LoadOptions = options;
I don't know how to force linq to use a join. But the following statment should give you the required result.
return (from pos in CompanyPositions where (p in People select p.PersonId).Contains(pos.PersonId) select pos).ToList();