I have a Persons table and other tables related – Orders, ContactHistory, Addresses, and Emails.
The Persons table has basic details – name, date of birth, and a few other properties.
The class for the table has collections for the tables referenced by the Persons table, so, I can use them in filters where needed or query referenced.

All those tables have Updated property that is set on every update.

I need to return a page of results ordered descendingly by the latest change.

So, if somebody changes the address for a person, changes their name, or contacts the person, all those actions should move the person to the top.

The base part of my query looks like

var result = await _context
               .Persons
               .Include(p => p.Addresses)
               .Include(p => p.Emails)
               .Include(p => p.Orders)
               .Include(p => p.ContactHistory)
               .OrderByDescending(p => p.Updated)
               .AsSplitQuery()
               .AsNoTracking()
               .ToArrayAsync();

I need to change OrderByDescending(p => p.Updated) to include the other tables.

I cannot use ThenBy as this will not work – every next part will order the data only when all the previous dates are the same.

I need something like OrderByDescending(p => Max(p.Updated, p.Addresses.Updated, p.Emails.Updated ... and so on))

I am considering writing a SQL Server function and calling it from the c# code with ef core if I cannot find a solution.

An example of how I would do it in T-SQL:

    select distinct top 10 p.* from persons p
      inner join Addresses a on p.id = a.PersonId
      inner join Emails e on p.id = e.PersonId
      inner join Orders o on p.id = o.PersonId
      inner join ContactHistory c on p.id = c.PersonId
    order by (select max(updated) from (values(p.Updated), (a.Updated),
      (e.Updated), (o.Updated), (c.Updated)) t(updated))

It is not the most efficient query, unfortunately, but the business needs demand that logic.

I am also open to other suggestions on how to tackle this problem – maybe there is a pattern I don’t know about or something else.

I am pretty sure this task is not unique and the question has been asked many times here, but I could not find the keywords to discover it, so, I sincerely apologise in advance for the possible duplicate.