What is the correct way to use computed properties with Dynamic LINQ?

Question

I have these models.

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

public class History
{
    public Employee Employee {get;set;}
    public string Comments {get;set;}
    public DateTime Timestamp {get;set;}
}

Employee is just a class with an int Id and string Name.

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;
}

From the user, I get the name of the queried property/column, the search term and whether the user wants it to contain, start with or be equal to, and I'm using dynamic LINQ, so I can concatenate multiple queries as the user applies further filters. Basically I get a JSON like this:

{
    "column":"Employee",
    "query":"Batman",
    "type":"equals"
}

Which is converted to a Dynamic LINQ "Employee == Batman".

But on the database itself, it still creates an EmployeeId column with a foreign key, so I need to fill it with a random (existing, because of the FK) value.

I, then, query it as

var filteredListQuery = 
_context.Tickets.Include(x=>x.History)
    .ThenInclude(y=>y.Employee)
.Where("Employee.Name == \"Batman\"");

Following that, I must return a JSON with information on the tickets to display. So I try

var filteredListQueryDTO = filteredListQuery
.Select(x=>new TicketDTO()
{
    Id = x.Id.ToString(),
    Employee = x.Employee.Name,
    Timestamp = x.Timestamp.ToString("g")
}).ToListAsync();

But then it reads the employee saved on the unnecesary column, rather than using the computed property. And if I try Employee = x.History.MaxBy(x=>x.Timestamp).Employee.Name I get a LINQ expression could not be translated.

It does work, though, if I, as per the linked KB article on the exception, first call ToList() on the filteredQueryList():

var filteredListQueryDTO = (await filteredListQuery.ToListAsync())
.Select(x=>new TicketDTO()
{
    Id = x.Id.ToString(),
    Employee = x.Employee.Name,
    Timestamp = x.Timestamp.ToString("g")
}).ToList();

Or if I iterate through filteredListQuery with a foreach loop (which I think is pretty much the same).

Is there an easier (less convoluted and more professional) way to do this (and without the extra unused column on the database)?

Answer 1

I've found it best to make my actual Entity models reflect the database structure as closely as possible, and largely use them for CRUD operations. You can create a separate class to represent other queryable facets of your entity, but instead of using computed properties, use mapping logic to create a projection for those properties.

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
        });
}

Then you can apply your query criteria as appropriate. Using your dynamic LINQ example above, for example:

var filteredListQuery = BuildSummaryQuery()
    .Where("LatestEmployee.Name == \"Batman\"");