title: I need to Optimize this Linq Query
body: <p>I have a Linq query that needs optimizing.</p>
  
  <p><strong>Edit</strong></p>
  
  <p>I guess over all what I am trying to do is select all the rows in the first table, but select the last or default row in Survey, CB and Science tables...</p>
  
  <p><strong>Edit</strong></p>
  
  <p>When this creates a query, I hit the database once for the entire query and then once more for EVERY single result I get back from the first query.  So if TestClass returns 30 results, I then hit the DB 30 more times for the Survey Object.</p>
  
  <p>I really need a better way to make this work.</p>
  
  <p>Thanks in advance!</p>
  
  <p><strong>Example Query</strong></p>
  
  <pre><code>return (from xx in db.test                    
          select new TestClass                    {
          Added_By_User_ID = xx.Added_By_User_ID,
          Survey = (from ru in db.Utopia
                    where ru.Province_ID == xx.Province_ID
                    orderby ru.uid descending
                    select ru).Take(1).ToList(),
                  }).ToList();
  </code></pre>
  
  <p><strong>Real Query</strong></p>
  
  <pre><code>return (from xx in db.Utopia_Province_Data_Captured_Gens
                  where xx.Owner_Kingdom_ID == ownerKingdomID
                  where kingdomList.Contains((Guid)xx.Kingdom_ID)
                  select new ProvinceClass
                  {
                      Kingdom_ID = xx.Kingdom_ID,
                      Kingdom_Island = xx.Kingdom_Island,
                      Kingdom_Location = xx.Kingdom_Location,
                      Owner_Kingdom_ID = xx.Owner_Kingdom_ID,
                      Province_ID = xx.Province_ID,
                      Province_Name = xx.Province_Name,
                      Owner_User_ID = xx.Owner_User_ID,
                      Race_ID = xx.Race_ID,
                      Updated_By_DateTime = xx.Updated_By_DateTime,
                      Networth = xx.Networth,
                      Land = xx.Land,
                      Monarch_Display = xx.Monarch_Display,
                      Owner = xx.Owner,
                      Sub_Monarch = xx.Sub_Monarch,
                      CB_Updated_By_Province_ID = xx.CB_Updated_By_Province_ID,
                      uid = xx.uid,
                      Formatted_By = xx.Formatted_By,
                      Utopian_Day_Month = xx.Utopian_Day_Month,
                      Utopian_Year = xx.Utopian_Year,
                      Ruler_Name = xx.Ruler_Name,
                      Personality_ID = xx.Personality_ID,
                      Nobility_ID = xx.Nobility_ID,
                      Money = xx.Money,
                      Daily_Income = xx.Daily_Income,
                      Food = xx.Food,
                      Runes = xx.Runes,
                      Population = xx.Population,
                      Peasents = xx.Peasents,
                      Peasents_Non_Percentage = xx.Peasents_Non_Percentage,
                      Trade_Balance = xx.Trade_Balance,
                      Building_Effectiveness = xx.Building_Effectiveness,
                      Military_Efficiency_Off = xx.Military_Efficiency_Off,
                      Military_Efficiency_Def = xx.Military_Efficiency_Def,
                      Draft = xx.Draft,
                      Soldiers = xx.Soldiers,
                      Soldiers_Regs_Off = xx.Soldiers_Regs_Off,
                      Soldiers_Regs_Def = xx.Soldiers_Regs_Def,
                      Soldiers_Elites = xx.Soldiers_Elites,
                      War_Horses = xx.War_Horses,
                      //Prisoners = xx.Prisoners,
                      Military_Net_Off = xx.Military_Net_Off,
                      Military_Net_Def = xx.Military_Net_Def,
                      Military_Current_Off = xx.Military_Current_Off,
                      Military_Current_Def = xx.Military_Current_Def,
                      Mil_Training = xx.Mil_Training,
                      Mil_Wage = xx.Mil_Wage,
                      Mil_Overall_Efficiency = xx.Mil_Overall_Efficiency,
                      Mil_Total_Generals = xx.Mil_Total_Generals,
                      Wizards = xx.Wizards,
                      Wizards_Value_Type = xx.Wizards_Value_Type,
                      Thieves = xx.Thieves,
                      Thieves_Value_Type = xx.Thieves_Value_Type,
                      Plague = xx.Plague,
                      Monarch_Vote_Province_ID = xx.Monarch_Vote_Province_ID,
                      Protected = xx.Protected,
                      Hit = xx.Hit,
                      Honor = xx.Honor,
                      Province_Notes = xx.Province_Notes,
                      CB_Export_Line = xx.CB_Export_Line,
                      Army_Out = xx.Army_Out,
                      Army_Out_Expires = xx.Army_Out_Expires,
                      Updated_By_Province_ID = xx.Updated_By_Province_ID,
                      SOM_Updated_By_Province_ID = xx.SOM_Updated_By_Province_ID,
                      SOM_Updated_By_DateTime = xx.SOM_Updated_By_DateTime,
                      CB_Updated_By_DateTime = xx.CB_Updated_By_DateTime,
                      CB_Requested = xx.CB_Requested,
                      CB_Requested_Province_ID = xx.CB_Requested_Province_ID,
                      SOM_Requested = xx.SOM_Requested,
                      SOM_Requested_Province_ID = xx.SOM_Requested_Province_ID,
                      SOS_Requested = xx.SOS_Requested,
                      SOS_Requested_Province_ID = xx.SOS_Requested_Province_ID,
                      Survey_Requested = xx.Survey_Requested,
                      Survey_Requested_Province_ID = xx.Survey_Requested_Province_ID,
                      Last_Login_For_Province = xx.Last_Login_For_Province,
                      Date_Time_User_ID_Linked = xx.Date_Time_User_ID_Linked,
                      Added_By_User_ID = xx.Added_By_User_ID,
                      NoteCount = (from yy in db.Utopia_Province_Notes
                                   where yy.Province_ID == xx.Province_ID
                                   select yy).Count(),
                      SOM = (from uu in db.Utopia_Province_Data_Captured_Type_Militaries
                             where uu.Province_ID == xx.Province_ID
                             where uu.Owner_Kingdom_ID == ownerKingdomID
                             where uu.DateTime_Added == (from ru in db.Utopia_Province_Data_Captured_Type_Militaries //datetime can be same for multiple items.
                                                         where ru.Province_ID == xx.Province_ID
                                                         where ru.Owner_Kingdom_ID == ownerKingdomID
                                                         orderby ru.uid descending // To get the last most inserted rows
                                                         select ru.DateTime_Added).FirstOrDefault()
                             select uu).ToList(),
  
  
                      SOS = (from zz in db.Utopia_Province_Data_Captured_Sciences
                             where ru.Province_ID == xx.Province_ID
                             where ru.Owner_Kingdom_ID == ownerKingdomID
                             orderby ru.uid descending
                             select ru).Take(1).ToList(),
                      Survey = (from ru in db.Utopia_Province_Data_Captured_Surveys
                                where ru.Province_ID == xx.Province_ID
                                where ru.Owner_Kingdom_ID == ownerKingdomID
                                orderby ru.uid descending
                                select ru).Take(1).ToList(),
                      CB = (from ru in db.Utopia_Province_Data_Captured_CBs
                            where ru.Province_ID == xx.Province_ID
                            where ru.Owner_Kingdom_ID == ownerKingdomID
                            orderby ru.uid descending
                            select ru).Take(1).ToList()
                  }).ToList();
  </code></pre>