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.

// Next guess: Maybe the DB field has some prefix that we don't have in our C# field?

// e.g. CustomerId (C#) vs t130CustomerId (DB)

if (dbFieldName.EndsWith(fieldName, StringComparison.OrdinalIgnoreCase))

{

return i;

}

// Next guess: Maybe the DB field has some prefix that we don't have in our C# field *and* has underscores?

// e.g. CustomerId (C#) vs t130_CustomerId (DB)

if (dbFieldNameWithNoUnderscores.EndsWith(fieldName, StringComparison.OrdinalIgnoreCase))

{

return i;

}

// Next guess: Maybe the DB field has some prefix that we don't have in our C# field *and* has special characters?

// e.g. CustomerId (C#) vs t130#CustomerId (DB)

if (dbFieldNameSanitized.EndsWith(fieldName, StringComparison.OrdinalIgnoreCase))

{

return i;

}

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.

mythz
—
2018-12-29T13:37:09Z —
#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: