A real world query with where's and ifs and nulls and multiple joins oh my!

I was looking for a good example of a Join but the examples I found were simple and did not include the other elements such as the where statement and multiple tables. So here is an example that works that includes those elements. I have eliminated some of the fields if you are wondering why I have so many joins that I didn't appear to use.

The following query has multiple joins, one of the joins is joining on two columns with a defaultifempty applied - essentially making it a left join. One of the fields is an integer but I wanted to switch out a string instead - hopefully this will help someone who is looking for the answer for one of these linq constructors.


MyEntities tfs = new MyEntities();


var rptCon = (from e in tfs.Company_Zips
join b in tfs.Franchisees on e.AccountCode equals b.AccountCode
join c in tfs.Account_StoreExtensions
on new{ e.Zip_StoreExtensionID,e.Accountcode} equals new{ c.StoreExtensionID,c.AccountCode} into ex
join p in tfs.Postal_Code_Territory on e.Postal_Code_Territory_Id equals p.Postal_Code_Territory_Id
where b.ConceptCode == ConceptCode
from subq in ex.DefaultIfEmpty()

select new DTO_Franchisee_Zip
{
Zip = p.Postal_Code,
Territory_Type = (e.TerritoryType == 0 ? "TFS" :
e.TerritoryType == 1 ? "Purchased" :
e.TerritoryType == 3 ? "Option" :
e.TerritoryType == 4 ? "Vendor" : "Error")

,Zip_StoreExtension = e.Zip_StoreExtension
,StoreExtension_URL= subq==null? String.Empty : subq.ExtensionURL

}).ToList();

return rptCon;