LinqToSql - Multiple subqueries creates many roundtrips

Answer 1

Update (based on relation):

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()
}

---

Try:

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()
}

Answer 2

Freddy was on the right track and almost had it. The solution ended up being the use of First() for each subquery. As in:

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()
}

I had neglected to mention that only one record should match in the subqueries. Thanks for the help Freddy!