将IEnumerable投影到计划的IQueryable内，向数据库发出N个请求 [英] Projecting an IEnumerable inside an Projected IQueryable making N Requests to the Database

问题描述

我正在创建一个Asp.net Core Api，并且我需要返回Controller的Action之一，以返回DTO的IQueryable，但是其中一个属性是另一个DTO的IEnumerable，其关系是: EF的数据库模型. 例如:

I'm making an Asp.net Core Api and one of the Actions of the Controller i need to return an IQueryable of a DTO, but one of the properties is an IEnumerable of another DTO in a relationship one to many in the database model of EF. For example:

    public class Customer
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public DateTime Birthday { get; set; }
        public List<Order> Orders { get; set; }
    }

    public class Order
    {
        public int OrderNumber { get; set; }
        public Customer Customer { get; set; }
    }

还有DTO

    public class CustomerDTO
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public IEnumerable<OrderDTO> Orders { get; set; }
    }

    public class OrderDTO
    {
        public int OrderNumber { get; set; }
    }

这只是一个简单的示例，因为在我的应用程序中，每个表上都有很多字段，并且我无法将所有内容公开给前端应用程序，这就是为什么我使用DTO的原因.

This is just a simple example because in my application there is alot more fields on each table and i cannot expose everything to the frontend application, that's why i'm using DTOs.

我正在使用选择以将每个元素投影到DTO"，那里没有问题，因为我可以在ASP.NET Core Web Server输出上看到系统仅向数据库发出一个请求(以获取客户)，但是当我尝试在CustomerDTO中投影OrdersDTO时，问题就来了.例如，如果我有100个客户，EF将向数据库发出101个请求. (1个获得客户，100个获得每个客户的订单)

I'm using the Select to Project each element to the DTO, there is no problem there because i can see on the ASP.NET Core Web Server output that the system is only making one request to the database (to get the Customers), but the problem comes when i try to project the OrdersDTO inside the CustomerDTO. What's happening is for example if i have 100 customers the EF will make 101 requests to the database. (1 to get the Customers and 100 to get the Orders for each customer)

   [HttpGet]
   [EnableQuery]
   public IEnumerable<CustomerDTO> Get()
      {
        return context.Customer
        .Select(s => new CustomerDTO
        {
           Id = s.Id,
           Name = s.Name,
           Orders = s.Orders.Select(so => new OrderDTO
           {
              OrderNumber = so.OrderNumber
           })
         });
      }

如果我在使用Select投影元素之前调用ToList()，它将仅向数据库发出一个请求(按预期)，但是由于我使用的是OData，因此我需要返回一个IQueryable，以便前端应用程序可以即使只是DTO，也可以直接对数据库执行查询

If i call ToList() before i project the elements using Select it will make only one request to the Database (as intended), but i need to return an IQueryable because i'm using OData, so that the frontend application can execute queries directly to the database even if is just a DTO

我已经尝试过像这样

     Orders = s.Orders.Any() ? s.Orders.Select(so => new OrderDTO
       {
         OrderNumber = so.OrderNumber
       }) : new List<OrderDTO>()

它部分解决了问题，因为如果在100个客户中只有50个有订单，EF只会向数据库发出50个请求.

It solved the problem partially because if out of the 100 customers theres is only 50 that have orders the EF will only make 50 requests to the Database.

我想知道是否有解决此问题的方法，因为我不希望应用程序每次有用户调用API的此端点时都对数据库进行数百次查询.

I would like to know if there is a solution to this problem because i don't want the application doing hundreds of queries to the database each time some user calls this endpoint of the API.

推荐答案

投影内部集合时需要添加ToList().

You need to add ToList() when projecting the inner collection.

       Orders = s.Orders.Select(so => new OrderDTO
       {
          OrderNumber = so.OrderNumber
       }).ToList() // <--

首先，因为CustomerDTO.Orders属性类型为List<OrderDTO>，所以代码不会对此进行编译.

First because CustomerDTO.Orders property type is List<OrderDTO>, so the code does not compile w/o that.

但是即使不是(假设它是IEnumerable<OrderDTO>)，您仍然需要ToList才能获得EF Core 2.1的引入

But even it wasn't (let say it's IEnumerable<OrderDTO>), you still need ToList in order to get EF Core 2.1 introduced Optimization of correlated subqueries :

我们改进了查询转换，以避免在许多常见情况下执行"N + 1"个SQL查询，在这种情况下，使用投影中的导航属性会导致将根查询中的数据与相关子查询中的数据连接在一起.优化需要缓冲子查询的结果，我们要求您修改查询以选择采用新行为.

We have improved our query translation to avoid executing "N + 1" SQL queries in many common scenarios in which the usage of a navigation property in the projection leads to joining data from the root query with data from a correlated subquery. The optimization requires buffering the results from the subquery, and we require that you modify the query to opt-in the new behavior.

请注意最后一句话-我们要求，您将查询修改为选择加入新行为" .然后文档包含一个示例，并继续:

Note the last sentence - "we require that you modify the query to opt-in the new behavior". Then documentation contains an example and continues with:

在正确的位置添加ToList()表示您适用于订单的缓冲，从而可以进行优化

By including ToList() in the right place, you indicate that buffering is appropriate for the Orders, which enable the optimization

这篇关于将IEnumerable投影到计划的IQueryable内，向数据库发出N个请求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！