SearchProductsAsync Ordering Ignores Category Order In default Sort

NopCommerce version
4.60.2 (and later versions)

Expected behaviour
Products sorted by Category.DisplayOrder then ProductCategory.DisplayOrder (this caters for products associated to multiple categories, where the order might be different in each one)

Actual behaviour
Products sorted by ProductCategory.DisplayOrder from first/random category

Steps to reproduce the problem

Set catalogsettings.showproductsfromsubcategories to true

Create a parent Category
ID, Name
1, Parent

Create 2 child categories
ID, Name, DisplayOrder
2, ChildA, 100
3, ChildB, 99

Create two products
ID, Name
1, ProductA
2, ProductB

Associate products to the categories
CategoryID, ProductID, DisplayOrder
2,1,1
2,2,1
3,1,2
3,2,1

The products should be ordered by the Category.DisplayOrder then the ProductCategory.DisplayOrder, instead they are ordered by the DisplayOrder of the first ProductCategory (which in practice is similar to sorting by CategoryID then the Products DisplayOrder in the first category it is in)

I believe the issue is in this file

https://github.com/nopSolutions/nopCommerce/blob/develop/src/Libraries/Nop.Services/Catalog/ProductService.cs

In function SearchProductsAsync this Code

if (categoryIds.Any())
{
    var productCategoryQuery =
        from pc in _productCategoryRepository.Table
        where (!excludeFeaturedProducts || !pc.IsFeaturedProduct) &&
            categoryIds.Contains(pc.CategoryId)
        group pc by pc.ProductId into pc
        select new
        {
            ProductId = pc.Key,
            DisplayOrder = pc.First().DisplayOrder
        };

    productsQuery =
        from p in productsQuery
        join pc in productCategoryQuery on p.Id equals pc.ProductId
        orderby pc.DisplayOrder, p.Name
        select p;
}

Fails to consider the Display order of the Categories as the line "DisplayOrder = pc.First().DisplayOrder" just takes the display order of the product in the first Productcategory it is found in, which is not determined (or the more likely just the order of the categories in the database)

The following code adds two lines to consider this issue

var productCategoryQuery =
    from pc in _productCategoryRepository.Table
    join c in _categoryRepository.Table on pc.CategoryId equals c.Id //Newline
    where (!excludeFeaturedProducts || !pc.IsFeaturedProduct) &&
        categoryIds.Contains(pc.CategoryId)
        orderby c.DisplayOrder //Newline
    group pc by pc.ProductId into pc
    select new
    {
        ProductId = pc.Key,
        DisplayOrder = pc.First().DisplayOrder
    };

An alternative solution is to ignore the category display order entirely and use this line, which caters for products that have different displayorders in different categories

DisplayOrder = pc.Min(pc => pc.DisplayOrder)

Apologies if I have got this incorrect.

Thanks! We've created an issue.