title: Optimize Memory Usage from LINQ Query in Entity Framework 4.1
body: <p>I currently have a system where users can register and bet the scores from soccer games.
  Right now i Have over 20k users, and more than 3 million bets. Every week I have to generate a ranking, so I have a query that loads on the memory all the users with bets just like this:</p>
  
  <pre><code>from u in context.Set&lt;User&gt;().include("Bets").OrderByDescending(u =&gt; u.Points)
  select u
  </code></pre>
  
  <p>Where Points is the sum of the points earned from each guess.
  After this query is over, I save the user and his position on another table to build my ranking.</p>
  
  <p>The thing is, this query is consuming too much memory! Over 4gb!!! And I need all the users and bets to calculate the ranking. </p>
  
  <p>The first alternative that I tried was: Create another table to store the user and its points. I would iterate over this query loading 500 users each time, than calculating and saving, but I am still stuck at the memory problem:</p>
  
  <pre><code>        int page = 0;
          int step = 500;
          while (page * step &lt; count)
          {
              foreach (var u in context.Set&lt;User&gt;()
                                             .Skip(page * step)
                                             .Take(step).ToList())
              {
                 //Saves in another table
              }
              page++;
          }
          //Sorts based on the data from this other table
  </code></pre>
  
  <p>Since this didn't work, I gave up and then I tried to do something like this:</p>
  
  <pre><code>var users = (from u in context.Set&lt;User&gt;().Include("Bets")
              select u).ToList();
  context.Dispose()
  var sortedUsers = from u in users.OrderByDescending(u =&gt; u.Points)
                    select u;
  </code></pre>
  
  <p>But didn't solve as well...</p>
  
  <p>I guess the problem is about the context holding all the tracking information.
  Does anyone have any clues? How to handle large amount of data using EF 4.1?</p>
  
  <p>Thanks</p>
  
  <hr>
  
  <p>Another thing that I noticed.
  Let's say I have user A and user B,
  Each has only 1 Bet to the same Match, I expected to have something like this:</p>
  
  <pre><code>User A ---&gt; Bet 
                  \
                    Match
                  /
  User B ---&gt; Bet 
  </code></pre>
  
  <p>But I'm having two different instances of Match having the same Data.
  Is there a way to avoid this?</p>
  
  <hr>
  
  <p>Why I am not willing to put this at a stored procedure:</p>
  
  <p>The ranking is based on the guesses, and there are some rules about this sorting.</p>
  
  <p>A user has N bets. Each bet associated with a Game which has a Score.</p>
  
  <p>The first sorting criterion is the points. So I would need to calculate the points for every bet (each user has about 200 bets and will have about 300 by the end of the championship). That's the first join. </p>
  
  <p>To calculate the points of each bet I need the final score of the match. That's another join. </p>
  
  <p>After having the sum of points of each bet(which has about 10 conditionals), and sorting by it I still have to sort based on:</p>
  
  <p>No. of correct bets,
  No. of bets where the winner was guessed,
  No. of bets where one score was guessed,
  Date of the last bet,
  Date of registering.</p>
  
  <p>So that's a huge sort with about 6 criterions, and about 3 joins and lots of logic. To calculate this in LINQ is very trivial, and If I'd have to put this on a SP it would take a lot of time, and more error prone. (Never tried TDD and even unit testing in SPs... This ranking have tests for everything)</p>