EF Core Linq查询：如何在投影中仅定义一次子查询获取客户父级

你可以通过在LINQ查询中先一次性获取父客户对象，再用它构造NameGuidResponse，避免重复执行相同的子查询。这里有几种可行的实现方式：

方法1：在Select块中定义临时变量 #

直接在Select的匿名方法里先获取父客户对象，后续复用这个变量即可：

var pagedResult = await query
   .OrderBy(c => c.Name)
   .Select(c => 
   {
       var parentCustomer = _context.Customers.FirstOrDefault(parent => parent.Hierarchy == c.Hierarchy);
       return new Response
       {
           Id = c.Id,
           Name = c.Name,
           Address = c.Address,
           City = c.City,
           ZipCode = c.ZipCode,
           Email = c.Email,
           Phone = c.Phone,
           Markup = c.Markup,
           Parent = parentCustomer != null ? new NameGuidResponse(parentCustomer.Id, parentCustomer.Name) : null
       };
   })
   .AsPaged(request.Page, request.PageSize);

方法2：使用LINQ查询语法的let关键字 #

如果偏好查询语法，用let关键字提前绑定父客户对象，后续直接引用：

var pagedResult = await (from c in query
                         orderby c.Name
                         let parentCustomer = _context.Customers.FirstOrDefault(parent => parent.Hierarchy == c.Hierarchy)
                         select new Response
                         {
                             Id = c.Id,
                             Name = c.Name,
                             Address = c.Address,
                             City = c.City,
                             ZipCode = c.ZipCode,
                             Email = c.Email,
                             Phone = c.Phone,
                             Markup = c.Markup,
                             Parent = parentCustomer != null ? new NameGuidResponse(parentCustomer.Id, parentCustomer.Name) : null
                         })
                         .AsPaged(request.Page, request.PageSize);

更优方案：利用导航属性 #

如果你的Customer实体已经配置了父级导航属性（比如public Customer Parent { get; set; }，并在EF Core中正确映射了关联关系），可以直接使用导航属性替代子查询，EF Core会自动优化为JOIN查询，性能更优：

var pagedResult = await query
   .OrderBy(c => c.Name)
   .Select(c => new Response
     {
       Id = c.Id,
       Name = c.Name,
       Address = c.Address,
       City = c.City,
       ZipCode = c.ZipCode,
       Email = c.Email,
       Phone = c.Phone,
       Markup = c.Markup,
       Parent = c.Parent != null ? new NameGuidResponse(c.Parent.Id, c.Parent.Name) : null
     })
   .AsPaged(request.Page, request.PageSize);

以上方式都能避免重复执行相同的子查询，减少数据库访问次数，提升查询效率。

内容的提问来源于stack exchange，提问作者Yudiel Curbelo