在预测的可观的n请求数据库中投射一个iEnumerable[英] Projecting an IEnumerable inside an Projected IQueryable making N Requests to the Database

本文是小编为大家收集整理的关于在预测的可观的n请求数据库中投射一个iEnumerable的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我正在制作ASP.NET核心API,并且我需要返回DTO的可视性的控制器的动作之一,但是其中一个属性是另一个dto的iEnumerable,在关系中与许多人的关系中的一个属性. EF的数据库模型. 例如:

    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; }
    }

这只是一个简单的示例,因为在我的应用程序中,每个表上都有更多字段,我无法将所有内容公开到前端应用程序中,这就是为什么我使用dtos.

我正在使用选择将每个元素投射到DTO,那里没有问题,因为我可以在ASP.NET Core Web服务器输出上看到系统仅向数据库提出一个请求(要获取客户),但是当我尝试将ordersdto投射到客户DTO中时,问题就到了.例如,如果我有100个客户,EF将向数据库提出101个请求. (1获取客户和100个以获取每个客户的订单)

   [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(),它将仅向数据库提出一个请求(按预期),但是我需要返回IQueryable,因为我使用的是ODATA,以便前端应用程序可以即使只是DTO

,直接执行查询直接到数据库

我已经尝试过这样的

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

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

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

推荐答案

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

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

首先是因为CustomerDTO.Orders属性类型为List<OrderDTO>,因此代码不会编译w/o.

但即使不是(假设是IEnumerable<OrderDTO>),您仍然需要ToList才能获得EF Core 2.1引入相关子Queries的优化 :

我们已经改进了查询翻译,以避免在许多常见方案中执行" n + 1" SQL查询,在许多常见情况下,在投影中,导航属性的使用导致从根查询中加入数据与相关子提点的数据连接数据.优化需要从子查询中缓冲结果,我们要求您修改查询以选择新行为.

请注意最后一句话 - "我们需要将查询修改为 opt -int -in 新行为.然后文档包含一个示例,并继续以下内容:

通过在正确的位置加入ToList(),您指出缓冲适用于订单,从而启用优化

本文地址:https://www.itbaoku.cn/post/1557051.html

问题描述

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; }
    }

And the 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; }
    }

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.

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

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

I already tried putting like this

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

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.

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.

推荐答案

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

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

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

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 :

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:

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