Forcing linq to perform inner joins

Question

I'm trying to force Linq to preform an inner join between two tables. I'll give an example.

CREATE TABLE [dbo].[People] (
   [PersonId] [int] NOT NULL,
   [Name] [nvarchar](MAX) NOT NULL,
   [UpdatedDate] [smalldatetime] NOT NULL
   ... Other fields ...
)

CREATE TABLE [dbo].[CompanyPositions] (
   [CompanyPositionId] [int] NOT NULL,
   [CompanyId] [int] NOT NULL,
   [PersonId] [int] NOT NULL,
   ... Other fields ...
)

Now I'm working with unusual database as there's a reason beyond my control for people to be missing from the People table but have a record in CompanyPositions. I want to filter out CompanyPositions with missing People by joining the tables.

return (from pos in CompanyPositions
        join p in People on pos.PersonId equals p.PersonId
        select pos).ToList();

Linq sees this join as redundant and removes it from the SQL it generates.

SELECT 
[Extent1].[CompanyPositionId] AS [CompanyPositionId], 
[Extent1].[CompanyId] AS [CompanyId], 
.... 
FROM  [dbo].[CompanyPositions] AS [Extent1]

However it's not redundant in my case. I can fix it like this

// The min date check will always be true, here to force linq to perform the inner join
var minDate = DateTimeExtensions.SqlMinSmallDate;

return (from pos in CompanyPositions
        join p in People on pos.PersonId equals p.PersonId
        where p.UpdatedDate >= minDate
        select pos).ToList();

However this now creates a needless where clause in my SQL. As a purest I'd like to remove this. Any idea's or does the current database design tie my hands?

Answer 1

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.