代码之家 › 专栏 › 技术社区 › BrettRobi

LinqToSQL-多个子查询创建多个往返

linq-to-sql linq .net

BrettRobi · 技术社区 · 16 年前

我有两张像这样的桌子:

alt text http://www.brettrobichaud.com/images/linq.png

我正试图从ApplianceStatisticsLog中为每个期间生成包含两个特定统计信息的查询。下面是我提出的LINQ查询。它可以工作,但会为第一个子选择生成一个查询,然后为第二个子选择的每条记录生成一个附加查询。哎呀,DB往返赛真让我受不了。

我如何重新编写它来挤出一个SQL查询?

from l in ApplianceStatisticsLogs
where l.ApplianceServerId > 1
orderby l.PeriodEndUtc ascending
select new
{
Time = l.PeriodEndUtc,
Stat1 = from s in ApplianceStatistics
        where s.ApplianceStatisticsLogId == l.ApplianceStatisticsLogId
          &&  s.ApplianceStatisticNameId == 2
        select s.Value,
Stat2 = from s in ApplianceStatistics
        where s.ApplianceStatisticsLogId == l.ApplianceStatisticsLogId
          &&  s.ApplianceStatisticNameId == 3
        select s.Value
}

2 回复 | 直到 16 年前

eglasius 16 年前

更新(基于关系) :

select new
{
Time = l.PeriodEndUtc,
Stat1 = (from s in l.ApplianceStatistics
         where s.ApplianceStatisticNameId == 2
         select s.Value
       ).ToList(),
Stat2 = (from s in l.ApplianceStatistics
         where s.ApplianceStatisticNameId == 3
         select s.Value
       ).ToList()
}

尝试 :

select new
{
Time = l.PeriodEndUtc,
Stat1 = (from s in ApplianceStatistics
                where s.ApplianceStatisticsLogId == l.ApplianceStatisticsLogId
                  &&  s.ApplianceStatisticNameId == 2
                select s.Value
       ).ToList(),
Stat2 = (from s in ApplianceStatistics
                where s.ApplianceStatisticsLogId == l.ApplianceStatisticsLogId
                  &&  s.ApplianceStatisticNameId == 3
                select s.Value
       ).ToList()
}

BrettRobi 16 年前

弗雷迪走上了正确的道路,几乎成功了。解决方案最终是为每个子查询使用first()。如:

from l in ApplianceStatisticsLogs
where l.ApplianceServerId > 1
orderby l.PeriodEndUtc ascending
select new
{
    Time = l.PeriodEndUtc,
    Stat1 = (from s in ApplianceStatistic
            where s.ApplianceStatisticsLogId == l.ApplianceStatisticsLogId
              &&  s.ApplianceStatisticNameId == 2
            select s.Value).First(),
    Stat2 = (from s in ApplianceStatistics
            where s.ApplianceStatisticsLogId == l.ApplianceStatisticsLogId
              &&  s.ApplianceStatisticNameId == 3
            select s.Value).First()
}

我忽略了在子查询中只应匹配一个记录。谢谢你的帮助,弗雷迪!

推荐文章

Megrez7 · C#ToArray转换合并为一行,导致数组元素更改

5 月前

user19291301 · 平行。ForEachAsync不会更改IEnumerable项的属性

6 月前

Pavel Foltyn · 如何在C中生成逆字典#

6 月前

JohnC · Enumerable中的空枚举。选择;或者为什么它的统计员的行为似乎与其他统计员不同?

6 月前