question

MohitTyagi-2522 avatar image
0 Votes"
MohitTyagi-2522 asked GrzegorzF-6539 edited

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

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://docs.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: @DuaneArnold-0443

dotnet-csharpazure-sql-databasedotnet-entity-framework-core
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Create a View using GREATEST() then use LINQ to query the View.

1 Vote 1 ·

Nice idea! Thank you for this.
It would have been even better if this function was somehow available directly in EF core.

0 Votes 0 ·

It would have been even better if this function was somehow available directly in EF core.

I'm pretty sure EF Core does not has this feature. At least I can't find a reference. You can request this feature in the GitHub repo.


0 Votes 0 ·
DanielZhang-MSFT avatar image
0 Votes"
DanielZhang-MSFT answered DanielZhang-MSFT commented

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.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Max() is used for aggregation when we are grouping the rows of data and we need to get maximum of a column in that group.
Greatest() is for finding the maximum of multiple columns in the same row of data



0 Votes 0 ·

Hi @MohitTyagi-2522,
You can query the same row of data in multiple columns in a collection, and then use Max to get the maximum value.
Best Regards,
Daniel Zhang

0 Votes 0 ·
GrzegorzF-6539 avatar image
0 Votes"
GrzegorzF-6539 answered GrzegorzF-6539 edited

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.