linq - What would be a reasonably fast way to code this sql query in c#? -

i have sql query:

select      sum(h.nbheures)       heures h join      heuresprojets hp on h.hpguid=hp.hpid join      activitycodes ac on h.code=ac.activityid join      maindoeuvre mdo on ac.activityid=mdo.codeguid      hp.extraguid = '61e931c8-3268-4c9c-9ff5-ed0213d348d0'      , mdo.notype = 1 

it runs in less second, good. project uses linq entities data. (very similar sql) query terribly slow, taking more minute.

  var result = (from hp in this.heuresprojets                 join h in ctx.heures on hp.hpid equals h.hpguid                 join ac in ctx.activitycodes on h.code equals ac.activityid                 join mdo in ctx.maindoeuvre on ac.activityid equals mdo.codeguid                 hp.extraguid == this.entityguid && mdo.notype == (int)sptype                 select h.nbheures).sum();   total = result;  

i tried using nested loops instead. it's faster still slow (~15 seconds).

foreach (heuresprojets item in this.heuresprojets) {          foreach (heures h in ctx.heures.where(x => x.hpguid == item.hpid))          {                if (h.activitycodes != null && h.activitycodes.maindoeuvre.firstordefault() != null && h.activitycodes.maindoeuvre.first().notype == (int)type)                {                       total += h.nbheures;                }           } } 

am doing wrong? if there's no way optimize i'll call stored procedure keep logic in code.

edit

i modified query according ironman84's advice.

decimal total = 0;  var result = (from hp in ctx.heuresprojets               join h in ctx.heures on hp.hpid equals h.hpguid               join ac in ctx.activitycodes on h.code equals ac.activityid               join mdo in ctx.maindoeuvre on ac.activityid equals mdo.codeguid               hp.extraguid == this.entityguid && mdo.notype == (int)sptype               select h);  if(result.any())   total = result.sum(x=>x.nbheures); 

this works. runs fast , gives decimal but:
1. it's not right value
2. result cached because returns exact same value different parameters.

from looking @ code i'm thinking query grabbing every single record tables you're joining on (hence long amount of time). i'm seeing using this.heuresprojets, i'm assuming collection of database objects had grabbed database (and that's why you're not using ctx.heuresprojets). collection, then, has been hydrated time join query. in case becomes linq-to-objects query, necessitating ef go , grab of other tables' records in order complete join.

assuming i'm correct in assumption (and let me know if i'm wrong), might want try out:

var result = (from hp in ctx.heuresprojets                 join h in ctx.heures on hp.hpid equals h.hpguid                 join ac in ctx.activitycodes on h.code equals ac.activityid                 join mdo in ctx.maindoeuvre on ac.activityid equals mdo.codeguid                 hp.extraguid == this.entityguid && mdo.notype == (int)sptype                 select h).sum(h => h.nbheures); total = result;  

also, if this.heuresprojets filtered list of specific objects, can add where clause of query make sure ids in this.heuresprojets.select(hp => hp.hpid)