title: Linq Acrobatics: How to flatten hierarical data models?
body: <p>I use SQL like this to flatten hierarchical data.  I just create a view and toss it on the EF diagram.  However this doesn't fit the "Replace SQL Management Studio with LinqPad" mentality.  How would I code these in Linq (and C#)?  (Linq to Entities / Entity Framework 4)</p>
  
  <p>Table A holds products and table B holds many kinds of categories.  I want to select the category id as a single field in the view:</p>
  
  <pre><code>select A.*, B1.category as color, B2.category as size, B3.category as shape
  from   A left join B B1 on A.key = B1.key and B1.type = 1 -- Selects one B row
           left join B B2 on A.key = B2.key and B2.type = 2
           left join B B3 on A.key = B3.key and B3.type = 3
  </code></pre>
  
  <p>Better yet, is there a Linq pattern cookbook where you can look-up the SQL and see the Linq equivalent?  I have already seen the <a href="http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx" rel="nofollow">101 Linq examples</a> in C#.</p>