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.
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
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!