How do I convert multiple inner joins in SQL to LINQ?

Question

I've got the basics of LINQ-to-SQL down, but I've been struggling trying to get JOINs to work properly. I'd like to know how to convert the following to LINQ-to-SQL (ideally using method chaining, as that is my preferred format).

SELECT      c.CompanyId, c.CompanyName,             p.FirstName + ' ' + p.LastName as AccountCoordinator,             p2.FirstName + ' ' + p2.LastName as AccountManager FROM        dbo.Companies c INNER JOIN  dbo.Persons p ON          c.AccountCoordinatorPersonId = p.PersonId INNER JOIN  dbo.Persons p2 ON          c.AccountManagerPersonId = p2.PersonId 

Answer 1

Using query syntax:

from c in dbo.Companies join p in dbo.Persons on c.AccountCoordinatorPersonId equals p.PersonId join p2 in dbo.Persons on c.AccountManagerPersonId equals p2.PersonId select new {     c.CompanyId,     c.CompanyName,     AccountCoordinator = p.FirstName + ' ' + p.Surname,     AccountManager = p2.FirstName + ' ' + p2.Surname } 

Using method chaining:

dbo.Companies.Join(dbo.Persons,                     c => c.AccountCoordinatorPersonId,                      p => p.PersonId,                      (c, p) => new                     {                          Company = c,                          AccountCoordinator = p.FirstName + ' ' + p.Surname                      })              .Join(dbo.Persons,                      c => c.Company.AccountManagerPersonId,                      p2 => p2.PersonId,                      (c, p2) => new                     {                          c.Company.CompanyId,                          c.Company.CompanyName,                          c.AccountCoordinator,                          AccountManager = p2.FirstName + ' ' + p2.Surname                     });