Missing binding on anonymous object select

ServiceStack.OrmLite
1

I have a lot of query that does not bind variables correctly when using manual selects.

Note that I’m using AliasAttribute on every table and every field I have defined, and that seems to lead to problems (usually non-binded fields does not have the AS clause in the Select list).

Here is an example:

var q = DB.From<LRDAnalisi>()
    .Join<LRDAnalisi, LRDContenitore>((dana, dcont) => dana.ContenitoreId == dcont.Id, DB.TableAlias("c"))
    .Join<LRDAnalisi, LRDProfiloAnalisi>((dana, dprofana) => dana.Id == dprofana.AnalisiId, DB.TableAlias("dprofana"))
    .Where<LRDProfiloAnalisi>(dprofana => Sql.TableAlias(dprofana.ProfiloAnalisiId, "dprofana") == null)
    .OrderBy<LRDAnalisi>(x => x.Ordine)
    .SelectDistinct<LRDAnalisi, LRDProfiloAnalisi, LRDContenitore>((dana, dprofana, dcont) =>
        new //ProfiloAnalisiDTO
        {
            Id = Sql.TableAlias(dprofana.Id, "dprofana"),
            AnalisiId = dana.Id,
            Codice = dana.Codice,
            Descrizione = dana.Descrizione,
            ContenitoreId = Sql.TableAlias(dcont.Id, "c"),
            ContenitoreCodice = Sql.TableAlias(dcont.Codice, "c"),
            ContenitoreDescrizione = Sql.TableAlias(dcont.Descrizione, "c"),
            dana.Ordine, // serve solo per ordinare, nel DTO restituito non c'e' questo campo
            VersioneRecord = Sql.TableAlias(dprofana.VersioneRecord, "dprofana")
        });

This query seems to work fine.

Now I change ContenitoreId “formula” to dana.ContenitoreId. Note that the field is used in the first INNER JOIN so while I’m using a different column, the values are absolutely the same.

The query won’t successfully bind ContenitoreId anymore, and the field will always 0, no matter what. This query also is full of JoinAlias\TableAlias. Those would not be needed in this specific query, but we have to use because basically every field of queries like that because otherwise some fields will never bind to the property of the anonymous object.

Please help us because we have a lot of query with the anonymous projection and that means that a the current state we cannot trust OrmLite when doing this kind of queries.

2

So basically what I noticed is that you don’t write an AS clause if the C# name of the column and the field of the Anonymous Object are the same. In my case, where I use AliasAttribute everywhere, this is a wrong assumption an it seems this lead to problems.

I would be so happy to get rid of all those AliasAttribute since they are not handled correctly by Ormlite, but I cannot since the product is already release to customers, and I cannot enforce my company to rebuild all customer DBs just because of some wrong name-handling.

Please check this issue and, if needed, always use aliases if I’m projecting to a custom anonymous object. Or check for the AliasAttribute when you can’t bind a variable.

EDIT: can confirm that is an alias problem because

ContenitoreId = Sql.As(dana.ContenitoreId, "ContenitoreId")

works but

ContenitoreId = dana.ContenitoreId

does not.

3

As per previous comment, will need the missing classes or a stand-alone repro to be able to produce a repro.

4

Here is the repro: https://github.com/samusaran/SSOrmLiteAliasIssues

It contains the repro for both this and the referenced problems.

5

This is working as expected:

new { ContenitoreId = dana.ContenitoreId }

Is the same as:

new { dana.ContenitoreId }

Which is inferred as using the [Alias("DCONTENITOREID")] on the ContenitoreId column name which is what gets selected:

"dana"."DCONTENITOREID",

So yeah if you wanted to revert back to using the property name instead of your custom alias you can use:

ContenitoreId = Sql.TableAlias(dana.ContenitoreId, "dana"),

Which will generate

dana."DCONTENITOREID" AS ContenitoreId
6

I’ve decided to make this configurable in this commit which you can specify to always use the anonymous object properties as aliases by setting q.UseSelectPropertiesAsAliases = true.

I’ve also added a options lambda in the From expression you can use to initialize SqlExpression<T> properties in a fluent expression, e.g:

var q = db.From<LRDAnalisi>(options => {
        options.SetTableAlias("dana");
        options.UseSelectPropertiesAsAliases = true;
    })
    .Join<LRDAnalisi, LRDContenitore>((dana, dcont) => dana.ContenitoreId == dcont.Id, db.TableAlias("c"))
    .Join<LRDAnalisi, LRDProfiloAnalisi>((dana, dprofana) => dana.Id == dprofana.AnalisiId, db.TableAlias("dprofana"))
    .Where<LRDProfiloAnalisi>(dprofana => Sql.TableAlias(dprofana.ProfiloAnalisiId, "dprofana") == null)
    .SelectDistinct<LRDAnalisi, LRDProfiloAnalisi, LRDContenitore>((dana, dprofana, dcont) =>
        new //ProfiloAnalisiDTO
        {
            Id = Sql.TableAlias(dprofana.Id, "dprofana"),
            AnalisiId = dana.Id,
            Codice = dana.Codice,
            Descrizione = dana.Descrizione,
            ContenitoreId = dana.ContenitoreId,
            ContenitoreCodice = Sql.TableAlias(dcont.Codice, "c"),
            ContenitoreDescrizione = Sql.TableAlias(dcont.Descrizione, "c"),
            VersioneRecord = Sql.TableAlias(dprofana.VersioneRecord, "dprofana")
        });

Where it will always use the custom select properties as aliases, e.g:

SELECT DISTINCT 
    dprofana."IDDPROFILOANALISI" AS Id, 
    "dana"."IDDANALISI" AS "AnalisiId", 
    "dana"."CODICE" AS "Codice", 
    "dana"."DESCRIZIONE" AS "Descrizione", 
    "dana"."DCONTENITOREID" AS "ContenitoreId", 
    c."CODICE" AS ContenitoreCodice, 
    c."DESCRIZIONE" AS ContenitoreDescrizione, 
    dprofana."VERSIONERECORD" AS VersioneRecord 
FROM "LRDANALISI" AS "dana" 
INNER JOIN "LRDCONTENITORI" AS "c" ON ("dana"."DCONTENITOREID" = "c"."IDDCONTENITORE") 
INNER JOIN "LRDPROFILOANALISI" AS "dprofana" ON ("dana"."IDDANALISI" = "dprofana"."DANALISIID")
WHERE (dprofana."DPROFILOANALISIID" is null)

This change is available in the latest v5.4.1 that’s now available on MyGet.