LINQ2SQL：查询 - 子查询优化 [英] Linq2Sql: query - subquery optimisation

问题描述

我有以下查询：

            IList<InfrStadium> stadiums =
                (from sector in DbContext.sectors
                 where sector.Type=typeValue
                 select new InfrStadium(sector.TeamId)
                ).ToList();

和InfrStadium类的构造函数：

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
    }

当前实现执行1 + N次查询，其中n - 多项纪录获取的第一时间

Current implementation perform 1+n queries, where n - number of records fetched the 1st time.

我要以优化。

和另外一个我喜欢使用的方式'组'操作是这样做的：

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

使用适当的构造函数：

    private InfrStadium(int iTeamId, IEnumerable<InfrStadiumSector> eSectors)
    {
        IList<Sector> teamSectors = eSectors.ToList();

        ... work with data
    }

但尝试启动查询将导致以下错误：

But attempt to launch query causes the following error:

防爆$ P $型pssionSystem.Int32的   不能用于构造   型的参数   System.Collections.Generic.IEnumerable`1 [InfrStadiumSector]

Expression of type 'System.Int32' cannot be used for constructor parameter of type 'System.Collections.Generic.IEnumerable`1[InfrStadiumSector]'

问题1：

能否请您解释一下，什么是错在这里，我不明白为什么team_sectors'作为'System.Int32的应用？

Could you please explain, what is wrong here, I don't understand why 'team_sectors' is applied as 'System.Int32'?

我试图改变查询了一点（与IQueryeable替换的IEnumerable）：

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

使用适当的构造函数：

    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:

防爆$ P $型pssionSystem.Int32的   不能用于类型的参数   System.Collections.Generic.IEnumerable 1 [InfrStadiumSector]   方法   System.Linq.IQueryable 1 [InfrStadiumSector]   AsQueryableInfrStadiumSector

Expression of type 'System.Int32' cannot be used for parameter of type 'System.Collections.Generic.IEnumerable1[InfrStadiumSector]' of method 'System.Linq.IQueryable1[InfrStadiumSector] AsQueryableInfrStadiumSector'

问2：

其实，同样的问题：无法理解都很这里究竟是怎么回事......

Actually, the same question: can't understand at all what is going on here...

P.S。 我还有一个优化查询的想法（在这里描述： LINQ2SQL：查询优化），但我很想找到用1请求到DB）中的溶液

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

推荐答案

首先，在本地提取数据，并把它变成满足您的需求结构。

First, pull the data locally and put it into a structure that meets your needs.

ILookup<int, InfrStadiumSector> sectorLookup =
(
  from sector in DbContext.sectors
  where sector.Type == typeValue
  select sector
).ToLookup(sector => sector.TeamId);

然后投射在查找每个分组到InfrStadium的一个实例（不必返回到数据库）...

Then project each grouping in that lookup into an instance of InfrStadium (without going back to the database)...

IList<InfrStadium> stadiums = sectorLookup
  .Select(x => new InfrStadium(x.Key, x))
  .ToList();

和该投影使用此构造函数。

And that projection uses this constructor.

private InfrStadium(int iTeamId, IEnumerable<InfrStadiumSector> eSectors)

这篇关于LINQ2SQL：查询 - 子查询优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！