具有 n+1 问题的 LINQ 查询

Question

我有一个包含三个子查询的查询，我的问题是子查询是针对每个国家/地区 (n+1( 运行的。

我已经将我的查询归结为更易于阅读，因为主查询大约 70 行，并更改了域以使其更易于理解。

我试过包括城市/山脉/河流和跑步.ToList(( 在子查询上，但无济于事。

// The CountryDto class I'm selecting to.
public class CountryDto
{
    public string CountryName { get; set; }
    public IEnumerable<CityDto> CityDtos { get; set; }
    public IEnumerable<MountainDto> MountainDtos { get; set; }
    public IEnumerable<RiverDto> RiverDtos { get; set; }
}
// The query
var query = _db.Countries
    .Select(country => new CountryDto
    {
        CountryName = country.Name,
        CityDtos = country.Citites
            .Where(city => city.Population > 10000)
            .Select(city => new CityDto
            {
                Name = city.Name,
            }),
        MountainDtos = country.Mountains
            .Where(mountain => mountain.Height > 100)
            .Select(mountain => new MountainDto
            {
                Name = mountain.Name,
            }),
        RiverDtos = country.Rivers
            .Where(river => river.Length > 1000)
            .Select(river => new RiverDto
            {
                Name = river.Name,
            }),
    })
    .Where(c => c.CityDtos.Any() || c.MountainDtos.Any() || c.RiverDtos.Any());
var totalCount = query.Count();
var countries = await query.ToListAsync();

Answer 1

实体

框架核心支持在客户端上计算查询的部分内容以及推送到数据库的部分查询。由数据库提供程序确定将在数据库中评估查询的哪些部分。

在您的情况下，我猜所有.Any部件都在客户端进行评估。可以将代码配置为在客户端评估中引发异常。

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(@"Server=(localdb)mssqllocaldb;Database=EFQuerying;Trusted_Connection=True;")
        .ConfigureWarnings(warnings => warnings.Throw(RelationalEventId.QueryClientEvaluationWarning));
}

欲了解更多信息 https://learn.microsoft.com/en-us/ef/core/querying/client-eval

Answer 2

最终将查询拆分为用于计数的.Where()部分和用于结果的.Select()部分，这消除了我的 n+1 问题。

var query = await _db.Countries
    .Include(c => c.Cities)
    .Include(c => c.Mountains)
    .Include(c => c.Rivers)
    .Where(c => c.Cities.Any(city => city.Population > 10000)
        || c.Mountains.Any(mountain => mountain.Heigh > 1000)
        || c.River.Any(river => river.Length > 100000))
    .Where(c => c.Cities.Any() || c.Mountains.Any() || c.Rivers.Any())
    .ToListAsync();
var totalCount = query.Count();
var countries = query
    .Select(country => new CountryDto
    {
        CountryName = country.Name,
        CityDtos = country.Citites
            .Select(city => new CityDto
            {
                Name = city.Name,
            }),
        MountainDtos = country.Mountains
            .Select(mountain => new MountainDto
            {
                Name = mountain.Name,
            }),
        RiverDtos = country.Rivers
            .Select(river => new RiverDto
            {
                Name = river.Name,
            }),
    })
    .ToList();