Select The Record With the Lowest Payment for Each Customer using EntityFramework Core

c# entity-framework-core

Question

I have an application that matches customers to vehicles in inventory. There are 3 main tables: Customer, Match, and Inventory. The match record contains the estimated monthly payment for a specific Customer and Inventory record. A customer can be matched to multiple vehicles in Inventory.

The match record contains a CustomerId and an InventoryId along with a MonthlyPayment field and a few other miscellaneous fields.

There is a 1 to Many relationship between Customer and Match. There is a 1 to Many relationship between Inventory and Match.

For each customer, I want to select the Customer record, the match record with the lowest monthly payment, and the Inventory record for that match.

What is the best way to do this? Can it be done with a single query?

I tried this code, but entity framework can't evaluate it and it executes it locally which kills the performance.

var bestMatches = _matchRepository.GetAll(customerMatchSummaryRequest)
                              .Where(match =>
                                    (_matchRepository.GetAll(customerMatchSummaryRequest)
                                    .GroupBy(m => new { m.Bpid, m.BuyerId, m.CurrentVehicleId })
                                    .Select(g => new
                                    {
                                        g.Key.Bpid,
                                        g.Key.BuyerId,
                                        g.Key.CurrentVehicleId,
                                        LowestMonthlyPayment = g.Min(m => m.MonthlyPayment)
                                    })
                                    .Where(m => m.Bpid == match.Bpid
                                        && m.BuyerId == match.BuyerId
                                        && m.CurrentVehicleId == match.CurrentVehicleId
                                        && m.LowestMonthlyPayment == match.MonthlyPayment)
                                  ).Any())
                              .Include(m => m.Buyer)
                              .Include(m => m.Inventory);

I receive the following Output when stepping through the debugger:

Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'GroupBy(new <>f__AnonymousType2`3(Bpid = [<generated>_2].Bpid, BuyerId = [<generated>_2].BuyerId, CurrentVehicleId = [<generated>_2].CurrentVehicleId), [<generated>_2])' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'GroupBy(new <>f__AnonymousType2`3(Bpid = [<generated>_2].Bpid, BuyerId = [<generated>_2].BuyerId, CurrentVehicleId = [<generated>_2].CurrentVehicleId), [<generated>_2])' could not be translated and will be evaluated locally.
1
1
9/24/2018 8:26:59 PM

Accepted Answer

Assuming your model is something like this

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Match> Matches { get; set; }
}

public class Inventory
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Match> Matches { get; set; }
}

public class Match
{
    public int CustomerId { get; set; }
    public Customer Custmer { get; set; }
    public int InventoryId { get; set; }
    public Inventory Inventory { get; set; }
    public decimal MonthlyPayment { get; set; }
}

the query in question could be something like this:

var query =
    from customer in db.Set<Customer>()
    from match in customer.Matches
    where !customer.Matches.Any(m => m.MonthlyPayment > match.MonthlyPayment)
    select new
    {
        Customer = customer,
        Match = match,
        Inventory = match.Inventory
    };

Note that it could return more than one match for a customer if it contains more than one inventory record with the lowest payment. If the data allows that and you want to get exactly 0 or 1 result per customer, change the

m.MonthlyPayment > match.MonthlyPayment

criteria to

m.MonthlyPayment > match.MonthlyPayment ||
(m.MonthlyPayment == match.MonthlyPayment && m.InventoryId < match.InventoryId)

P.S. The above LINQ query is currently the only way which translates to single SQL query. Unfortinately the more natural ways like

    from customer in db.Set<Customer>()
    let match = customer.Matches.OrderBy(m => m.MonthlyPayment).FirstOrDefault()
    ...

or

    from customer in db.Set<Customer>()
    from match in customer.Matches.OrderBy(m => m.MonthlyPayment).Take(1)
    ...

lead to client evaluation.

2
9/24/2018 8:25:22 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow