c# - Entity Framework 6 - Group by then Order by the First() takes too long -

i need 1 , couldn't find related answers after hours of searching.

mysql, entity framework 6, database few millions of records, record looks like:

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

what need records specific taskid, group deviceid , sort timeofrun in order latest data each deviceid in specific taskid.

this code:

list<jobsrecordhistory> newh = db.jobsrecordhistories.asnotracking().where(x => x.taskid == taskid).groupby(x => x.deviceid).                 select(x => x.orderbydescending(y => y.timeofrun).firstordefault()).tolist(); 

but generated query:

{select 

apply1.index, apply1.taskid, apply1.deviceid1 deviceid, apply1.runresult, apply1.jobresult, apply1.jobresultvalue, apply1.extendedresults, apply1.comments, apply1.reporterid, apply1.fieldid, apply1.timeofrun (select project2.p__linq__0, project2.deviceid, (select project3.index jobsrecordhistories project3 (project3.taskid = @p__linq__0) , (project2.deviceid = project3.deviceid) order project3.timeofrun desc limit 1) index, (select project3.taskid jobsrecordhistories project3 (project3.taskid = @p__linq__0) , (project2.deviceid = project3.deviceid) order project3.timeofrun desc limit 1) taskid, (select project3.deviceid jobsrecordhistories project3 (project3.taskid = @p__linq__0) , (project2.deviceid = project3.deviceid) order project3.timeofrun desc limit 1) deviceid1, (select project3.runresult jobsrecordhistories project3 (project3.taskid = @p__linq__0) , (project2.deviceid = project3.deviceid) order project3.timeofrun desc limit 1) runresult, (select project3.jobresult jobsrecordhistories project3 (project3.taskid = @p__linq__0) , (project2.deviceid = project3.deviceid) order project3.timeofrun desc limit 1) jobresult, (select project3.jobresultvalue jobsrecordhistories project3 (project3.taskid = @p__linq__0) , (project2.deviceid = project3.deviceid) order project3.timeofrun desc limit 1) jobresultvalue, (select project3.extendedresults jobsrecordhistories project3 (project3.taskid = @p__linq__0) , (project2.deviceid = project3.deviceid) order project3.timeofrun desc limit 1) extendedresults, (select project3.comments jobsrecordhistories project3 (project3.taskid = @p__linq__0) , (project2.deviceid = project3.deviceid) order project3.timeofrun desc limit 1) comments, (select project3.reporterid jobsrecordhistories project3 (project3.taskid = @p__linq__0) , (project2.deviceid = project3.deviceid) order project3.timeofrun desc limit 1) reporterid, (select project3.fieldid jobsrecordhistories project3 (project3.taskid = @p__linq__0) , (project2.deviceid = project3.deviceid) order project3.timeofrun desc limit 1) fieldid, (select project3.timeofrun jobsrecordhistories project3 (project3.taskid = @p__linq__0) , (project2.deviceid = project3.deviceid) order project3.timeofrun desc limit 1) timeofrun (select @p__linq__0 p__linq__0, distinct1.deviceid (select distinct extent1.deviceid jobsrecordhistories extent1 extent1.taskid = @p__linq__0) distinct1) project2) apply1}

which takes way long.
don't know sql enough, admit, if insert tolist() after statement, results quicker, though it's still not right thing since there's lot of un-needed data database passes app in situation, , it's still slow = 30 seconds 40k records.

i tried this:

dictionary<long, datetime> deviceidandtime = db.jobsrecordhistories.asnotracking().where(x => x.taskid == taskid).groupby(x => x.deviceid)                 .select(g => new deviceidaandtime { deviceid = g.key, timeofrun = g.max(gi => gi.timeofrun) }).todictionary(x => x.deviceid, x => x.timeofrun); 

in order use dictionary way:

                list<jobsrecordhistory> newh = db.jobsrecordhistories.asnotracking().where(x => deviceidandtime.keys.contains(x.deviceid) && x.timeofrun == deviceidandtime[x.deviceid]).tolist(); 

but error:

additional information: linq entities not recognize method 'system.datetime get_item(int64)' method, , method cannot translated store expression. 

which makes sense cause understand, when comparing timeofrun dictionary value, linq needs specific value , not collection when composing query.

it's weird me didn't find related post , other people didn't encounter problem. guess missed something.

appreciate help, thanks

give query syntax instead of method based shot.
haven't tested locally might see improved sql generation.
or @ least maybe approach might lead down right path

using system; using system.data.entity; using system.linq; using microsoft.visualstudio.testtools.unittesting;  namespace ef.codefirst {     [testclass]     public class unittest1     {         [testmethod]         public void testmethod1()         {             using (var db = new testdbcontext())             {                 var taskid = 1;                 var query = job in db.jobrecordhistories                     job.taskid == taskid                     orderby job.timeofrun descending                     group job job.deviceid                     devicegroup                     select devicegroup;                  foreach (var devicegroup in query)                 {                     foreach (var jobrecordhistory in devicegroup)                     {                         console.writeline("deviceid '{0}', taskid'{1}' runtime'{2}'", jobrecordhistory.deviceid,                             jobrecordhistory.taskid, jobrecordhistory);                     }                 }             }         }     }      public class testdbcontext : dbcontext     {         public dbset<jobrecordhistory> jobrecordhistories { get; set; }     }      public class jobrecordhistory     {         public int id { get; set; }         public int taskid { get; set; }         public int deviceid { get; set; }         public datetime timeofrun { get; set; }     } }