LINQ Left Outer Joins and filtering the Outer fields.

I just had a psycho time trying to get a fairly simple LEFT OUTER JOIN query working in LINQ. In the end, I had to fudge my query with some nesting in the Select. but that's a fairly inelegant way to do things. Especially given the Transact-SQL query itself is easy, it should easily translate to LINQ. Yes, i can already hear you laughing at that statement just as I did when I wrote it. As is usually the case with LINQ, the complexity is related more to how you are trying to get your chosen data view to sing with the data you get back from a query. So while it would have been fairly easy to solve my problem with a conventional list or details view control, conventional solutions do not a guru make. So anyway, let me lay out some data definition for you to demonstrate my problem. I have a list of Companies that do Mail Ordering. Each Company may have a presence in a number of Regions. Each Region for a company may allow a number of predetermined Postage options. So, my Db tables and fields for this may be something like:

Company (Company_Id, Company_Name)
Regions(Region_Id, Region_Name)
Postage(Postage_Id, Postage_Name)
Company_Regions(Company_Region_Id, Company_Id, Region_Id)
Region_Postage(Company_Region_Id, Postage_Id)

Now, we've got a Company Interface. There is a section in the interface for managing the Regions, which is a Gridview that lists rows for the associated Regions and allows insertion and deletion to the list. What I've been asked to do is allow row selection in the Region list and have all Postage items displayed as Checkboxes under the Region list, with those that are associated being checked. Obviously, checking or unchecking a box will insert or delete a row into Region_Postage. The quickest way to get my Checkboxes to show is in a repeater that has a LEFT OUTER JOIN query behind it. In Transact-SQL, my OUTER JOIN query would look something like this.

Select post.Postage_Name, Post.Postage_Id, reg.Company_Region_Id
From Postage post
left outer join Region_Postage reg
on post.Postage_Id = reg.Postage_Id
and reg.Company_Id = --selected value of the region gridview--

The results of which will look like

Postage_Name	Postage_Id	Company_Region_Id
Local	1	22
International	2	Null
Local Courier	3	22
International Courier	4	22
Pick Up Only	5	Null

Nice. Now I bind my Checkbox value to the presence of Contract_Output_Id and handle insertion and deletion in my code behind. Done. But no, we are using LINQ now, so that Transact-SQL needs to be translated. For the most part I can translate the LEFT OUTER JOIN. There are plenty of examples online on how to do this. Here's one that I almost got working.

FROM p IN Postage
GROUP JOIN r in Region_Postage on p.Postage_Id EQUALS r.Postage_Id into reg = GROUP
FROM r in reg.DefaultIfEmpty
SELECT NEW WITH {p.Postage_Name, p.Postage_Id, .Company_Region_Id =
r.Company_Region_Id.ToString}

So that makes a group join on Region_Postage and returns a null object if there is no match. The trick has been to translate the condition on Company_Region_Id. Where does it go? I haven't found a sample and I haven't been able to figure it out with trial and error. So here's how I kludged it.

FROM p IN Postage
SELECT p.Postage_Name, p.Postage_Id, Company_Region_Id =
{ From creg in RegionPostage
Where creg.Company_Regions.Company_Id = --some selected value--
And creg.Postage_Id = p.Postage_Id
Select Company_Region_Id = creg.Company_Region_Id.ToString}.SingleOrDefault

Any port in a storm. Funny thing is, this query was more efficient.