林克查询更正

Question

我想知道Linq和SQL是否相等（意味着Linq将返回与SQL相同的结果集？我在表中没有数据，我需要将 SQL 转换为 LINQ。请建议

 var excludeTypes = new[]
               {
                  "CA00", "CA01", "CA03", "CA04", "CA02",
                  "PA00", "PA01", "PA02", "PA03", "PA04"     
               };
 var accounts =
                from account in context.Accounts
                from owner in context.AccountOwners
                from business in context.Businesses
                from accountStatus in context.AccountStatuses
                from legalStatus in context.LegalStatuses
                where !excludeTypes.Contains(account.AccountType)
                select new AccountsReport { Account = account };

 ALTER VIEW [dbo].[vwRptBorrowerAccount]  
AS  
SELECT dbo.tblAccount.[Creditor Registry ID], dbo.tblAccount.[Account No], dbo.tblAccount.[Date Opened], dbo.tblAccount.[Account Status ID],   
               dbo.tblAccount.[Date First Reported], dbo.tblAccount.[Credit Limit], dbo.tblAccount.Balance, dbo.tblAccount.[Minimum Installment], dbo.tblAccount.[Account Type],   
               dbo.tblAccount.Term, dbo.tblAccount.Purpose, dbo.tblAccount.[Account Owner Notes], dbo.tblAccount.[Creditor Notes], dbo.tblAccount.Collateral,   
               dbo.tblAccount.[Collateral Value], dbo.tblAccount.[Legal Status ID], dbo.tblAccount.[Legal Status Date], dbo.tblAccount.LastUpdatedBy,   
               dbo.tblAccount.LastUpdated, dbo.tblAccount.[Unique ID], dbo.tblAccount.[Account Status Date], dbo.tblAccount.Payment, dbo.tblAccount.[Payment Date],   
               dbo.tblAccount.[Balance Date], dbo.tblAccount.[Term Frequency], dbo.tblAccount.[State Change Date],   
               dbo.fn_GetAccountTypeDescription(dbo.tblAccount.[Account Type]) AS [Account Type Description], dbo.tblBusiness.[Business Name] AS CreditorName,   
               dbo.tblBusiness.Address AS CreditorAddress, dbo.tblBusiness.City AS CreditorCity, dbo.tblBusiness.State AS CreditorState,   
               dbo.tblLegalStatus.[Legal Status Description] AS [Legal Status], dbo.tblAccountStatus.[Account Status Description] AS [Account Status],   
               dbo.tblAccountOwner.[Account Owner Registry ID]  
FROM  dbo.tblAccount INNER JOIN  
               dbo.tblAccountOwner ON dbo.tblAccount.[Creditor Registry ID] = dbo.tblAccountOwner.[Creditor Registry ID] AND   
               dbo.tblAccount.[Account No] = dbo.tblAccountOwner.[Account No] INNER JOIN  
               dbo.tblBusiness ON dbo.tblAccount.[Creditor Registry ID] = dbo.tblBusiness.[Registry ID] INNER JOIN  
               dbo.tblAccountStatus ON dbo.tblAccount.[Account Status ID] = dbo.tblAccountStatus.[Account Status ID] INNER JOIN  
               dbo.tblLegalStatus ON dbo.tblAccount.[Legal Status ID] = dbo.tblLegalStatus.[Legal Status ID]  
WHERE (dbo.tblAccount.[Account Type] NOT IN ('CA00', 'CA01', 'CA03', 'CA04', 'CA02', 'PA00', 'PA01', 'PA02', 'PA03', 'PA04'))  

Answer 1

不，你的 linq 不等同于你的 sql。 您缺少表之间的关系。

---

第二个和第三个"来自"被翻译成对System.Linq.Queryable.SelectMany的呼唤。 由于没有指定关系，因此此查询将每个帐户与每个帐户所有者匹配，然后将每个结果对与每个业务匹配。 这称为笛卡尔连接（所有可能的匹配）。

from account in context.Accounts
from owner in context.AccountOwners
from business in context.Businesses

更传统的方法是在查询中指定关系。 此查询将每个帐户与其帐户所有者匹配，然后每个帐户都与其业务匹配。这称为内联接。 （注意，必须使用关键字equals。 另请注意严格的范围规则 on (leftside) equals (rightside) ）。

from account in context.Accounts
join owner in context.AccountOwners
  on new {account.RegistryId, account.AccountNo}
  equals new {owner.RegistryId, owner.AccountNo}
join business in context.Businesses
  on account.CreditorRegistryID
  equals business.RegistryID

第二个和第三个"来自"被翻译成对System.Linq.Queryable.SelectMany的呼吁。 由于指定了关系，因此此查询将每个帐户与其帐户所有者及其业务进行匹配。 这是一个内部联接（帐户 = 1，其他 = 多）。

from account in context.Accounts
from owner in account.AccountOwners
from business in account.Businesses

此查询将每个帐户与其唯一的帐户所有者和唯一的业务进行匹配。这也是一个内部联接（帐户 = 许多，其他 = 1）。

from account in context.Accounts
let owner = account.AccountOwner
let business = account.Business