Problem Statement:

I am using Entity Framework (EF) Core and PostgreSQL. In my target table, there is a JSONB column that might contain a version number. My goal is to sort the query by this version number, but the version needs to be sorted as integers.

Target PostgreSQL Query:

ORDER BY string_to_array("Metadata"->>'version', '.')::int[]

Current EF Core Query:

query.OrderBy(_ => EF.Functions.StringToArray(_.UserMetadata!.RootElement.GetProperty("version").GetString()!, "."))

The above EF Core query generates the following PostgreSQL query:

ORDER BY string_to_array("Metadata"->>'version', '.')

However, the conversion to ::int[] is missing, which is crucial for sorting by integer values.

Question:

How can I modify my EF Core query to include the ::int[] conversion so that the generated PostgreSQL query includes the important part for integer sorting?