LINQ query with many tables, left outer joins, and where clause

Question

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).

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'

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

    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 => 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();

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!

Edit 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.

Edit 2 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.

public partial class Facility
{
    public Facility()
    {
        this.SiteLocationInterests = new List<SiteLocationInterest>();
    }

    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<int> 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<SiteLocationInterest> SiteLocationInterests { get; set; }
}

public partial class SiteLocationInterest
{
    public int SiteLocationInterestId { get; set; }
    [ForeignKey("Facility")]
    public Nullable<int> FacilityId { get; set; }
    [ForeignKey("EnvironmentalInterest")]
    public Nullable<int> EnvironmentalInterestId { get; set; }
    public Nullable<int> EventId { get; set; }
    [ForeignKey("GeographicFeature")]
    public Nullable<int> 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<Affiliation>();
        this.SiteLocationInterests = new List<SiteLocationInterest>();
    }

    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<int> EnvironmentalInterestSubTypeId { get; set; }
    public string EnvironmentalInterestDescription { get; set; }
    public virtual ICollection<Affiliation> Affiliations { get; set; }
    public virtual EnvironmentalInterestType EnvironmentalInterestType { get; set; }
    public virtual ICollection<SiteLocationInterest> SiteLocationInterests { get; set; }
}

public partial class Affiliation
{
    public Affiliation()
    {
        this.AffiliationContacts = new List<AffiliationContact>();
    }

    public int AffiliationId { get; set; }
    public string AffiliationIdentifier { get; set; }
    public string AffiliationName { get; set; }
    [ForeignKey("Entity")]
    public Nullable<int> EntityId { get; set; }
    [ForeignKey("EnvironmentalInterest")]
    public Nullable<int> EnvironmentalInterestId { get; set; }
    public int AffiliationTypeId { get; set; }
    public int StatusTypeId { get; set; }
    public virtual EnvironmentalInterest EnvironmentalInterest { get; set; }
    public virtual ICollection<AffiliationContact> 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<AffiliationContact>();
        this.Phones = new List<Phone>();
    }

    public int ContactId { get; set; }
    public string ContactIdentifier { get; set; }
    public int EntityId { get; set; }
    public Nullable<int> 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<AffiliationContact> AffiliationContacts { get; set; }
    public virtual Entity Entity { get; set; }
    public virtual ICollection<Phone> 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; }
}

Answer 1

Northwnd db = new Northwnd(@"c:\northwnd.mdf");
IEnumerable<Customer> results = db.ExecuteQuery<Customer>
(@"SELECT c1.custid as CustomerID, c2.custName as ContactName
    FROM customer1 as c1, customer2 as c2
    WHERE c1.custid = c2.custid"
);

As long as the column names in the tabular results match column properties of your entity class, LINQ to SQL creates your objects out of any SQL query.

You're able to execute native SQL code in C# via Linq so you actually don't have to convert this at all. Check out this MSDN article: https://msdn.microsoft.com/en-us/library/bb399403(v=vs.110).aspx

Answer 2

Here's how I did it:

var bpQuery =
    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 into eiGroup
    from eig in eiGroup.DefaultIfEmpty().Where(e => e.EnvIntTyp.EnvIntTyp == "Fleet")
    join a in _CEMDbContext.setAff on eig.EnvIntId equals a.EnvIntId into aGroup
    from ag in aGroup.DefaultIfEmpty()
    join ac in _CEMDbContext.setAffCon.DefaultIfEmpty() on ag.AffId equals ac.AffId into acGroup
    from acg in acGroup.DefaultIfEmpty()
    join c in _CEMDbContext.setCon.DefaultIfEmpty() on acg.ConId equals c.ConId into cGroup
    from cg in cGroup.DefaultIfEmpty()
    join p in _CEMDbContext.setPho.DefaultIfEmpty() on cg.ContactId equals p.ConId into pGroup
    from pg in pGroup.DefaultIfEmpty()
    select new BusinessParticipant
    {
        facility = f,
        sitLocInt = sli,
        envInt = eig,
        pho = pg,
    };                        

    BusinessParticipantList = bpQuery.ToList();

Answer 3

Lets reduce the problem into something easier to understand. Here's a simple left join:

SELECT As.AId, Bs.BId
FROM As
LEFT JOIN Bs ON As.AId = Bs.AId

And in LINQ:

from a in ctx.As
join b in ctx.Bs on a.AId equals b.AId into bgrp // group join -- put matching Bs
                                                 // into a group "bgrp".
from b in bgrp.DefaultIfEmpty()                  // from DefaultIfEmpty() -- if the group
                                                 // has any rows, return them. otherwise,
                                                 // return a single row with the
                                                 // entity's default value (null).
select new
{
    a.AId,
    b.BId
}

You can chain multiple left joins by just repeating:

from a in ctx.As
join b in ctx.Bs on a.AId equals b.AId into bs
from b in bs.DefaultIfEmpty()
join c in ctx.Cs on b.BId equals c.BId into cs
from c in cs.DefaultIfEmpty()
select new
{
    a.AId,
    b.BId,
    c.CId
}