CODEWITHSUNDEEP
c#linqjoinlambda
inquisitive_one
inquisitive_one

Reputation: 1495

How to join 3 tables with lambda expression?

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.

Upvotes: 18

Views: 68155

Answers (4)

Septwaant Software
Septwaant Software

Reputation: 51

For 4 Tables

var query = CurrencyDeposits
    .Join(
        Customers, 
        cd => cd.CustomerId, cus => cus.Id, 
        (cd, cus) => new { CurrencyDeposit = cd, Customer = cus })
    .Join(
        Currencies, 
        x => x.CurrencyDeposit.CurrencyId, cr => cr.Id, 
        (x, cr) => new { x.CurrencyDeposit, x.Customer, Currency =  cr })
    .Join(
        Banks, 
        x => x.CurrencyDeposit.BankId, 
        bn => bn.Id, 
        (x, bn) => new { x.CurrencyDeposit, x.Customer, x.Currency, Bank = bn})
    .Select(s => new {
         s.CurrencyDeposit.Id,
         s.Customer.NameSurname,
         s.Currency.Code,
         s.Bank.BankName,
         s.CurrencyDeposit.RequesCode
     });

Upvotes: 5

Douglas
Douglas

Reputation: 54927

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

Upvotes: 54

Jon Skeet
Jon Skeet

Reputation: 1504092

Okay, I can't see why you'd want to select sector_code when you already know it, but I think you want this:

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've changed it into a query expression as that's a much more readable way of expressing a query like this.
  • Although the "where" clause comes after the join, assuming this is a LINQ to SQL or Entity Framework query, it shouldn't make any difference
  • I've lengthened the range variable names for clarity
  • I've converted your other names into conventional .NET names; you can do this too in your model

Upvotes: 19

user959729
user959729

Reputation: 1187

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 {...});

Upvotes: 2

Related Questions