title: LINQ join in lambda with most recent date
body: <p>I'm trying to do a linq query on table 'Personen' and join table 'Leners'.<br>
  From table 'Leners' I would like the most recent item if available.</p>
  
  <p>In SQL I would do that like this:</p>
  
  <pre><code>select p.*, l.* 
  from dbo.Personen p
  left outer join dbo.Leners l on l.LenerId = 
      (select top 1 LenerId 
       from dbo.Leners lt
       where lt.PersoonId = p.PersoonId 
       order by lt.Vanaf desc)
  </code></pre>
  
  <p>How can I do this with linq?
  I would like to do this in a lambda expression because it depends on a selection.</p>
  
  <pre><code>using (var db = new DbContext())
  {
      var query = from p in db.Personen                        
                  select p;
  
      if (someSelection != null)
      {
          query = ?
      }
  }
  </code></pre>
  
  <p>These are the tables: (EF code first)</p>
  
  <pre><code>[Table("Personen")]
  public class Persoon
  {
      [Key]
      public int PersoonId { get; set; }
  
      [StringLength(50)]
      [Required]
      public string Naam { get; set; }
  }
  
  [Table("Leners")]
  public class Lener
  {
      public int LenerId { get; set; }
  
      public int? PersoonId { get; set; }
  
      [ForeignKey("PersoonId")]
      public virtual Persoon Persoon { get; set; }
  
      public DateTime Vanaf { get; set; }
  }
  </code></pre>