CODEWITHSUNDEEP
.netlinqlinq-to-sql
BrettRobi
BrettRobi

Reputation: 3931

LinqToSql - Multiple subqueries creates many roundtrips

I've got two tables that look like this:

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

I'm trying to generate query that includes two specific StatisticsNames for each period from ApplianceStatisticsLog. Below is the Linq query I have come up with. It works but generates a single query for the first sub-select, then one additional query for EACH record for the 2nd sub-select. Ouch, the DB roundtrips are killing me.

How can I rework this to squeeze out a single sql query?

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
}

Upvotes: 1

Views: 169

Answers (2)

BrettRobi
BrettRobi

Reputation: 3931

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!

Upvotes: 0

eglasius
eglasius

Reputation: 36035

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

Upvotes: 1

Related Questions