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

[jkruer01]

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.