LINQ query with many tables, left outer joins, and where clause <p>I need to translate this SQL query into LINQ in c#. I would appreciate any help or guidance you can give. (I'm using entity framework 6).</p> <pre><code>SELECT f.FacId ,sli.SitLocIntId ,ei.EnvIntId ,p.PhoId FROM Fac AS f join SitLocInt AS sli on f.FacId = sli.FacId join EnvInt AS ei on sli.EnvIntId = ei.EnvIntId join EnvIntTyp AS eit on ei.EnvIntTypId = eit.EnvIntTypId left outer join Aff AS a on ei.EnvIntId = a.EnvIntId left outer join AffCon AS ac on a.AffId = ac.AffId left outer join Con AS c on ac.ConId = c.ConId left outer join Pho AS p on c.ConId = p.ConId WHERE EnvIntTyp = 'Fleet' </code></pre> <p>I've been looking at group join syntax, but with so many tables, a lot of left outer joins and a where clause I haven't made much headway. So far I have</p> <pre><code> var testQuery = from f in _CEMDbContext.setFac join sli in _CEMDbContext.setSitLocInt on f.FacId equals sli.FacId join ei in _CEMDbContext.setEnvInt on sli.EnvIntId equals ei.EnvIntId join eit in _CEMDbContext.setEnvIntTyp on ei.EnvIntTypId equals eit.EnvIntTypId into eiGroup from item in eiGroup.DefaultIfEmpty().Where(e =&gt; e.EnvIntTyp == "Fleet") select new BusinessParticipant { fac = f, sitLocInt = sli, envInt = ei, // pho = p, // not working... Phone number from table Pho. }; var TestList = testQuery.ToList(); </code></pre> <p>Getting the EnvIntTyp "Fleet" and all its associated data including Fac, SitLocInt, and EnvInt is working. But I run into trouble when I try to get the phone number from table Pho which may or may not exist. Is there a nice way to chain together all the left outer joins? Thanks for advice or direction!</p> <p><b>Edit</b> I don't just need the Ids as I wrote in the SQL query, but the whole object. Plus, I should mention that EnvIntTyp is a member of the table of the same name.</p> <p><b>Edit 2</b> Here are (parts of) the relevant entities. Fac is an abbreviation for Facility. SitLocInt is an abbreviation for SiteLocationInterest. EnvInt stands for EnvironmentalInterest. Aff is for Affiliation. Con is for Contact. Pho is for Phone.</p> <pre><code>public partial class Facility { public Facility() { this.SiteLocationInterests = new List&lt;SiteLocationInterest&gt;(); } public int FacilityId { get; set; } public string FacilityIdentifier { get; set; } public string FacilityName { get; set; } public int SiteTypeId { get; set; } public string FacilityDescription { get; set; } [ForeignKey("GeographicFeature")] public Nullable&lt;int&gt; GeographicFeatureId { get; set; } [Display(Name = "resAddress", ResourceType = typeof(CEMResource))] public string AddressLine1 { get; set; } [Display(Name = "resAddressLine2", ResourceType = typeof(CEMResource))] public string AddressLine2 { get; set; } public string City { get; set; } [Display(Name = "resState", ResourceType = typeof(CEMResource))] public string StateCode { get; set; } [Display(Name = "resZip", ResourceType = typeof(CEMResource))] public string AddressPostalCode { get; set; } public string CountyName { get; set; } public virtual GeographicFeature GeographicFeature { get; set; } public virtual ICollection&lt;SiteLocationInterest&gt; SiteLocationInterests { get; set; } } public partial class SiteLocationInterest { public int SiteLocationInterestId { get; set; } [ForeignKey("Facility")] public Nullable&lt;int&gt; FacilityId { get; set; } [ForeignKey("EnvironmentalInterest")] public Nullable&lt;int&gt; EnvironmentalInterestId { get; set; } public Nullable&lt;int&gt; EventId { get; set; } [ForeignKey("GeographicFeature")] public Nullable&lt;int&gt; GeographicFeatureId { get; set; } public System.DateTime CreateDate { get; set; } public string CreateBy { get; set; } public System.DateTime LastUpdateDate { get; set; } public string LastUpdateBy { get; set; } public virtual EnvironmentalInterest EnvironmentalInterest { get; set; } public virtual Facility Facility { get; set; } public virtual GeographicFeature GeographicFeature { get; set; } } public partial class EnvironmentalInterest { public EnvironmentalInterest() { this.Affiliations = new List&lt;Affiliation&gt;(); this.SiteLocationInterests = new List&lt;SiteLocationInterest&gt;(); } public int EnvironmentalInterestId { get; set; } public string EnvironmentalInterestIdentifier { get; set; } public string EnvironmentalInterestFacilityIdentifier { get; set; } public string FacilityIdentifier { get; set; } public string EnvironmentalInterestName { get; set; } [ForeignKey("EnvironmentalInterestType")] public int EnvironmentalInterestTypeId { get; set; } public Nullable&lt;int&gt; EnvironmentalInterestSubTypeId { get; set; } public string EnvironmentalInterestDescription { get; set; } public virtual ICollection&lt;Affiliation&gt; Affiliations { get; set; } public virtual EnvironmentalInterestType EnvironmentalInterestType { get; set; } public virtual ICollection&lt;SiteLocationInterest&gt; SiteLocationInterests { get; set; } } public partial class Affiliation { public Affiliation() { this.AffiliationContacts = new List&lt;AffiliationContact&gt;(); } public int AffiliationId { get; set; } public string AffiliationIdentifier { get; set; } public string AffiliationName { get; set; } [ForeignKey("Entity")] public Nullable&lt;int&gt; EntityId { get; set; } [ForeignKey("EnvironmentalInterest")] public Nullable&lt;int&gt; EnvironmentalInterestId { get; set; } public int AffiliationTypeId { get; set; } public int StatusTypeId { get; set; } public virtual EnvironmentalInterest EnvironmentalInterest { get; set; } public virtual ICollection&lt;AffiliationContact&gt; AffiliationContacts { get; set; } } public partial class AffiliationContact { public int AffiliationContactId { get; set; } public int AffiliationId { get; set; } public int ContactId { get; set; } public virtual Affiliation Affiliation { get; set; } public virtual Contact Contact { get; set; } } public partial class Contact { public Contact() { this.AffiliationContacts = new List&lt;AffiliationContact&gt;(); this.Phones = new List&lt;Phone&gt;(); } public int ContactId { get; set; } public string ContactIdentifier { get; set; } public int EntityId { get; set; } public Nullable&lt;int&gt; MailAddressId { get; set; } public int ContactTypeId { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public string EmailAddress { get; set; } public virtual ICollection&lt;AffiliationContact&gt; AffiliationContacts { get; set; } public virtual Entity Entity { get; set; } public virtual ICollection&lt;Phone&gt; Phones { get; set; } } public partial class Phone { public int PhoneId { get; set; } public int ContactId { get; set; } public int ContactTypeId { get; set; } public string PhoneNumber { get; set; } public string PhoneExtensionNumber { get; set; } public virtual Contact Contact { get; set; } public virtual ContactType ContactType { get; set; } } </code></pre>