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

Question

I have this SQL query:

select 
    sum(h.nbHeures) 
from 
    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
where 
    hp.ExtraGuid = '61E931C8-3268-4C9C-9FF5-ED0213D348D0' 
    and mdo.NoType = 1

It runs in less than a second, which is good. My project uses LINQ to entities to get data. This (very similar to the sql) query is terribly slow, taking more than a 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
                where hp.ExtraGuid == this.EntityGuid && mdo.NoType == (int)spType
                select h.NbHeures).Sum();
  total = result;

I tried using nested loops instead. It's faster but 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 I doing something obviously wrong? If there's no way to optimize this I'll just call a stored procedure but I would really like the keep the logic in the code.

EDIT

I modified my query according to 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
              where hp.ExtraGuid == this.EntityGuid && mdo.NoType == (int)spType
              select h);

if(result.Any())
  total = result.Sum(x=>x.NbHeures);

This almost works. It runs fast and gives back a decimal but:
1. It's not the right value
2. The result is clearly cached because it returns the exact same value with different parameters.

Have you looked at SQL Profiler to see what SQL the LINQ query is executing under the hood? — Pete, Apr 05 '13 at 19:20
@Pete SQL Profiler isn't installed, 'Ill yake a look at it when it finished downloading — 0xFF, Apr 05 '13 at 19:35
You can read the post http://stackoverflow.com/questions/696431/linq-query-with-nullable-sum-problem for your nullable sum problem. — Fabrice Mainguené, Apr 06 '13 at 14:08

Corey Adler · Accepted Answer · 2013-04-05T20:11:57.617

3

From looking at your code I'm thinking that your query is grabbing every single record from those tables that you're joining on (hence the long amount of time). I'm seeing you using this.HeuresProjets, which I'm assuming is a collection of database objects that you already had grabbed from the database (and that's why you're not using ctx.HeuresProjets). That collection, then, has probably already been hydrated by the time you get to your join query. In which case it becomes a LINQ-To-Objects query, necessitating that EF go and grab all of the other tables' records in order to complete the join.

Assuming I'm correct in my assumption (and let me know if I'm wrong), you might want to try this 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
                where hp.ExtraGuid == this.EntityGuid && mdo.NoType == (int)spType
                select h).Sum(h => h.NbHeures);
total = result;

Also, if this.HeuresProjets is a filtered list of only specific objects, you can then just add to the where clause of the query to make sure that the IDs are in this.HeuresProjets.Select(hp => hp.HPId)

edited Apr 05 '13 at 20:11

answered Apr 05 '13 at 19:29

Corey Adler

15,897
18
66
80

Changing 'this' for 'ctx' give me this error: "The cast to value type 'Decimal' failed because the materialized value is null.", which is weird because the only decimal in this is h.NbHeures and it's not nullable. – 0xFF Apr 05 '13 at 19:38
It's saying that the result of the query was null. – Corey Adler Apr 05 '13 at 19:42
Ok but is shouldn't be null. It's really fast though, I hope we can fix it – 0xFF Apr 05 '13 at 19:47
Ok I tried to only "select h" then do if (result.Any()) total = result.Sum(x=>x.NbHeures); It kinda works but the query is clearly cached because I get the same value for different Project(the "this"). – 0xFF Apr 05 '13 at 19:52
Your newest edit gives me the "The cast to value [...]" error again. Lemme edit my post for more clarity – 0xFF Apr 05 '13 at 20:20
Coming back after the weekend I successfully made it work. Thank you for your help – 0xFF Apr 08 '13 at 13:00

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

1 Answers1