title: LINQ; how to get record with max date with join?
body: <p>How can I do this in LINQ?</p>
  
  <pre><code>select
      *
  from customer c
  left join order o on o.CustomerID = c.CustomerID
  where o.OrderDate = (select MAX(OrderDate) from order where CustomerID = o.CustomerID )
  </code></pre>
  
  <p>not worried about dups as there will always only be one order per day.</p>
  
  <p>I got as far as the left join in LINQ, but not sure how or where to put the subquery in.</p>
  
  <pre><code>var query = from customer in clist
              from order in olist
                  .Where(o =&gt; o.CustomerID == customer.CustomerID)
              select new {
                  customer.CustomerID,
                  customer.Name,
                  customer.Address,
                  Product = order != null ? order.Product : string.Empty
              };
  </code></pre>
  
  <p>FINAL SOLUTION:</p>
  
  <pre><code>var query = from customer in clist
              from order in olist
              .Where(o =&gt; o.CustomerID == customer.CustomerID &amp;&amp; o.OrderDate ==
                  olist.Where(o1 =&gt; o1.CustomerID == customer.CustomerID).Max(o1 =&gt; o1.OrderDate)
              )
              select new {
                  customer.CustomerID,
                  customer.Name,
                  customer.Address,
                  order.Product,
                  order.OrderDate
              };
  </code></pre>
  
  <p>Another solution without any lambdas</p>
  
  <pre><code>var query = from customer in clist
              from order in olist 
              where order.CustomerID == customer.CustomerID &amp;&amp; order.OrderDate == 
                  (from o in olist 
                   where o.CustomerID == customer.CustomerID 
                   select o.OrderDate).Max()
              select new {
                  customer.CustomerID,
                  customer.Name,
                  customer.Address,
                  order.Product,
                  order.OrderDate
              };
  </code></pre>