Postgresql fails to project data

ServiceStack.OrmLite PostgreSQL
1

I have the following SQL Expression:

var q = DB.From<LRPTDiagrammaTAT>()
    .Join<LRPTDiagrammaTATFiltro>((tat, filtro) => filtro.Id == tat.FiltroId)
    .Where<LRPTDiagrammaTATFiltro>(filtro => filtro.OperatoreId == idOperatore);

The query works fine in SQL Server, but it have a strange behaviour with Postgresql. One of the fields is always 0 after selecting this query with OrmLite, even if the query returns a value. To verify that, I called ToMergedParamsSelectStatement just before the query executes, and manually executed the sql command, and all fields are valued correctly.

The model is the following: https://gist.github.com/samusaran/ccb16db4ae8691a413226ccdbfead6aa

I marked in the gist which property is never correctly projected.
Thanks

2

Debugging your code I’ve found this:

image.png880×83 23.5 KB

This happens at OrmLiteWriteCommandExtensions.cs:290. Since the column ProfiloAnalisiId is actually always NULL in my case, AnalisiId is always set as 0.

3

Ok, I’ve found the issue.

Basically its the sum of 2 errors:

For PostgreSql, this does not seems to work very well:

My field is named analisi_id on the query, has ANALISIID as Alias, and is named AnalisiId in my POCO and the method returns analisiid. Pretty complex case.

Anyway, the following is the real problem:

You make a couple of assumptions to find the real field index, one of this is “finding a field that ends with the current string”. My case is very unfortunate because I have profilo_analisi_id at index 13, but analisi_id at index 15. This results in PostgreSql never actually finding the analisi_id correct index, and always setting that field equals to the profilo_analisi_id field.

I’m pretty sure this is something that involves some missing check of AliasAttribute. You are searching for analisi_id that is derived from our POCO property name AnalisiId, but we are using the alias ANALISIID that is in turn changed to analisiid by the postgre naming strategy.

4

FYI you can disable the built-in heuristics for fuzzy column fallback matches with:

OrmLiteConfig.DisableColumnGuessFallback = false;

Note: If you’re using an [Alias] it should be exact name of the column in the database, e.g. including the exact case for PostgreSQL.

By default the built-in PostgreSqlNamingStrategy.cs assumes you’re using the pgsql convention of snake_case, if you instead prefer to uppercase your column names you can create a custom naming strategy:

public class MyPostgreSqlNamingStrategy : OrmLiteNamingStrategyBase
{
    public override string GetTableName(string name) => name.ToLowercaseUnderscore().ToUpper();

    public override string GetColumnName(string name) => name.ToLowercaseUnderscore().ToUpper();
}

PostgreSqlDialectProvider.Instance.PostgreSqlDialectProvider = new MyPostgreSqlNamingStrategy();
5

Solved using a custom naming strategy, thanks!