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.
Please help me to find out what I'm missing. Follows the queries:
SQL:
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
LINQ: (Edited after a few comments)
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
});
EDIT:
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:
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] )))
The problem is that it only made more confuse, once Payment and Authorized joins have exactly the same structure.
