C# or Linq function that gets a max value from multiple properties on an object?

Question

I'm using Entity Framework. I want to get ID's from an IQueryable that has multiple associations, and each associated IQueryable has a last updated timestamp. I only want to get the ID if the MAX of all of the last updated timestamps (DateTime - not nullable) is greater than a given date. In Oracle I would use the GREATEST function.

I have a solution, but looking for something more elegant. I tried the SelectMany combined with a Where function, but I'm not seeing it work for this situation. Here is my solution:

public IQueryable<int> GetAllContractIdsWithChanges(MyDb context, DateTime lastTimeChecked)
{
    return context.Contracts.Where(c => c.GetGreatestLastUpdateTs() >= lastTimeChecked).Select(c=>c.ContractId);
}

public static DateTime GetGreatestLastUpdateTs(this Contract c)
{
    return new DateTime[]
    {
                c.LST_UPDT_TS,
                c.ContractSection1.Max(a => a.LST_UPDT_TS),
                c.ContractSection2.Max(a => a.LST_UPDT_TS),
                c.ContractSection3.Max(a => a.LST_UPDT_TS),
                c.ContractSection4.Max(a => a.LST_UPDT_TS),
                c.ContractSection5.Max(a => a.LST_UPDT_TS)
    }.Max();
}

I was wondering if C#/Linq has something built in it to do this naturally. - Thanks!

Answer 1

In Oracle I would use the GREATEST function.

I would recommend that you create a stored procedure to do the heavy lifting instead of piecing it together with Linq/EF; for one can call a sproc with EF.

See

Does SQL Server support GREATEST and LEAST, if not what is the common workaround?

Execute stored procedure using entity framework