title: To optimize my linq query which listed as top consuming query in azure query performance insight tool
body: <p>My Linq query to optimize:</p>
  
  <pre><code>var onlineData = (from od in peopleStatus.AsNoTracking().ToList()
  
  let location = (from zone in db.RTLS_ZONEDTLS
                  where zone.zone_id == od.Person.Zone_ID
                  select zone.area).AsNoTracking().FirstOrDefault()
  
  let zoneIdsArray = getZoneList((od.ZoneIds.ToArray()))
  let fzones = zoneIdsArray.Select(z =&gt; z).Take(4)
  
  select new OnlineDataInfoDTO
  {
      P_ID = od.Person.PERSONID,
      T_ID = (int)od.Person.TAGID,
      Z_ID = (od.Person.created_time &gt;= startOfThisDay) ? (int)od.Person.Zone_ID : -1,
      LOC = (location != null ? location : " "),
      STATUS = (od.Person.created_time &gt;= startOfThisDay) ? (int)od.Person.status : 6,
      T_BAT_SIG_STR = (int)od.Person.TAG_SIGNALSTRENGTH,
      B_BAT_SIG_STR = (int)od.Person.BS_SIGNALSTRENGTH,
      T_BAT_STA_VAL = (int)od.Person.TAG_BATTERY_STATUS_VAL,
      T_BAT_STA_PERCNT = (int)od.Person.TAG_BATTERY_STATUS_PERCNT,
      BS_BAT_STA_VAL = (int)od.Person.BS_BATTERY_STATUS_VAL,
      BS_BAT_STA_PER = (int)od.Person.BS_BATTERY_STATUS_PERCNT,
      IN_TIME = (od.Person.INTIME).ToString(),
      ALL_NOT_TME = (od.Person.ALLISNOTWELLTIME).ToString(),
      P_TME = (od.Person.PANICTIME).ToString(),
      NO_M_TME = (od.Person.NOMOTIONTIME).ToString(),
      OUT_TME = (od.Person.OUT_TIME).ToString(),
      TEMP_TME = (od.Person.TEMPEXCEEDTIME).ToString(),
      LOW_BAT_TME = (od.Person.LOW_BATTERY_TIME).ToString(),
      FOUT_TME = (od.Person.FOUT_TIME).ToString(),
      LAST_UPDATE_TIME = (od.Person.LASTUPDATEDTIME).ToString(),
      TEMP_VAL = (decimal)(od.Person.TEMP_VALUE),
  
  
      NO_OF_OUT = (
        from o in db.RTLS_FAULT_DTLS
        where (o.faultno == (int)Constants.Faults.LowBattery)
        where (startOfThisDay &lt;= o.ORC_DATETIME &amp;&amp; o.ORC_DATETIME &lt;= todayEndDate)
        where (o.PERSON_ID.ToLower() == od.Person.PERSONID.ToLower())
        select o.fltname).Count(),
      NO_OF_PANIC = (
        from o in db.RTLS_FAULT_DTLS
        where o.faultno == (int)Constants.Faults.Panic
        where (startOfThisDay &lt;= o.ORC_DATETIME &amp;&amp; o.ORC_DATETIME &lt;= todayEndDate)
        where (o.PERSON_ID.ToLower() == od.Person.PERSONID.ToLower())
        select o.fltname).Count(),
      NO_OF_IN_ACTIVE = (
        from o in db.RTLS_FAULT_DTLS
        where o.faultno == (int)Constants.Faults.InActive
        where (startOfThisDay &lt;= o.ORC_DATETIME &amp;&amp; o.ORC_DATETIME &lt;= todayEndDate)
        where (o.PERSON_ID == od.Person.PERSONID)
        select o.fltname).Count(),
      NO_OF_TEMP = (
        from o in db.RTLS_FAULT_DTLS
        where (o.faultno == (int)Constants.Faults.HighTemp || o.faultno == (int)Constants.Faults.LowTemp)
        where (startOfThisDay &lt;= o.ORC_DATETIME &amp;&amp; o.ORC_DATETIME &lt;= todayEndDate)
        where (o.PERSON_ID == od.Person.PERSONID)
        select o.fltname).Count(),
      NO_OF_LBAT = (
        from o in db.RTLS_FAULT_DTLS
        where o.faultno == (int)Constants.Faults.LowBattery
        where (startOfThisDay &lt;= o.ORC_DATETIME &amp;&amp; o.ORC_DATETIME &lt;= todayEndDate)
        where (o.PERSON_ID == od.Person.PERSONID)
        select o.fltname).Count(),
      LOCS = fzones.ToList()
  }).ToList();
  </code></pre>
  
  <p>And my getZoneList method looks like below which was used in the above query.</p>
  
  <pre><code>public int[] getZoneList(decimal[] zoneIdsArray)
  {
      int[] zoneIds = Array.ConvertAll(zoneIdsArray, x =&gt; (int)x);
      List&lt;int&gt; list = zoneIds.ToList();
      for (int c = 1; c &lt; zoneIdsArray.Count(); c++)
      {
          if (zoneIdsArray[c] == zoneIdsArray[c - 1])
          {
              list.Remove((int)zoneIdsArray[c]);
          }
      }
      return list.ToArray();
  }
  </code></pre>
  
  <p>For my application i had written a web-service using WebApi2 in visual studio-2015, with Entity framework as ORM and Azure_SQL as backend. I have observed that <strong>above linq query taking minimum 25 secs to maximum 1.5 mins to execute</strong> (used azure portal's  query performance insight tool to find it).
  After commenting and testing line by line code along with time, I came to know that </p>
  
  <pre><code>from od in peopleStatus.AsNoTracking().ToList()
  </code></pre>
  
  <p>This line taking so much time(average 55 seconds). I cant skip to convert into List to use further. Suggest me what should i do to optimize my query to decrease my overall turnaround time.  </p>