title: Entity Framework 6 - Group by then Order by the First() takes too long
body: <p>I really need help with this one and couldn't find related answers after hours of searching.  </p>
  
  <p>MySQL, Entity Framework 6, database with a few millions of records, record looks like:</p>
  
  <pre><code>Indexint(11) NOT NULL  
  TaskIDint(11) NOT NULL  
  DeviceIDbigint(20) NOT NULL  
  Commentslongtext NULL  
  ExtendedResultslongtext NULL  
  RunResultint(11) NOT NULL  
  JobResultint(11) NOT NULL  
  JobResultValuedouble NOT NULL  
  ReporterIDbigint(20) NOT NULL  
  FieldIDbigint(20) NOT NULL  
  TimeOfRundatetime NOT NULL  
  </code></pre>
  
  <p>What I need is to get all the records for a specific taskID, then group by DeviceID and sort by TimeOfRun in order to get the latest data for each deviceID in a specific taskID.</p>
  
  <p>This is my code:</p>
  
  <pre><code>List&lt;JobsRecordHistory&gt; newH = db.JobsRecordHistories.AsNoTracking().Where(x =&gt; x.TaskID == taskID).GroupBy(x =&gt; x.DeviceID).
                  Select(x =&gt; x.OrderByDescending(y =&gt; y.TimeOfRun).FirstOrDefault()).ToList();
  </code></pre>
  
  <p>But this is the generated query:  </p>
  
  <pre><code>{SELECT
  `Apply1`.`Index`, 
  `Apply1`.`TaskID`, 
  `Apply1`.`DEVICEID1` AS `DeviceID`, 
  `Apply1`.`RunResult`, 
  `Apply1`.`JobResult`, 
  `Apply1`.`JobResultValue`, 
  `Apply1`.`ExtendedResults`, 
  `Apply1`.`Comments`, 
  `Apply1`.`ReporterID`, 
  `Apply1`.`FieldID`, 
  `Apply1`.`TimeOfRun`
  FROM (SELECT
  `Project2`.`p__linq__0`, 
  `Project2`.`DeviceID`, 
  (SELECT
  `Project3`.`Index`
  FROM `JobsRecordHistories` AS `Project3`
   WHERE (`Project3`.`TaskID` = @p__linq__0) AND (`Project2`.`DeviceID` = `Project3`.`DeviceID`)
   ORDER BY 
  `Project3`.`TimeOfRun` DESC LIMIT 1) AS `Index`, 
  (SELECT
  `Project3`.`TaskID`
  FROM `JobsRecordHistories` AS `Project3`
   WHERE (`Project3`.`TaskID` = @p__linq__0) AND (`Project2`.`DeviceID` = `Project3`.`DeviceID`)
   ORDER BY 
  `Project3`.`TimeOfRun` DESC LIMIT 1) AS `TaskID`, 
  (SELECT
  `Project3`.`DeviceID`
  FROM `JobsRecordHistories` AS `Project3`
   WHERE (`Project3`.`TaskID` = @p__linq__0) AND (`Project2`.`DeviceID` = `Project3`.`DeviceID`)
   ORDER BY 
  `Project3`.`TimeOfRun` DESC LIMIT 1) AS `DEVICEID1`, 
  (SELECT
  `Project3`.`RunResult`
  FROM `JobsRecordHistories` AS `Project3`
   WHERE (`Project3`.`TaskID` = @p__linq__0) AND (`Project2`.`DeviceID` = `Project3`.`DeviceID`)
   ORDER BY 
  `Project3`.`TimeOfRun` DESC LIMIT 1) AS `RunResult`, 
  (SELECT
  `Project3`.`JobResult`
  FROM `JobsRecordHistories` AS `Project3`
   WHERE (`Project3`.`TaskID` = @p__linq__0) AND (`Project2`.`DeviceID` = `Project3`.`DeviceID`)
   ORDER BY 
  `Project3`.`TimeOfRun` DESC LIMIT 1) AS `JobResult`, 
  (SELECT
  `Project3`.`JobResultValue`
  FROM `JobsRecordHistories` AS `Project3`
   WHERE (`Project3`.`TaskID` = @p__linq__0) AND (`Project2`.`DeviceID` = `Project3`.`DeviceID`)
   ORDER BY 
  `Project3`.`TimeOfRun` DESC LIMIT 1) AS `JobResultValue`, 
  (SELECT
  `Project3`.`ExtendedResults`
  FROM `JobsRecordHistories` AS `Project3`
   WHERE (`Project3`.`TaskID` = @p__linq__0) AND (`Project2`.`DeviceID` = `Project3`.`DeviceID`)
   ORDER BY 
  `Project3`.`TimeOfRun` DESC LIMIT 1) AS `ExtendedResults`, 
  (SELECT
  `Project3`.`Comments`
  FROM `JobsRecordHistories` AS `Project3`
   WHERE (`Project3`.`TaskID` = @p__linq__0) AND (`Project2`.`DeviceID` = `Project3`.`DeviceID`)
   ORDER BY 
  `Project3`.`TimeOfRun` DESC LIMIT 1) AS `Comments`, 
  (SELECT
  `Project3`.`ReporterID`
  FROM `JobsRecordHistories` AS `Project3`
   WHERE (`Project3`.`TaskID` = @p__linq__0) AND (`Project2`.`DeviceID` = `Project3`.`DeviceID`)
   ORDER BY 
  `Project3`.`TimeOfRun` DESC LIMIT 1) AS `ReporterID`, 
  (SELECT
  `Project3`.`FieldID`
  FROM `JobsRecordHistories` AS `Project3`
   WHERE (`Project3`.`TaskID` = @p__linq__0) AND (`Project2`.`DeviceID` = `Project3`.`DeviceID`)
   ORDER BY 
  `Project3`.`TimeOfRun` DESC LIMIT 1) AS `FieldID`, 
  (SELECT
  `Project3`.`TimeOfRun`
  FROM `JobsRecordHistories` AS `Project3`
   WHERE (`Project3`.`TaskID` = @p__linq__0) AND (`Project2`.`DeviceID` = `Project3`.`DeviceID`)
   ORDER BY 
  `Project3`.`TimeOfRun` DESC LIMIT 1) AS `TimeOfRun`
  FROM (SELECT
  @p__linq__0 AS `p__linq__0`, 
  `Distinct1`.`DeviceID`
  FROM (SELECT DISTINCT 
  `Extent1`.`DeviceID`
  FROM `JobsRecordHistories` AS `Extent1`
   WHERE `Extent1`.`TaskID` = @p__linq__0) AS `Distinct1`) AS `Project2`) AS `Apply1`}
  </code></pre>
  
  <p>Which takes way too long.<br>
  I don't know SQL good enough, I admit, but if insert a ToList() after the WHERE statement, then I get the results much quicker, though it's still not the right thing to do since there's a lot of un-needed data that the database passes to my app in this situation, and it's still slow = 30 seconds for 40k records.</p>
  
  <p>I also tried this:</p>
  
  <pre><code>Dictionary&lt;long, DateTime&gt; DeviceIDAndTime = db.JobsRecordHistories.AsNoTracking().Where(x =&gt; x.TaskID == taskID).GroupBy(x =&gt; x.DeviceID)
                  .Select(g =&gt; new DeviceIDaAndTime { deviceID = g.Key, timeOfRun = g.Max(gi =&gt; gi.TimeOfRun) }).ToDictionary(x =&gt; x.deviceID, x =&gt; x.timeOfRun);
  </code></pre>
  
  <p>In order to use the dictionary this way:</p>
  
  <pre><code>                List&lt;JobsRecordHistory&gt; newH = db.JobsRecordHistories.AsNoTracking().Where(x =&gt; DeviceIDAndTime.Keys.Contains(x.DeviceID) &amp;&amp; x.TimeOfRun == DeviceIDAndTime[x.DeviceID]).ToList();
  </code></pre>
  
  <p>But I get this error:</p>
  
  <pre><code>Additional information: LINQ to Entities does not recognize the method 'System.DateTime get_Item(Int64)' method, and this method cannot be translated into a store expression.
  </code></pre>
  
  <p>Which makes sense cause from what I understand, when comparing the timeOfRun to the dictionary value, LINQ needs a specific value and not a collection when composing the query.</p>
  
  <p>It's weird to me that I didn't find any related post and that other people didn't encounter this problem. I guess I missed something.</p>
  
  <p>Appreciate any help, Thanks</p>