EF Core Linq Query to select two columns from another table

Question

I'm trying to get a list of customers with their parents in the same query.

Is there a way to specify the subquery only once inside the projection?

Parent = new NameGuidResponse(
   _context.Customers.FirstOrDefault(parent => parent.Hierarchy == c.Hierarchy).Id,
   _context.Customers.FirstOrDefault(parent => parent.Hierarchy == c.Hierarchy).Name
)

public record NameGuidResponse(Guid Id, string Name);

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 = new NameGuidResponse(
          _context.Customers.FirstOrDefault(parent => parent.Hierarchy == c.Hierarchy).Id,
          _context.Customers.FirstOrDefault(parent => parent.Hierarchy == c.Hierarchy).Name
        )
      })
   .AsPaged(request.Page, request.PageSize);

Answer 1

You can use Join() method:

var pagedResult = await query
   .OrderBy(c => c.Name)
   .Join(_context.Customers, 
         c => c.Hierarchy, 
         parent => parent.Hierarchy, 
         (c, parent) => 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 = new NameGuidResponse(parent.Id, parent.Name)
         })
   .AsPaged(request.Page, request.PageSize);