StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POLEFT OUTER JOIN in Linq - How to Force
text
Body
copied!<p>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.</p> <p>Thanks in advance (samples pulled from LINQPad) - Doug</p> <pre><code>(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) </code></pre> <p>Here is the resulting SQL</p> <pre><code>-- 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] </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload