时间戳

首页 > 解决方案 > 带有 n+1 问题的 LINQ 查询

c# - 带有 n+1 问题的 LINQ 查询

问题描述

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

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

我试过包括 Cities/Mountains/Rivers 并在子查询上运行 .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();

标签： c#.net-coreef-core-2.2

解决方案

Entity Framework Core supports parts of the query being evaluated on the client and parts of it being pushed to the database. It is up to the database provider to determine which parts of the query will be evaluated in the database.

In your case, all .Any parts are evaluated in the client side I guess. You can configure your code to throw an exception for client evaluation. 

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

For more info https://docs.microsoft.com/en-us/ef/core/querying/client-eval

推荐阅读