sql - 在 c# 中编写此 sql 查询的合理快速方法是什么？

Question

我有这个 SQL 查询:

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

它运行不到一秒钟，这很好。我的项目使用 LINQ to entities 来获取数据。这个(与 sql 非常相似)查询非常慢，需要一分多钟。

  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; 

我尝试改用嵌套循环。它更快但仍然很慢(约 15 秒)。

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;
               }
          }
}

我做错了什么吗？如果没有办法对此进行优化，我将只调用一个存储过程，但我真的很想在代码中保留逻辑。

编辑

我根据 IronMan84 的建议修改了我的查询。

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);

这几乎行得通。它运行速度很快并返回一个小数但是:
1. 值不对
2.结果显然被缓存了，因为它返回完全相同的值，但参数不同。

Answer 1

通过查看您的代码，我认为您的查询正在从您要加入的那些表中获取每条记录(因此需要很长时间)。我看到你在使用 this.HeuresProjets，我假设它是你已经从数据库中获取的数据库对象的集合(这就是为什么你没有使用 ctx. HeuresProjets).那么，当您进入连接查询时，该集合可能已经被水化了。在这种情况下，它变成了 LINQ-To-Objects 查询，需要 EF 去获取所有其他表的记录以完成连接。

假设我的假设是正确的(如果我错了请告诉我)，您可能想试试这个:

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; 

此外，如果 this.HeuresProjets 是仅包含特定对象的过滤列表，那么您只需添加到查询的 where 子句中即可确保 ID位于 this.HeuresProjets.Select(hp => hp.HPId)