title: Select all rows with distinct column value using LINQ
body: <p>I have a database with 4 fields that looks something like this:</p>
  
  <pre><code>ID      DeviceId       Location        Date
  1           A             ...            2
  2           A             ...            1
  3           B             ...            2
  </code></pre>
  
  <p>For each <code>DeviceId</code> I want the location from the record with the highest date. I can get the distinct <code>DeviceId</code>'s like this:</p>
  
  <pre><code>// get all locations
  var locations = Session.Query&lt;Location&gt;().ToList();
  
  //Get the latest used unique deviceId's
  var distinctDeviceIdsByDate = (
        from loc in locations
        orderby loc.DeviceId, loc.Date descending
        select loc.DeviceId).Distinct().ToList();
  </code></pre>
  
  <p>I would then use join to get the wanted rows, but this won't do any good since I can't get anything other than the <code>DeviceId</code>'s and therefore can't identify which rows to select. and if I try to select the following:</p>
  
  <pre><code>select loc
  </code></pre>
  
  <p>I can only get the rows with unique combinations of all the columns. I'm sure there's a simple solution, but I'm afraid I can't figure it out right now.</p>