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!

Maybe this will give you a start. [Get a List of Max values across a list of lists](https://stackoverflow.com/questions/22735016/get-a-list-of-max-values-across-a-list-of-lists) — FrankJames, Dec 31 '20 at 19:34
IMO, if there is no way to collect ContractSection objects in an array or a list inside the Contract class and to reduce redundancy, I think this extension method is better than just putting everything inside Where(). — Ikigai programmeh, Dec 31 '20 at 19:39
Could do if all those named properties were backed by an array? Then you'd just need to max the array.. but do you need any efforts to accurately translate into oracle SQL so they can be processed on the server? Perhaps you should have oracle maintain a field on Contract when any section changes if you're finding your client side efforts aren't being evaluated in the db — Caius Jard, Dec 31 '20 at 19:43

score 0 · Answer 1 · answered Dec 31 '20 at 22:00

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

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

1 Answers1