title: LINQ query with sub-query on LEFT JOIN conditions
body: <p>I have these queries in SQL and LINQ that were built to retrieve the same data. Unfortunately they are retrieving different amount of records (LINQ returns 1555 values, and SQL returns 1969) and I can't figure out why.</p>
  
  <p>Please help me to find out what I'm missing. Follows the queries:</p>
  
  <p><strong>SQL:</strong> </p>
  
  <pre><code>SELECT l.Lease_Detail_ID, l.Lease_ID, l.XRef_Lease_ID, v.Vendor_Name, l.Description, c.County, l.Amount, l.Payment_Due_Date,
      l.Lease_Type, l.Location_ID, l.Active, l.Expiration_Date, a.Authorized, p.Payment_Date
      FROM tblfLeaseDetail AS l
      LEFT JOIN tblvVendor AS v ON l.Vendor_ID = v.Vendor_ID
      LEFT JOIN tblvCounty AS c ON l.County_ID = c.County_ID
      LEFT JOIN tblfAuthorization AS a ON l.Lease_Detail_ID = a.Lease_Detail_ID
      AND a.Authorization_ID = (SELECT TOP 1 Authorization_ID
                              FROM tblfAuthorization
                              WHERE Lease_Detail_ID = l.Lease_Detail_ID
                              ORDER BY Authorized_Date)
      LEFT JOIN tblfPayment AS p ON l.Lease_Detail_ID = p.Lease_Detail_ID
      AND p.Payment_ID = (SELECT TOP 1 Payment_ID
                          FROM tblfPayment
                          WHERE Lease_Detail_ID = l.Lease_Detail_ID
                          ORDER BY payment_date)
      ORDER BY l.Lease_Detail_ID
  </code></pre>
  
  <p><strong>LINQ:</strong> <em>(Edited after a few comments)</em></p>
  
  <pre><code>var leaseList = (from l in leases.tblfLeaseDetails
                           join v in leases.tblvVendors on l.Vendor_ID equals v.Vendor_ID into lv
                           from jlv in lv.DefaultIfEmpty()
                           join c in leases.tblvCounties on l.County_ID equals c.County_ID into lc
                           from jlc in lc.DefaultIfEmpty()
                           join a in leases.tblfAuthorizations on l.Lease_Detail_ID equals a.Lease_Detail_ID into la
                           from jla in la.DefaultIfEmpty()
                           where jla.Authorization_ID == (from aj in leases.tblfAuthorizations
                                                          where aj.Lease_Detail_ID == l.Lease_Detail_ID
                                                          orderby aj.Authorized_Date ascending
                                                          select aj.Authorization_ID).FirstOrDefault()
                           join p in leases.tblfPayments on l.Lease_Detail_ID equals p.Lease_Detail_ID into lp
                           from jlp in lp.DefaultIfEmpty()
                           where jlp.Payment_ID == (from pj in leases.tblfPayments
                                                    where pj.Lease_Detail_ID == l.Lease_Detail_ID
                                                    orderby pj.Payment_Date ascending
                                                    select pj.Payment_ID).FirstOrDefault()
                           select new LeaseViewModel()
                           {
                               Lease_Detail_ID = l.Lease_Detail_ID,
                               Lease_ID = l.Lease_ID,
                               XRef_Lease_ID = l.XRef_Lease_ID,
                               Vendor_Name = jlv.Vendor_Name,
                               Description = l.Description,
                               County = jlc.County,
                               Amount = l.Amount,
                               Payment_Due_Date = l.Payment_Due_Date,
                               Lease_Type = l.Lease_Type.ToString(),
                               Location_ID = l.Location_ID,
                               Active = l.Active,
                               Expiration_Date = l.Expiration_Date,
                               Authorized = jla.Authorized,
                               Payment_Date = jlp.Payment_Date
                           });
  </code></pre>
  
  <p><strong>EDIT:</strong></p>
  
  <p>After analyzing the run-time SQL query generated by LINQ statement I found out that it's creating the Authorized sub-query in the wrong place. Here is what it looks like:</p>
  
  <pre><code>SELECT [t0].[Lease_Detail_ID], [t0].[Lease_ID], [t0].[XRef_Lease_ID], [t1].[Vendor_Name] AS [Vendor_Name], [t0].[Description], [t2].[County] AS [County], [t0].[Amount], [t0].[Payment_Due_Date], [t0].[Expiration_Date], [t3].[Authorized] AS [Authorized], CONVERT(NVarChar(1),[t0].[Lease_Type]) AS [Lease_Type], [t0].[Location_ID], CONVERT(Int,[t0].[Active]) AS [Active], [t4].[Payment_Date] AS [Payment_Date]
  FROM [dbo].[tblfLeaseDetail] AS [t0]
  LEFT OUTER JOIN [dbo].[tblvVendor] AS [t1] ON [t0].[Vendor_ID] = ([t1].[Vendor_ID])
  LEFT OUTER JOIN [dbo].[tblvCounty] AS [t2] ON [t0].[County_ID] = ([t2].[County_ID])
  LEFT OUTER JOIN [dbo].[tblfAuthorization] AS [t3] ON ([t0].[Lease_Detail_ID]) = [t3].[Lease_Detail_ID]
  LEFT OUTER JOIN [dbo].[tblfPayment] AS [t4] ON ([t0].[Lease_Detail_ID]) = [t4].[Lease_Detail_ID]
  WHERE ([t4].[Payment_ID] = ((SELECT TOP (1) [t5].[Payment_ID] FROM [dbo].[tblfPayment] AS [t5] WHERE [t5].[Lease_Detail_ID] = ([t0].[Lease_Detail_ID]) 
                              ORDER BY [t5].[Payment_Date] ))) 
                              AND ([t3].[Authorization_ID] = (( SELECT TOP (1) [t6].[Authorization_ID] 
                                                                  FROM [dbo].[tblfAuthorization] AS [t6] 
                                                                  WHERE [t6].[Lease_Detail_ID] = ([t0].[Lease_Detail_ID]) 
  ORDER BY [t6].[Authorized_Date] )))
  </code></pre>
  
  <p>The problem is that it only made more confuse, once Payment and Authorized joins have exactly the same structure.</p>