title: Linq to entities: replace OrderByDescending/First with Max?
body: <p>I have the following query that I'm using to extract data from an SQL database using Entity Framework.</p>
  
  <pre><code>var result = ViewHelper.Create&lt;Keyword&gt;().Find(query, inc =&gt; inc.Page)
      .GroupBy(g =&gt; g.PageId)
      .Select(g =&gt; g.OrderByDescending(o =&gt; o.Volume).First())
      .OrderByDescending(r =&gt; r.Volume)
      .Select(r =&gt; new LinkDto(
          currentUrl.ToAbsolute(r.Page.Path),
          r.Term.HighlightExcept(meta.ReservedWords)));
  </code></pre>
  
  <p>If you ignore the first line (which is our own repository stuff), the next 3 lines are the important stuff.</p>
  
  <p>What I'm trying to do is group the rows by <code>PageId</code>, selecting the one with the largest <code>Volume</code>.</p>
  
  <p>Then, from the grouped data, I reorder again by <code>Volume</code> to return the overall list in <code>Volume</code> order.</p>
  
  <p>It does seem to produce the required result, but as we all know, not all Linq statements perform the same way, and I'm convinced it can be improved upon.</p>
  
  <p>The first thing that springs to mind is that the <code>OrderByDescending/First</code> elements could be replaced by a <code>Max</code> - unfortunately I haven't been able to get it to hang together syntactically.</p>
  
  <p>Can this statement be improved upon.</p>
  
  <p>For completeness, here's the table.</p>
  
  <pre><code>CREATE TABLE [dbo].[Keyword] (
      [Id]     SMALLINT      NOT NULL,
      [Term]   VARCHAR (100) NOT NULL,
      [Volume] INT           NOT NULL,
      [PageId] SMALLINT      NOT NULL,
      CONSTRAINT [PK_Keyword] PRIMARY KEY CLUSTERED ([Id] ASC),
      CONSTRAINT [AK_Keyword_Keyword] UNIQUE NONCLUSTERED ([Term] ASC),
      CONSTRAINT [FK_Keyword_Page] FOREIGN KEY ([PageId]) REFERENCES [dbo].[Page] ([Id])
  );
  </code></pre>