Query is Executed Locally when GroupBy is Joined to another Table and Then Sorted

No source files or tests are named. Start with the LINQ reproduction and generated SQL in this issue, then trace EF Core's GroupBy, join, and OrderBy translation path and locate existing query-translation regression tests. Done means ordering by BuyerId, CurrentVehicleId, or LowestMonthlyPayment remains server-translated without client-evaluation warnings for paging.

Brief Overview

I am doing a group by to find the minimum monthly payment. I think join to other tables. If I execute the query everything works as expected. If I sort by any field on the tables I join to everything works as expected. If I sort by any field on the table I did the group by on the query can't be interpreted and gets executed locally.

This by-passes the paging and causes major performance issues.

Detailed Explanation

I have an application that represents a car dealership. There are 3 tables.

• Buyer (i.e. a previous customer)

    public class Buyer
    {
        public int BuyerId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

• Current Vehicle (i.e. the vehicle they previously purchased)

    public class CurrentVehicle
    {
        public int CurrentVehicleId { get; set; }
        public int BuyerId { get; set; }
        public int Year { get; set; }
        public string Make { get; set; }
        public string Model { get; set; }
    }

• Match (i.e. a match to a vehicle currently in the dealer's inventory)

    public class Match
    {
        public int MatchId { get; set; }
        public int BuyerId { get; set; }
        public int CurrentVehicleId { get; set; }
        public int InventoryId { get; set; }
        public int NewMonthlyPayment { get; set; }
    }

A buyer and current vehicle could be matched to multiple vehicles. For this purpose I only want the match that will result in the lowest monthly payment.

Here is my query:

            var bestMatches = _matchRepository.GetAll(customerMatchSummaryRequest)
                .GroupBy(m => new { m.BuyerId, m.CurrentVehicleId })
                .Select(g => new
                {
                    g.Key.BuyerId,
                    g.Key.CurrentVehicleId,
                    LowestMonthlyPayment = g.Min(m => m.MonthlyPayment)
                });

            var query = from buyer in _buyerRepository.GetAll()
                        join currentVehicle in _currentVehicleRepository.GetAll() on buyer.BuyerId equals currentVehicle.BuyerId
                        join bestMatch in bestMatches on new { buyer.BuyerId, currentVehicle.CurrentVehicleId } equals new { bestMatch.BuyerId, bestMatch.CurrentVehicleId } 
                   select new BestMatch
                   {
                       BuyerId = bestMatch.BuyerId,
                       CurrentVehicleId = bestMatch.CurrentVehicleId,
                       NewMonthlyPayment = bestMatch.LowestMonthlyPayment,
                       Buyer = buyer,
                       CurrentVehicle = currentVehicle
                   };

If I call ToList() everything works as expected.

If I do a .OrderBy on any field in Buyer everything works as expected.

If I do a .OrderBy on any field in CurrentVehicle everything works as expected.

If I do a .OrderBy on BuyerId, CurrentVehicleId, or NewMonthlyPayment the query cannot be evaluated and get executed locally.

Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'orderby [bestMatch].NewMonthlyPayment asc' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'orderby [bestMatch].NewMonthlyPayment asc' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Skip(__p_1)' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Skip(__p_1)' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Take(__p_2)' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Take(__p_2)' could not be translated and will be evaluated locally.

This is the SQL that gets generated.

SELECT [buyer].[BuyerId], [buyer].[FirstName], [buyer].[LastName], [currentVehicle].[CurrentVehicleId], [currentVehicle].[BuyerId], [currentVehicle].[Year], [currentVehicle].[Make], [currentVehicle].[Model], [t].[BuyerId], [t].[CurrentVehicleId], [t].[LowestMonthlyPayment]
FROM [UpgradeOpportunities].[Buyer] AS [buyer]
INNER JOIN [UpgradeOpportunities].[CurrentVehicle] AS [currentVehicle] ON [buyer].[BuyerId] = [currentVehicle].[BuyerId]
INNER JOIN (
    SELECT [m].[BuyerId], [m].[CurrentVehicleId], MIN([m].[MonthlyPayment]) AS [LowestMonthlyPayment]
    FROM [UpgradeOpportunities].[Match] AS [m]
    WHERE [m].[MonthlyPayment] <= @__monthlyPaymentMax_0
    GROUP BY [m].[BuyerId], [m].[CurrentVehicleId]
	) AS [t] ON ([buyer].[BuyerId] = [t].[BuyerId]) AND ([currentVehicle].[CurrentVehicleId] = [t].[CurrentVehicleId])

Everything looks like I would expect it to except it needs an ORDER BY LowestMonthlyPayment.