JoinAlias - bug

ServiceStack.OrmLite
1

I found this while trying to left join a table to my main table twice.

var q = db.From()
.LeftJoin<CASE_HISTORY>((c, ch1) => c.Id = ch1.CASE_HISTORY_ID, db.JoinAlias(“created”))
.LeftJoin<CASE_HISTORY>((c, ch2) => c.Id = ch2.CASE_HISTORY_ID, db.JoinAlias(“updated”))
.Select("*");

var result = db.SelectMulti<CASES, CASE_HISTORY, CASE_HISTORY)(q, new [] { “CASES.", "created.”, “updated.*” });

This, unfortunately won’t work since the table CASE_HISTORY has the column CASE_HISTORY_ID, and the JoinAlias will replace both the tablename AND the columnname with “created” and “updated”, resulting in a FromExpression looking like this
FROM CASES LEFT JOIN CASE_HISTORY created ON (CASES.Id = created.created_ID) LEFT JOIN CASE_HISTORY updated ON (CASES.Id = updated.updated_ID)

Of course the table doesn’t have a column name updated_ID or created_ID, it’s named CASE_HISTORY_ID, but the JoinAlias replaces the column name with the alias.

Didn’t know where I should report this other than here.

Using OrmLite 5.4.0 and in this case connecting to an Oracle DB.

2

Can you switch to using db.TableAlias() which is the successor of JoinAlias(). It’s available in the latest v5.4.1 that’s now available on MyGet.

3

Hello again and thank you for a quick reply

Using TableAlias instead of JoinAlias fixes the replacement issues of the columns. So far so good :smile:

However, it instead translates the query to
FROM CASES
LEFT JOIN CASE_HISTORY AS created ON (CASES.Id = created.CASE_HISTORY_ID)
LEFT JOIN CASE_HISTORY AS updated ON (CASES.Id = updated.CASE_HISTORY_ID)

In SQL this is fine, in Oracle it is not as the AS keyword is not supported and gives “ORA-00933: SQL command not properly ended”

4

Using “AS” join aliases should be removed from this commit, this change is available from the latest v5.4.1 that’s now on MyGet.

As you previously had v5.4.1 installed you will need to clear your NuGet packages cache in order to download the latest v5.4.1 packages.

5

Works like a charm now.

Great work.

1 Like