title: How to improve LINQ to EF performance
body: <p>I have two classes: Property and PropertyValue. A property has several values where each value is a new revision. </p>
  
  <p>When retrieving a set of properties I want to include the latest revision of the value for each property.</p>
  
  <p>in T-SQL this can very efficiently be done like this:</p>
  
  <pre><code>SELECT 
  p.Id,
  pv1.StringValue,
  pv1.Revision
  FROM dbo.PropertyValues pv1
  LEFT JOIN dbo.PropertyValues pv2 ON pv1.Property_Id = pv2.Property_Id AND pv1.Revision &lt; pv2.Revision
  JOIN dbo.Properties p ON p.Id = pv1.Property_Id
  WHERE pv2.Id IS NULL
  ORDER BY p.Id
  </code></pre>
  
  <p>The "magic" in this query is to join on the lesser than condition and look for rows without a result forced by the LEFT JOIN.</p>
  
  <p>How can I accomplish something similar using LINQ to EF?</p>
  
  <p>The best thing I could come up with was:</p>
  
  <pre><code>from pv in context.PropertyValues
  group pv by pv.Property into g
  select g.OrderByDescending(p =&gt; p.Revision).FirstOrDefault()
  </code></pre>
  
  <p>It does produce the correct result but is about 10 times slower than the other.</p>