title: How can I speed my Entity Framework code?
body: <p>My SQL and Entity Framework knowledge is a somewhat limited. In one Entity Framework (4) application, I notice it takes forever (about 2 minutes) to complete one of my method calls. The first queries do not take much time, but when I loop through the Entity Framework objects returned by the queries, even though I am only reading (not modifying) the data I supposedly got, it takes forever to complete the nested loops, even though there are only dozens of entries in each list and a few levels of looping. </p>
  
  <p>I expect the example below could be re-written with a fancier query that could probably include all of the filtering I am doing in my loops with some SQL words I don't really know how to use, so if someone could show me what the equivalent SQL expression would be, that would be extremely educational to me and probably solve my current performance problem.</p>
  
  <p>Moreover, since other parts of this and other applications I develop often want to do more complex computations on SQL data, I would also like to know a good way to retrieve data from Entity Framework to local memory objects that do not have huge delays in reading them. In my LINQ-to-SQL project there was a similar performance problem, and I solved it by refactoring the whole application to load all SQL data into parallel objects in RAM, which I had to write myself, and I wonder if there isn't a better way to either tell Entity Framework to not keep doing whatever high-latency communication it is doing, or to load into local RAM objects.</p>
  
  <p>In the example below, the code gets a list of food menu items for a member (i.e. a person) on a certain date via a SQL query, and then I use other queries and loops to filter out the menu items on two criteria: 1) If the member has a rating of zero for any group id which the recipe is a member of (a many-to-many relationship) and 2) If the member has a rating of zero for the recipe itself.</p>
  
  <p>Example:</p>
  
  <pre><code>List&lt;PFW_Member_MenuItem&gt; MemberMenuForCookDate =
              (from item in _myPfwEntities.PFW_Member_MenuItem
               where item.MemberID == forMemberId
               where item.CookDate == onCookDate
               select item).ToList();
  
          // Now filter out recipes in recipe groups rated zero by the member:
          List&lt;PFW_Member_Rating_RecipeGroup&gt; ExcludedGroups =
              (from grpRating in _myPfwEntities.PFW_Member_Rating_RecipeGroup
               where grpRating.MemberID == forMemberId
               where grpRating.Rating == 0
               select grpRating).ToList();
          foreach (PFW_Member_Rating_RecipeGroup grpToExclude in ExcludedGroups)
          {
              List&lt;PFW_Member_MenuItem&gt; rcpsToRemove = new List&lt;PFW_Member_MenuItem&gt;();
              foreach (PFW_Member_MenuItem rcpOnMenu in MemberMenuForCookDate)
              {
                  PFW_Recipe rcp = GetRecipeById(rcpOnMenu.RecipeID);
                  foreach (PFW_RecipeGroup group in rcp.PFW_RecipeGroup)
                  {
                      if (group.RecipeGroupID == grpToExclude.RecipeGroupID)
                      {
                          rcpsToRemove.Add(rcpOnMenu);
                          break;
                      }
                  }
              }
              foreach (PFW_Member_MenuItem rcpToRemove in rcpsToRemove)
                  MemberMenuForCookDate.Remove(rcpToRemove);
          }
          // Now filter out recipes rated zero by the member:
          List&lt;PFW_Member_Rating_Recipe&gt; ExcludedRecipes =
              (from rcpRating in _myPfwEntities.PFW_Member_Rating_Recipe
               where rcpRating.MemberID == forMemberId
               where rcpRating.Rating == 0
               select rcpRating).ToList();
          foreach (PFW_Member_Rating_Recipe rcpToExclude in ExcludedRecipes)
          {
              List&lt;PFW_Member_MenuItem&gt; rcpsToRemove = new List&lt;PFW_Member_MenuItem&gt;();
              foreach (PFW_Member_MenuItem rcpOnMenu in MemberMenuForCookDate)
              {
                  if (rcpOnMenu.RecipeID == rcpToExclude.RecipeID)
                      rcpsToRemove.Add(rcpOnMenu);
              }
              foreach (PFW_Member_MenuItem rcpToRemove in rcpsToRemove)
                  MemberMenuForCookDate.Remove(rcpToRemove);
          }
  </code></pre>