LEFT OUTER JOIN in Linq - How to Force

Question

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]

Answer 1

I'm not sure if it will change the result set with "AND ([t2].[EligEnd] = @p0" as part of the subquery rather than the join condition. One thing I like to do with complex queries might help you here. I like to break them into smaller queries before combining them. The deferred execution of LINQ lets us do multiple statements with one eventual call to the database. Something like this:

var elig = from mri in vwMETRemotes 
           where met.EligEnd == Convert.ToDateTime("2009-10-31") 
           select mri;

var users = from l in Users
            where l.LastName.StartsWith("smith")
            where l.DeletedDate == null

var result = from l in users
             join mr in elig on l.Mahcpid equals mr.Mahcpid into lo
             from g in lo.DefaultIfEmpty()
             orderby l.LastName, l.FirstName
             select g

Breaking it down like that can make it easier to debug, and perhaps it can tell LINQ better what you intend.

Answer 2

Code ended up looking like this. RecodePopulation and RecordRegistration are just methods to translate values from the query.

            var elig = from mri in db.MetRemote 
                    where mri.EligEnd == Convert.ToDateTime(ConfigurationManager.AppSettings["EligibilityDate"]) 
                   orderby mri.EligEnd 
                   select mri;

        var users = from l in db.Users
                    where l.LastName.StartsWith(filter)
                    where l.DeletedDate == null
                        select l;

        var results = (from l in users
                     join m in elig on l.MahcpId equals m.MAHCPID into lo
                     from g in lo.DefaultIfEmpty()
                     orderby l.LastName, l.FirstName
                       select new UserManage()
                       {
                           Username = l.Username,
                           FirstName = l.FirstName,
                           LastName = l.LastName,
                           DateOfBirth = l.DOB,
                           Gender = l.Gender,
                           Status = RecodePopulation(g.Population, l.CreatedDate),
                           UserId = l.Id,
                           WellAwardsRegistered = RecodeRegistration(l.Id, 1)

                       }).Distinct().OrderBy(a => a.LastName).ThenBy(n => n.FirstName).Skip((currentPage - 1) * resultsPerPage).Take(resultsPerPage);