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

问题描述

我有以下查询：

1. 获取所需要的团队的ID列表从DB：

1. Get list of required team ids from DB:

        IList<int> teamIds =
            (from sector in DbContext.sectors
             where sector.Type=typeValue
             group sector by sector.TeamId into teamSectors
             select teamSectors.Key
            ).ToList();

使用这个列表中取得各部门所需的团队：

Using this list fetch all sectors for required teams:

IList<InfrStadSector> sectorsForAllTeams = (from sector in DbContext.sectors
                         where teamIds.Contains(sector.TeamId)
                         select sector
                        ).ToList();

创建体育场馆的基础上行业列表：

Create list of Stadiums based on sectors:

IList<InftStadium> stadiums = new List<InfrStadium>();
foreach(int teamId in teamIds)
{
    IList<InfrStadSector> teamSectors = 
        sectorsForAllTeams.Where(sect=>sect.TeamId==teamIds).ToList();
    stadiums.Add(new InfrStadium(teamId, teamSectors);
}

在这里我担心的是，从数据库收到的集合，我需要每队每一次应用在哪里/了ToList在客户端

My concern here is that for collection received from DB I need to apply Where/ToList on the client side once per each team

有没有办法来优化？

感谢。

P.S。潜在的，我可以排序（使用索引），然后进行排序teamIds和部门收集，而没有真正'查询'收集工作......但可能有更好的方法来优化服务器上​​的项目？

P.S. Potentially, I could sort items on server (using index), then sort teamIds and work with sectors collection without really 'querying' collection... but probably there is a better way to optimize that?

推荐答案

我觉得你可以一步到位做到这一点。

I think you can do this in one step.

var stadiums = DbContext.sectors
                        .Where( s => s.Type == typeValue )
                        .ToLookup( s => s.TeamId )
                        .Select( l => new InfrStadium( l.Key, l.ToList() )
                        .ToList();

虽然，这将是更好，如果你能有构造 InfrStadium 采取的IEnumerable＆LT;行业＆GT; 而不是需要一个名单，其中，部门和GT; ，那么你可以省略额外的了ToList

Although, it would be better if you could have the constructor for InfrStadium take an IEnumerable<Sector> rather than requiring a List<Sector>, then you could omit the extra ToList.

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