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

Question

question

MohitTyagi-2522 asked Jun 22, '21 GrzegorzF-6539 edited May 30, '22

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-csharp azure-sql-database dotnet-entity-framework-core

· 3

5 |1600 characters needed characters left characters exceeded

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

AgaveJoe · Jun 22, 2021 at 03:24 PM

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

1 ·

MohitTyagi-2522 AgaveJoe · Jun 22, 2021 at 04:34 PM

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

0 ·

AgaveJoe MohitTyagi-2522 · Jun 22, 2021 at 07:49 PM

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 ·

Answer 1 · 2021-06-23T02:26:19.627Z

DanielZhang-MSFT answered Jun 23, '21 DanielZhang-MSFT commented Jun 23, '21

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

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

MohitTyagi-2522 · Jun 23, 2021 at 09:15 AM

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 ·

DanielZhang-MSFT MohitTyagi-2522 · Jun 23, 2021 at 09:30 AM

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 ·

Answer 2 · 2022-05-30T14:55:08.703Z

GrzegorzF-6539 answered May 30, '22 GrzegorzF-6539 edited May 30, '22

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

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

question