title: Proper way to construct linq queries to achieve fastest performance?
body: <p>Similar question have been asked here but none fits to my need.<br>
  I made test cases to see which is faster. But I feel my linq code is still slow. How do I construct the linq code for faster performance?  </p>
  
  <p>Others said using double <code>.Tolist()</code> can cause slower operation, when I test it, it shows that it is faster than any other tests.</p>
  
  <p>Test:  </p>
  
  <pre><code>Preparation
  ---------------------------------------------------------------
  return Properties of UserInfo(userinf, true){
      UserID = userinf.UserID;
      FirstName = userinf.user.FirstName;
      MiddleName = userinf.user.MiddleName;
      LastName = userinf.user.LastName;
      LoginID = userinf.user.LoginID;
      Birthday = userinf.Birthday;
  }
  
  skip = 0;
  take = 100;
  
  total table records = 304;
  
  Linq to Entity Framework 
  </code></pre>
  
  <p>Fiddler: v2.4.0.0  </p>
  
  <pre><code>https://127.0.0.1/..../RetrieveUserInfo?skip=0&amp;take=100
  {
      "client":{
          "SessionID":"5433ab64-7e0d-444f-b886-a901ea9a0601"
      },
      "session":{
          "SessionID":"35b75daa-25ad-45a4-9f99-0e69ec3b66a4"
      }
  }
  </code></pre>
  
  <hr>
  
  <pre><code>//Test 1
  //1) 00:00:15.3068755 -- Attempt1
  //2) 00:00:13.8207905 -- Attempt2
  //3) 00:00:16.2489294 -- Attempt3
  
  var list = (from usr in dbase.userinfoes
              select usr).OrderBy(i =&gt; i.UserID).Skip(skip).Take(take).ToList();
  
  userlist = (from i in list
              select new UserInfo(i, true)).ToList();
  
  
  ///Test 2
  //1) 00:00:15.3908803
  //2) 00:00:14.8818512
  //3) 00:00:19.4761140
  
  var list = (from usr in dbase.userinfoes.AsEnumerable().OrderBy(i =&gt; i.UserID).Skip(skip).Take(take).ToList()
              select new UserInfo(usr, true)).ToList();
  
  
  //Test 3
  //1) 00:00:30.1937270
  //2) 00:00:24.1003784
  //3) 00:00:28.8806519
  
  var list = dbase.userinfoes.OrderBy(i =&gt; i.UserID).Skip(skip).Take(take).ToList();
  userlist = (from i in list select new UserInfo(i, true)).ToList();
  
  
  //Test 4
  //1) 00:00:57.2652754
  //2) 00:00:54.4051118
  //3) 00:00:55.3251644
  
  var list = (from usr in dbase.userinfoes
              select usr).ToList();
  
  userlist = (from i in list
              select new UserInfo(i, true)).OrderBy(i =&gt; i.UserID).Skip(skip).Take(take).ToList();
  
  
  //Test 5
  //1) 00:01:06.8378229
  //2) 00:01:01.2845053
  //3) 00:00:55.0721499
  
  var list = from usr in dbase.userinfoes
             select usr;
  
  userlist = (from i in list.AsEnumerable()
              select new UserInfo(i, true)).OrderBy(i =&gt; i.UserID).Skip(skip).Take(take).ToList();
  
  
  // Test 6
  // VERY LONG. It tooks all records first and construct UserInfo one by one before doing the skip and take
  
  var list = (from usr in dbase.userinfoes.AsEnumerable()
              select new UserInfo(usr, true)).OrderBy(i =&gt; i.UserID).Skip(skip).Take(take).ToList();
  
  
  //Test 7
  // VERY LONG. It tooks all records first and construct UserInfo one by one before doing the skip and take
  
  var list = from usr in dbase.userinfoes.AsEnumerable()
             select new UserInfo(usr);
  </code></pre>
  
  <p>Proper code for faster search. Thanks to <code>casperOne</code> for pointing out that <strong>the ordering, skip and take are all performed on the server</strong> is more faster. </p>
  
  <p>Here is the final code:  </p>
  
  <pre><code>var list = (from usr in dbase.userinfoes
                  .OrderBy(i =&gt; i.UserID)
                  .Skip(skip)
                  .Take(take)
                  .AsEnumerable()
              select new UserInfo(usr, true)).ToList();   
  
  1) 00:00:10.9210513
  2) 00:00:10.8270973
  3) 00:00:10.8250151
  </code></pre>
  
  <p>Thanks to <code>Richard Neil Ilagan</code> for the final code.</p>