How to use greatest() function in EF core LINQ queries

Question

Azure SQL now supports greatest() function to find out the greatest value among multiple columns thereby preventing from writing cumbersome CASE WHEN statements in the query.
Ref: https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-greatest-transact-sql?view=sql-server-ver15#syntax

I need to use this in a LINQ query's order by clause. I have 4 date columns and I need to order the results based on the maximum of these. Can someone please point me how can this be achieved? cc: @Duane Arnold

Answer 1

Hi MohitTyagi-2522,
As AgaveJoe said that EF Core does not support greatest() function.
And you can use Max method to get the maximum resulting value.
Here is also a related thread you can refer to.
Best Regards,
Daniel Zhang

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I know question was asked a year ago. However, since EF core 2, there is DbFunctionAttribute, which allows you to define custom non-aggregate db functions to use with linq.

[DbFunction("array_percentile")]
        public static double ArrayPercentile(double[] values, double fraction)
        {
            throw new Exception();
        }

Postgres supports array types. You can use it as follows:

var percentiles_context.MyTable.Select(x => ArrayPercentile(x.ArrayColumn, 0.9)).ToList();

Probably it will work with params as well, but you can also try array approach or fixed number of arguments

Regards GrzegorzF