JoinAlias vs TableAlias

ServiceStack.OrmLite
1

Are there some difference in usage between JoinAlias and TableAlias? While the former is working fine (but marked as obsolete), the latter does not create well formed ON clauses like:

JoinAlias
INNER JOIN "LRDREPARTI" AS "reparto" ON ("LRARICHIESTE"."DREPARTOID" = "reparto"."IDDREPARTO")

TableAlias
INNER JOIN "LRDREPARTI" AS "reparto" ON ("LRARICHIESTE"."DREPARTOID" = "LRDREPARTI"."IDDREPARTO")

When changing from JoinAlias to TableAlias I’d just replaced the old function with the new. Should I do something different?

Thanks!

2

Yeah TableAlias() is the replacement for JoinAlias() which uses a different impl that substitutes the alias whilst walking the expression tree whilst generating SQL Statements whereas JoinAlias() worked by post string substitution on the generated SQL and therefore was more fragile.

Yeah you should only need to replace JoinAlias() with TableAlias() in .Join() and .From() methods. Can you provide the typed SQL Expression you’re using?

3

What about the JoinAlias present in .Select() method? I use them every time I use a JoinAlias. Should I replace them too?

4

This is my case:

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")
        });

The query crashed with: The multi-part identifier "LRDPROFILOANALISI.DANALISIID" could not be bound..

This is the SQL Statement with the error marked:

SELECT DISTINCT
       dprofana."IDDPROFILOANALISI" AS Id
     , "LRDANALISI"."IDDANALISI" AS "AnalisiId"
     , "LRDANALISI"."CODICE"
     , "LRDANALISI"."DESCRIZIONE"
     , c."IDDCONTENITORE" AS ContenitoreId
     , c."CODICE" AS ContenitoreCodice
     , c."DESCRIZIONE" AS ContenitoreDescrizione
     , "LRDANALISI"."ORDINE"
     , dprofana."VERSIONERECORD" AS VersioneRecord
  FROM "LRDANALISI"
       INNER JOIN "LRDCONTENITORI" AS "c" ON("LRDANALISI"."DCONTENITOREID" = "c"."IDDCONTENITORE")
       INNER JOIN "LRDPROFILOANALISI" AS "dprofana" ON("LRDANALISI"."IDDANALISI" = "LRDPROFILOANALISI"."DANALISIID") -- error here
  WHERE( dprofana."DPROFILOANALISIID" IS NULL
       )
ORDER BY "LRDANALISI"."ORDINE";

By the way, the query still contains missing SQL aliases that results in missing bindings after query execution, with no error from OrmLite. We are full of cases where columns does not bind at all with the anonymous object. We may need to talk about them in a new thread.

5

I don’t have these classes in OrmLite’s LRAIssues.cs to be able to produce a repro, please submit a PR that includes these classes or provide a stand-alone repro I can run locally.

6

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

It contains the repro for both problems.

7

This issue should be resolved from this commit which is now available from the latest v5.4.1 on MyGet.

FYI you can also use db.TableAlias() to add an alias on the main table, e.g:

var q = db.From<LRDAnalisi>(db.TableAlias("dana"))
1 Like
8

Oh nice! I didn’t know that i could alias the main table.

Thanks!