How to use left outer join in Linq

John Schultz

I have a SQL statement

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:

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'

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

What am I doing wrong here?

John Schultz

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

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

How to perform left outer join in Linq

How to use IN condition along with LEFT OUTER JOIN and ON

EFCore Linq Left Outer Join

Equivalent of Left outer join in LINQ

How to do a double left outer join in Linq query syntax(or fluent)

How to do a Linq Left Outer Join with a middle table?

how to convert a sql query into linq LEFT OUTER JOIN

How to translate an advanced SQL statement with LEFT OUTER JOIN to LINQ?

How to use constant value in Linq left join?

Implementation difference between left outer join and right outer join in LINQ

Use a filter in an outer join in linq

How to use LEFT OUTER JOIN to identify missing products by vendor?

How to use Joins followed by left outer join in ruby on rails?

Left outer join using LINQ -- understanding the code

Convert SQL (Left Outer Join's) to LINQ

Linq query of left outer join not properly working

Convert SQL (LEFT OUTER JOIN) to LinQ

Linq left outer join with multiple condition

Translate SQL to LINQ using LEFT OUTER JOIN

how left outer join in sqlalchemy?

How to implement left outer join?

LINQ to SQL - Left Outer Join with multiple join conditions

Converting SQL to LINQ (inner join into a left outer join) C#

How do you perform a left outer join using linq extension methods

How to write a LEFT OUTER JOIN where the right side is null in LINQ to SQL?

How can I do Left Outer Join with Linq when updating an object?

Linq using how to use order by and group by with left join?

How to create a JPA query with LEFT OUTER JOIN

How to do left outer join exclusion in pandas