Hi I have found a quite strange error, when using OrderByRaw method of SqlKata.
var query = new Query("SoftwareVersions").OrderByRaw("string_to_array([version], '.')::int[] DESC")
Also tested it here https://sqlkata.com/playground/pgsql same error.
the problem is with the int square brackets, they are being converted as "column" with nothing in or simply 2 quotes, here is the result that is completely identical with that produced by SqlKata in runtime:
SELECT * FROM "SoftwareVersions" ORDER BY string_to_array("version", '.') :: int "" DESC
Note: here you can see after the int cast we have 2 quotes.
My question is how to avoid this unexpected behavior using SQLKata.
By default SqlKata swap the [] to the corresponding delimiters for each compiler, hence this is why you are getting the double quotes ", in MySql you may get the backtick and so on.
To avoid this, simply escape them with a backslash.
.OrderByRaw(@"string_to_array([version], '.')::int\[\] DESC")
Check https://sqlkata.com/playground/pgsql?code=var%20query%20%3D%20new%20Query(%22SoftwareVersions%22).OrderByRaw(%40%22string_to_array(%5Bversion%5D%2C%20'.')%3A%3Aint%5C%5B%5C%5D%20DESC%22