title: Most efficient way to order by and update a row in LINQ
body: <p>I'm using this code to rank players in a game.</p>
  
  <pre><code>    private void RecalculateUserRanks(GWDatabase db)
      {
          // RankedScore is a precalculated Double.
          var users = db.UserStatistics.Where(x =&gt; x.RankedScore &gt; 0);
          var usersWithRank = users.OrderByDescending(x =&gt; x.RankedScore)
                 .Select(x =&gt; new
                 {
                      x.Id,
                      x.RankedScore
                 });
  
          int position = 0;
          foreach (var u in usersWithRank)
          {
              position++;
              db.UserStatistics.First(x =&gt; x.Id == u.Id).Rank = position;
          }
  
          db.SaveChanges();
      }
  </code></pre>
  
  <p>It's not the prettiest and as the number of players grows this will probably take some time and use a bit of memory.</p>
  
  <p>I could do this in pure TSQL like this:</p>
  
  <pre><code>;WITH r AS
    (
      SELECT 
          [Id]
          ,[RankedScore]
          ,ROW_NUMBER() OVER(ORDER BY [RankedScore] DESC) AS Rnk
      FROM [dbo].[UsersStatistics]
  )
  UPDATE u
      SET u.Rank = r.Rnk
  FROM [dbo].[UsersStatistics] u
  INNER JOIN r ON r.Id = u.Id
  </code></pre>
  
  <p>But I would prefer to keep all my logic in the C# code as the database gets rebuilt all the time right now (and all other logic is there as well).</p>
  
  <p>So my question is if there is a smarter way to do this in C# LINQ (or Lambda if thats your thing) without iterating over it in a for loop, and without dragging all the data outside of the SQL?</p>