Linq2Sql: query - subquery optimisation
- by Budda
I have the following query:
IList<InfrStadium> stadiums =
(from sector in DbContext.sectors
where sector.Type=typeValue
select new InfrStadium(sector.TeamId)
).ToList();
and InfrStadium class constructor:
private InfrStadium(int teamId)
{
IList<Sector> teamSectors = (from sector in DbContext.sectors
where sector.TeamId==teamId
select sector)
.ToList<>();
... work with data
}
Current implementation perform 1+n queries, where n - number of records fetched the 1st time.
I want to optimize that.
And another one I would love to do using 'group' operator in way like this:
IList<InfrStadium> stadiums =
(from sector in DbContext.sectors
group sector by sector.TeamId into team_sectors
select new InfrStadium(team_sectors.Key, team_sectors)
).ToList();
with appropriate constructor:
private InfrStadium(int iTeamId, IEnumerable<InfrStadiumSector> eSectors)
{
IList<Sector> teamSectors = eSectors.ToList();
... work with data
}
But attempt to launch query causes the following error:
Expression of type 'System.Int32'
cannot be used for constructor
parameter of type
'System.Collections.Generic.IEnumerable`1[InfrStadiumSector]'
Question 1:
Could you please explain, what is wrong here, I don't understand why 'team_sectors' is applied as 'System.Int32'?
I've tried to change query a little (replace IEnumerable with IQueryeable):
IList<InfrStadium> stadiums =
(from sector in DbContext.sectors
group sector by sector.TeamId into team_sectors
select new InfrStadium(team_sectors.Key, team_sectors.AsQueryable())
).ToList();
with appropriate constructor:
private InfrStadium(int iTeamId, IQueryeable<InfrStadiumSector> eSectors)
{
IList<Sector> teamSectors = eSectors.ToList();
... work with data
}
In this case I've received another but similar error:
Expression of type 'System.Int32'
cannot be used for parameter of type
'System.Collections.Generic.IEnumerable1[InfrStadiumSector]'
of method
'System.Linq.IQueryable1[InfrStadiumSector]
AsQueryableInfrStadiumSector'
Question 2:
Actually, the same question: can't understand at all what is going on here...
P.S.
I have another to optimize query idea (describe here: Linq2Sql: query optimisation) but I would love to find a solution with 1 request to DB).
