I have a LEFT OUTER OUTER join in LINQ that is combining with the outer join condition and not providing the desired results. It is basically limiting my LEFT side result with this combination. Here is the LINQ and resulting SQL. What I'd like is for "AND ([t2].[EligEnd] = @p0" in the LINQ query to not bew part of the join condition but rather a subquery to filter results BEFORE the join.

Thanks in advance (samples pulled from LINQPad) - Doug

(from l in Users
                       join mr in (from mri in vwMETRemotes where met.EligEnd == Convert.ToDateTime("2009-10-31") select mri) on l.Mahcpid equals mr.Mahcpid into lo
                       from g in lo.DefaultIfEmpty()
                       orderby l.LastName, l.FirstName
                       where l.LastName.StartsWith("smith") && l.DeletedDate == null 
                       select g)

Here is the resulting SQL

-- Region Parameters
DECLARE @p0 DateTime = '2009-10-31 00:00:00.000'
DECLARE @p1 NVarChar(6) = 'smith%'
-- EndRegion
SELECT [t2].[test], [t2].[MAHCPID] AS [Mahcpid], [t2].[FirstName], [t2].[LastName], [t2].[Gender], [t2].[Address1], [t2].[Address2], [t2].[City], [t2].[State] AS [State], [t2].[ZipCode], [t2].[Email], [t2].[EligStart], [t2].[EligEnd], [t2].[Dependent], [t2].[DateOfBirth], [t2].[ID], [t2].[MiddleInit], [t2].[Age], [t2].[SSN] AS [Ssn], [t2].[County], [t2].[HomePhone], [t2].[EmpGroupID], [t2].[PopulationIdentifier]
FROM [dbo].[User] AS [t0]
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t1].[MAHCPID], [t1].[FirstName], [t1].[LastName], [t1].[Gender], [t1].[Address1], [t1].[Address2], [t1].[City], [t1].[State], [t1].[ZipCode], [t1].[Email], [t1].[EligStart], [t1].[EligEnd], [t1].[Dependent], [t1].[DateOfBirth], [t1].[ID], [t1].[MiddleInit], [t1].[Age], [t1].[SSN], [t1].[County], [t1].[HomePhone], [t1].[EmpGroupID], [t1].[PopulationIdentifier]
    FROM [dbo].[vwMETRemote] AS [t1]
    ) AS [t2] ON ([t0].[MAHCPID] = [t2].[MAHCPID]) AND ([t2].[EligEnd] = @p0)
WHERE ([t0].[LastName] LIKE @p1) AND ([t0].[DeletedDate] IS NULL)
ORDER BY [t0].[LastName], [t0].[FirstName]