How to join 3 tables with lambda expression?

Question

I have a simple LINQ lambda join query but I want to add a 3rd join with a where clause. How do I go about doing that?

Here's my single join query:

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 above LINQ query and still maintain the projections:

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

The 3rd join would be made with Sector table & Distribution_sector_industry on sector_code.

Thanks in advance.

Answer 1

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