Inability to use "MaxBy" on "Where" clauses with EFCore + MySQL

Question

My database has the following structure:

public class Ticket
{
    public int Id { get; set; }
    public List<History> Histories { get; set; }
}

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class History
{
    public int Id { get; set; }
    public Employee Employee { get; set; }
    public DateTime Timestamp { get; set; }
}

The user provides the name of the property to filter by and the query string. What I had in mind is that I needed to allow the users to query Tickets by a computed property in Tickets, such as

public Employee LatestEmployee
{
    get => History.MaxBy(x=>x.Timestamp).Employee;
}

I was suggested to leave the Entity models strictly to reflect the db structure and use a separate class to represent queryable properties of the entity:

public class TicketSummary
{
    public int TicketId {get;set;}
    public Employee LatestEmployee {get;set;}
}

public IQueryable<TicketSummary> BuildSummaryQuery()
{
    return _context.Tickets.Select(t => new TicketSummary
        {
            TicketId = t.Id,
            LatestEmployee = t.History.MaxBy(x=>x.Timestamp).Employee
        });
}

And then call BuildSummaryQuery().Where(x=>x.LatestEmployee.Name == "Batman"). However, I found out MaxBy() could not be translated into a valid query on the MySQL Database. I keep getting a The LINQ expression could not be translated. How can I work out a similar, valid query?

Answer 1

As per StriplingWarrior's comment (What is the correct way to use computed properties with Dynamic LINQ?), instead of using MaxBy(), I successfully managed to create a valid query using OrderByDescending() and FirstOrDefault()

First, instead of computed properties on the entity, the actual queryable entity is

public class TicketSummary
{
    public string TicketId { get; set; }
    public History? LatestHistory { get; set; }
}

Then, the query is composed as follows

//Includes the tables needed for the filterable properties.
var firstQuery = _context.Tickets.Include(t => t.Histories).ThenInclude(h => h.Employee);

//Creates an IQueryable<TicketSummary> with the latest History entity
var ticketSummariesQueryable = firstQuery.Select(x => new TicketSummary
{
    TicketId = x.Id.ToString(),
    LatestHistory = x.Histories.OrderByDescending(x=>x.Timestamp).FirstOrDefault()
});

//Finally apply the filters given by the user
var filteredQuery = ticketSummariesQueryable.Where(ts=>ts.LatestHistory.Employee.Name == "Black Canary");

This way the most recent History is stored on LatestHistory to be promptly used on queries.