How to join 3 tables with lambda expression?

Question

I have a simple LINQ lambda join query, but I want to add a third connection with the where clause. How can I do it?

Here is my only connection request:

var myList = Companies .Join( Sectors, comp => comp.Sector_code, sect => sect.Sector_code, (comp, sect) => new {Company = comp, Sector = sect} ) .Select( c => new { c.Company.Equity_cusip, c.Company.Company_name, c.Company.Primary_exchange, c.Company.Sector_code, c.Sector.Description }); 

I want to add the following SQL command to the LINQ query above and still support the predictions:

 SELECT sector_code, industry_code FROM distribution_sector_industry WHERE service = 'numerical' 

The third connection will be made using the sector table and Distribution_sector_industry by sector_code.

Thanks in advance.

Answer 1

Just to guess:

 var myList = Companies .Join( Sectors, comp => comp.Sector_code, sect => sect.Sector_code, (comp, sect) => new { Company = comp, Sector = sect }) .Join( DistributionSectorIndustry.Where(dsi => dsi.Service == "numerical"), cs => cs.Sector.Sector_code, dsi => dsi.Sector_code, (cs, dsi) => new { cs.Company, cs.Sector, IndustryCode = dsi.Industry_code }) .Select(c => new { c.Company.Equity_cusip, c.Company.Company_name, c.Company.Primary_exchange, c.Company.Sector_code, c.Sector.Description, c.IndustryCode }); 

Answer 2

Well, I don’t understand why you want to select sector_code when you already know this, but I think you want it:

 var query = from company in Companies join sector in Sectors on company.SectorCode equals sector.SectorCode join industry in DistributionSectorIndustry on sector.SectorCode equals industry.SectorCode where industry.Service == "numerical" select new { company.EquityCusip, company.CompanyName, company.PrimaryExchange, company.SectorCode, sector.Description, industry.IndustryCode }; 

Notes:

• I changed it to a query expression as a more readable way to express such a query.
• Although the where clause appears after the join, assuming it is a LINQ to SQL or Entity Framework query, it should not have any meaning.
• Clarify range variable names for clarity
• I converted your other names to regular .NET names; You can do this too in your model.

Answer 3

Try something like this ...

 var myList = ({from a in Companies join b in Sectors on a.Sector_code equals b.Sector_code join c in Distribution on b.distribution_code equals a.distribution_code select new {...});