Postgresql fails to project data

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:

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

Debugging your code I’ve found this:

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

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.

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();

Solved using a custom naming strategy, thanks!