LINQ查询和LEFT JOIN条件上的子查询 [英] LINQ query with sub-query on LEFT JOIN conditions

问题描述

我在SQL和LINQ中拥有这些查询，这些查询旨在检索相同的数据.不幸的是，他们正在检索不同数量的记录(LINQ返回1555个值，SQL返回1969年)，我不知道为什么.

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: (几句话后编辑)

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
                         });

分析LINQ语句生成的运行时SQL查询后，我发现它在错误的位置创建了Authorized子查询.这是它的样子:

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.

推荐答案

经过一番研究，我终于找到了解决方法.这是生成我试图获取的SQL的LINQ查询:

after some research I finally found how to do it. Here is the LINQ query that generates the SQL I was trying to get:

var leaseList = (from l in leases.tblfLeaseDetails
                             join p in leases.tblfPayments
                             on l.Lease_Detail_ID equals p.Lease_Detail_ID into lp
                             from jlp in lp.Where(x => x.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()).DefaultIfEmpty()
                             join a in leases.tblfAuthorizations on l.Lease_Detail_ID equals a.Lease_Detail_ID into la
                             from jla in la.Where(x => x.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()).DefaultIfEmpty()                            
                             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()
                             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
                             });

这篇关于LINQ查询和LEFT JOIN条件上的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！