PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How can I modify my EF Core query to include an array conversion

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()!, "."))

This 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?

Tags:

2

Answers


  1. 0

    I think you need a property that marked [NotMapped]

    public class MyEntity
{
    public int Id { get; set; }
    public string VersionStr { get; set; }
    
    [NotMapped]
    public int[] VersionNums => VersionStr.Split(".").Select(int.Parse).ToArray();
}
    Login or Signup to reply.
  2. 0

    You can just use a normal C# cast. Because of C# typing rules, you need to cast to object first.

    query.OrderBy(x => (int[]) (object) EF.Functions.StringToArray(x.UserMetadata!.RootElement.GetProperty("version").GetString()!, "."))

    Ideally you would store your version number as an int[] array in the database in the first place.

    Eg, instead of {"Version":"1.2.3"} you would have {"Version":[1,2,3]}. Then you can just do something like

    query.OrderBy(x => (int[]) (object) x.UserMetadata!.RootElement.GetProperty("version").GetString()!)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search