如何在Linq中使用左外连接

I have a SQL statement

我有一个SQL语句

SELECT 
cci.[ID], cci.[OwnerFirstName], cci.[OwnerLastName], cci.[CompanyName],
cci.[AddressLine1], cci.[AddressLine2], cci.[AddressLine3], cci.[AddressLine4],
cci.[AddressLine5], cci.[AddressCity], sp1.[Abbreviation] AS [StateAbbreviation],
sp2.[Abbreviation] AS [ProvinceAbbreviation], cci.[AddressPostalCode],
cr1.[Name] AS [CountryName], cr2.[Name] AS [RegionName], cci.[AddressNote],
cci.[Phone1], cci.[Phone2], cci.[Phone3], cci.[Phone4], cci.[Phone5],
cci.[Fax1], cci.[Fax2], cci.[Fax3], cci.[Fax4], cci.[Fax5], cci.[Email1],
cci.[Email2], cci.[Email3], cci.[Email4], cci.[Email5], cci.[CompanyWebSite],
ci.[EIN], ci.[SSN], cli.[LegalName], cli.[LegalAddressLine1], cli.[LegalAddressLine2],
cli.[LegalAddressLine3], cli.[LegalAddressLine4], cli.[LegalAddressLine5],
cli.[LegalAddressCity], sp3.[Abbreviation] AS [LegalAddressStateAbbreviation],
sp4.[Abbreviation] AS [LegalAddressProvinceAbbreviation], cli.[LegalAddressPostalCode],
cr3.[Name] AS [LegalAddressCountryName], cr4.[Name] AS [LegalAddressRegionName],
cli.[LegalAddressNote], cri.[FirstMonthFiscalYear], cri.[FirstMonthIncomeTaxYear],
CONCAT(CONCAT(CONCAT(itfl.[TaxForm], ' - ('),itfl.[Description]), ')') AS [TaxForm],
c.[CryptoPassPhrase], c.[CryptoVector], c.[CryptoMinSALTLen],
c.[CryptoMaxSALTLen], c.[CryptoKeySize], c.[CryptoHash], c.[CryptoSALT],
c.[CryptoIterations], ga.[AccountCode], ga.[UseAnalytics],
CONCAT(CONCAT(CONCAT(CONCAT(e1.[LastName], ', '), e1.[FirstName]), ' '), e1.[MiddleName]) AS [CreatedBy],
sd.[CreatedByID], sd.[CreatedDateTime],
CONCAT(CONCAT(CONCAT(CONCAT(e2.[LastName], ', '), e2.[FirstName]), ' '), e2.[MiddleName]) AS [ModifiedBy],
sd.[ModifiedByID], sd.[ModifiedDateTime], sd.[SiteDescription], 
s.[SMTPServer], s.[SMTPUserName], s.[SMTPUserName], s.[SMTPPassword], 
s.[SMTP_TO], s.[SMTP_CC], s.[SMTP_BCC], s.[SMTPEncoding]
FROM [settings].[CompanyContactInformation] cci
LEFT OUTER JOIN [settings].[CompanyIdentification] ci ON cci.[ID] = ci.[SiteID]
LEFT OUTER JOIN [settings].[CompanyLegalInformation] cli ON cci.[ID] = cli.[ID]
LEFT OUTER JOIN [settings].[CompanyReportInformation] cri ON cci.[ID] = cri.[SiteID]
LEFT OUTER JOIN [settings].[Cryptography] c ON cci.[ID] = c.[SiteID]
LEFT OUTER JOIN [settings].[GoogleAnalytics] ga ON cci.[ID] = ga.[SiteID]
LEFT OUTER JOIN [settings].[IncomeTaxFormList] itfl ON cri.[TaxFormID] = itfl.[ID]
LEFT OUTER JOIN [settings].[SiteDetails] sd ON cci.[ID] = sd.[SiteID]
LEFT OUTER JOIN [settings].[SMTP] s ON cci.[ID] = s.[SiteID]
LEFT OUTER JOIN [dbo].[Employee] e1 ON sd.[CreatedByID] = e1.[ID]
LEFT OUTER JOIN [dbo].[Employee] e2 ON sd.[ModifiedByID] = e2.[ID]
LEFT OUTER JOIN [dbo].[CountryOrRegion] cr1 ON cci.[AddressCountryID] = cr1.[ID]
LEFT OUTER JOIN [dbo].[CountryOrRegion] cr2 ON cci.[AddressRegionID] = cr2.[ID]
LEFT OUTER JOIN [dbo].[CountryOrRegion] cr3 ON cli.[LegalAddressCountryID] = cr3.[ID]
LEFT OUTER JOIN [dbo].[CountryOrRegion] cr4 ON cli.[LegalAddressRegionID] = cr4.[ID]
LEFT OUTER JOIN [dbo].[StateOrProvince] sp1 ON cci.[AddressStateID] = sp1.[ID]
LEFT OUTER JOIN [dbo].[StateOrProvince] sp2 ON cci.[AddressProvinceID] = sp2.[ID]
LEFT OUTER JOIN [dbo].[StateOrProvince] sp3 ON cli.[LegalAddressStateID] = sp3.[ID]
LEFT OUTER JOIN [dbo].[StateOrProvince] sp4 ON cli.[LegalAddressProvinceID] = sp4.[ID]

So far I have the following LINQ statement in LinqPad:

到目前为止,我在LinqPad中有以下LINQ语句:

var q = (
    from cci in CompanyContactInformation
    join sp1 in StateOrProvince on cci.AddressStateID equals sp1.ID into t1
    from rt1 in t.DefaultIfEmpty()
    select new {
        cci.ID, cci.OwnerFirstName, cci.OwnerLastName, cci.CompanyName,
        cci.AddressLine1, cci.AddressLine2,cci.AddressLine3,
        cci.AddressLine4, cci.AddressLine5, cci.AddressCity,
        StateAbbreviation = sp1.Abbreviation
    }).ToList();
q.Dump();

And I am getting the following error:

我收到以下错误:

'LINQPad.User.StateOrProvince' is a 'type' but is used like a 'variable'

'LINQPad.User.StateOrProvince'是'type',但用作'变量'

The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'GroupJoin'.

join子句中某个表达式的类型不正确。在对“GroupJoin”的调用中类型推断失败。

What am I doing wrong here?

我在这做错了什么?

1 个解决方案

#1

Solved it after pulling my hair out. Had to pluralize some of the tables. Here is the proper LINQ statement:

把头发拉出后解决了。不得不复数一些表。这是正确的LINQ语句:

var q = ( from cci in CompanyContactInformation join ci in CompanyIdentifications on cci.ID equals ci.SiteID into t1 from rt1 in t1.DefaultIfEmpty() join cli in CompanyLegalInformation on cci.ID equals cli.ID into t2 from rt2 in t2.DefaultIfEmpty() join cri in CompanyReportInformation on cci.ID equals cri.SiteID into t3 from rt3 in t3.DefaultIfEmpty() join c in Cryptographies on cci.ID equals c.SiteID into t4 from rt4 in t4.DefaultIfEmpty() join ga in GoogleAnalytics on cci.ID equals ga.SiteID into t5 from rt5 in t5.DefaultIfEmpty() join itfl in IncomeTaxFormLists on rt3.TaxFormID equals itfl.ID into t6 from rt6 in t6.DefaultIfEmpty() join sd in SiteDetails on cci.ID equals sd.SiteID into t7 from rt7 in t7.DefaultIfEmpty() join s in SMTPs on cci.ID equals s.SiteID into t8 from rt8 in t8.DefaultIfEmpty() join e1 in Employees on rt7.CreatedByID equals e1.ID into t9 from rt9 in t9.DefaultIfEmpty() join e2 in Employees on rt7.ModifiedByID equals e2.ID into t10 from rt10 in t10.DefaultIfEmpty() join cr1 in CountryOrRegions on cci.AddressCountryID equals cr1.ID into t11 from rt11 in t11.DefaultIfEmpty() join cr2 in CountryOrRegions on cci.AddressRegionID equals cr2.ID into t12 from rt12 in t12.DefaultIfEmpty() join cr3 in CountryOrRegions on rt2.LegalAddressCountryID equals cr3.ID into t13 from rt13 in t13.DefaultIfEmpty() join cr4 in CountryOrRegions on rt2.LegalAddressRegionID equals cr4.ID into t14 from rt14 in t14.DefaultIfEmpty() join sp1 in StateOrProvinces on cci.AddressStateID equals sp1.ID into t15 from rt15 in t15.DefaultIfEmpty() join sp2 in StateOrProvinces on cci.AddressProvinceID equals sp2.ID into t16 from rt16 in t16.DefaultIfEmpty() join sp3 in StateOrProvinces on rt2.LegalAddressStateID equals sp3.ID into t17 from rt17 in t17.DefaultIfEmpty() join sp4 in StateOrProvinces on rt2.LegalAddressProvinceID equals sp4.ID into t18 from rt18 in t18.DefaultIfEmpty() select new { cci.ID, cci.OwnerFirstName, cci.OwnerLastName, cci.CompanyName, cci.AddressLine1, cci.AddressLine2, cci.AddressLine3, cci.AddressLine4, cci.AddressLine5, cci.AddressCity, StateAbbreviation = rt15.Abbreviation, ProvinceAbbreviation = rt16.Abbreviation, cci.AddressPostalCode, CountryName = rt11.Name, RegionName = rt12.Name, cci.AddressNote, cci.Phone1, cci.Phone2, cci.Phone3, cci.Phone4, cci.Phone5, cci.Fax1, cci.Fax2, cci.Fax3, cci.Fax4, cci.Fax5, cci.Email1, cci.Email2, cci.Email3, cci.Email4, cci.Email5, cci.CompanyWebSite, rt1.EIN, rt1.SSN, rt2.LegalName, rt2.LegalAddressLine1, rt2.LegalAddressLine2, rt2.LegalAddressLine3, rt2.LegalAddressLine4, rt2.LegalAddressLine5, rt2.LegalAddressCity, LegalAddressStateAbbreviation = rt17.Abbreviation, LegalAddressProvinceAbbreviation = rt18.Abbreviation, rt2.LegalAddressPostalCode, LegalAddressCountryName = rt13.Name, LegalAddressRegionName = rt14.Name, rt2.LegalAddressNote, rt3.FirstMonthFiscalYear, rt3.FirstMonthIncomeTaxYear, TaxForm = rt6.TaxForm + " - (" + rt6.Description + ")", rt4.CryptoPassPhrase, rt4.CryptoVector, rt4.CryptoMinSALTLen, rt4.CryptoMaxSALTLen, rt4.CryptoKeySize, rt4.CryptoHash, rt4.CryptoSALT, rt4.CryptoIterations, rt5.AccountCode, rt5.UseAnalytics, CreatedBy = rt9.LastName + ", " + rt9.FirstName + " " + rt9.MiddleName, rt7.CreatedByID, rt7.CreatedDateTime, ModifiedBy = rt10.LastName + ", " + rt10.FirstName + " " + rt10.MiddleName, rt7.ModifiedByID, rt7.ModifiedDateTime, rt7.SiteDescription, rt8.SMTPServer, rt8.SMTPUserName, rt8.SMTPPassword, rt8.SMTP_TO, rt8.SMTP_CC, rt8.SMTP_BCC, rt8.SMTPEncoding }).ToList(); q.Dump();

var q =(来自CompanyContactInformation中的cci,cci.ID中的CompanyIdentifications中的ci等于c1.SiteID,来自t1中的rt1的t1 .DefaultIfEmpty()cci.ID中的CompanyLegalInformation中的cli等于来自t2.DefaultIfEmpty中的rt2的t2的cli.ID ()在cci.ID中将CompanyReportInformation中的cri连接到来自trt中rt3的t3中的cri.SiteID.defaultIfEmpty()cci.ID中的密码中的连接c等于c.SiteID到t4中rt4的t4中.DefaultIfEmpty()在GoogleAnalytics中加入ga在cci.ID等于ga.SiteID从t5中的rt5到t5 .DefaultIfEmpty()在rt3上的IncomeTaxFormLists中加入itfl.TaxFormID将itfl.ID加到来自t6中的rt6的t6中.defaultIfEmpty()在cci.ID上的SiteDetails中加入sd等于sd .titeID从t7中的rt7到t7.defaultIfEmpty()在cci.s上的SMTP中加入s将s.SiteID加到来自t8中的rt8的t8中.DefaultIfEmpty()在rt7.CreatedByID上的Employees中加入e1将e1.ID加到来自rt9的t9中在t9.DefaultIfEmpty()中,在rt7.ModifiedByID中的员工e2中将e2.ID等于t10中的rt10的t10 .DefaultIfEmp ty()将cr1在国家/地区中加入cci.AddressCountryID等于cr1.ID到t11中的rt11中的t11.DefaultIfEmpty()将c0.InterRegionID上的CountryOrRegions中的cr2连接到来自t12的rt12的t12中的cr2.ID.defaultIfEmpty()连接cr3 in rt2.LegalAddressCountryID上的CountryOrRegions等于t13中的rt13中的cr3.ID.DefaultIfEmpty()将rt2.LegalAddressRegionID上的CountryOrRegions中的cr4连接到t14中的rt14中的cr4.ID等于t14.DefaultIfEmpty()在cci.AddressStateID上的StateOrProvinces中连接sp1 sp1.ID从t15中的rt15到t15.DefaultIfEmpty()连接c2上的StateOrProvinces中的sp2.AddressProvinceID将sp2.ID连接到t16中的rt16的t16.DefaultIfEmpty()连接rt2.LegalAddressStateID上的StateOrProvinces中的sp3等于来自t17的sp3.ID t17中的rt17.DefaultIfEmpty()在rt2.LegalAddressProvinceID上的StateOrProvinces中加入sp4,在t18中将sp4.ID等于t18中的rt18.DefaultIfEmpty()选择new {cci.ID,cci.OwnerFirstName,cci.OwnerLastName,cci.CompanyName,cci。 AddressLine1,cci。 AddressLine2,cci.AddressLine3,cci.AddressLine4,cci.AddressLine5,cci.AddressCity,StateAbbreviation = rt15.Abbreviation,ProvinceAbbreviation = rt16.Abbreviation,cci.AddressPostalCode,CountryName = rt11.Name,RegionName = rt12.Name,cci.AddressNote, cci.Phone1,cci.Phone2,cci.Phone3,cci.Phone4,cci.Phone5,cci.Fax1,cci.Fax2,cci.Fax3,cci.Fax4,cci.Fax5,cci.Email1,cci.Email2,cci。 Email3,cci.Email4,cci.Email5,cci.CompanyWebSite,rt1.EIN,rt1.SSN,rt2.LegalName,rt2.LegalAddressLine1,rt2.LegalAddressLine2,rt2.LegalAddressLine3,rt2.LegalAddressLine4,rt2.LegalAddressLine5,rt2.LegalAddressCity, LegalAddressStateAbbreviation = rt17.Abbreviation,LegalAddressProvinceAbbreviation = rt18.Abbreviation,rt2.LegalAddressPostalCode,LegalAddressCountryName = rt13.Name,LegalAddressRegionName = rt14.Name,rt2.LegalAddressNote,rt3.FirstMonthFiscalYear,rt3.FirstMonthIncomeTaxYear,TaxForm = rt6.TaxForm +“ - (” + rt6.Description +“)”,rt4.CryptoPassPhrase,rt4.CryptoVector,rt4.Cr yptoMinSALTLen,rt4.CryptoMaxSALTLen,rt4.CryptoKeySize,rt4.CryptoHash,rt4.CryptoSALT,rt4.CryptoIterations,rt5.AccountCode,rt5.UseAnalytics,CreatedBy = rt9.LastName +“,”+ rt9.FirstName +“”+ rt9.MiddleName ,rt7.CreatedByID,rt7.CreatedDateTime,ModifiedBy = rt10.LastName +“,”+ rt10.FirstName +“”+ rt10.MiddleName,rt7.ModifiedByID,rt7.ModifiedDateTime,rt7.SiteDescription,rt8.SMTPServer,rt8.SMTPUserName, rt8.SMTPPassword,rt8.SMTP_TO,rt8.SMTP_CC,rt8.SMTP_BCC,rt8.SMTPEncoding})。ToList(); q.Dump();