title: Linq query and Foreach on large number of records from SQL database
body: <p>I'm working with Entity Framework and Linq. I need to do a query on 2 properties of my object.</p>
  
  <p>I have this object on the database (about 200.000 records): </p>
  
  <pre><code>public class DeviceState
  {
      public int ID { get; set; }
      public DateTime TimeStamp { get; set; }
      public string StatusCode { get; set; }      
      public int Device_ID { get; set; }
  }
  </code></pre>
  
  <p>I need to do a query like this:</p>
  
  <pre><code>List&lt;DeviceState&gt; listState = systemDB.DeviceStates.Where(s =&gt; s.Device_ID == DeviceID).Where(l =&gt; l.TimeStamp &gt; startDate).Where(l =&gt; l.TimeStamp &lt; endDate).Where(s =&gt; s.StatusCode == "xx").ToList();
  foreach (DeviceState status in listState)
  {  
     // here I need to save in an object the status code and the time stamp:
     object.StatusCode= status.StatusCode;
     object.TimeStamp = status.TimeStamp; 
  }
  </code></pre>
  
  <p>This query takes much time (15 minutes approx). I think that is due to the creation of the list. So I tried this:</p>
  
  <pre><code>foreach (DeviceState status in systemDB.DeviceStates.Where(s =&gt; s.Device_ID == DeviceID).Where(l =&gt; l.TimeStamp &gt; startDate).Where(l =&gt; l.TimeStamp &lt; endDate).Where(s =&gt; s.StatusCode == "xx"))
  {  
     // here I need to save in an object the status code and the time stamp:
     object.StatusCode= status.StatusCode;
     object.TimeStamp = status.TimeStamp; 
  }
  </code></pre>
  
  <p>This was much faster in creating the list. But I still have performance problems due to the foreach cycle. It takes 5ms for each element.</p>
  
  <p>I need to find a solution that takes seconds to execute that.</p>