title: How to do a subquery in Linq
body: <p>I have the classes <code>Foo</code> and <code>Bar</code>:</p>
  
  <pre><code>public class Foo
  {
    public int Id {get;set;}
    public IEnumerable&lt;Bar&gt; Bars {get;private set;}
  }
  
  public class Bar
  {
    public int Id {get;set;}
    public string Type {get;set;}
    public string Description {get;set;}
  }
  </code></pre>
  
  <p>These two classes map to tables in the database in a predictable '<code>Bar</code> has  foreign key to <code>Foo</code>' kind of way. (I can provide the FluentNHibernate mappings if required).</p>
  
  <p>I would like to write a linq query that does the equivalent of this:</p>
  
  <pre><code>SELECT FOOS.*, (SELECT Description FROM BARS WHERE BAR.FOOID = FOO.ID AND BAR.TYPE = 'Irish')
  FROM FOOS
  WHERE FOO.ID = 1
  </code></pre>
  
  <p>How would I do this?</p>
  
  <p>And yes, I'm aware that the query will break if the <code>Foo</code> has more than one Irish <code>Bar</code> (although I could use <code>TOP 1</code> type syntax to protect it). And yes, I mean to use a subquery rather than a join. </p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>This is what I'm trying so far:</p>
  
  <pre><code>var foosWithIrishBarDescription = FooRepository.All.Where(x =&gt; x.Id == 1).Select(x=&gt; new {FooId = x.Id, IrishBarDescription = x.Bars.Where(y =&gt; y.Type == "Irish").Select(y =&gt; y.Description)});
  </code></pre>
  
  <p>But I get the error 'The method 'Select' is not implemented.' Can this not be achieved in Linq to NHibernate? (I'm using 2.1).</p>