Sql.In doesn't add table prefix

ServiceStack.OrmLite
1

Observe the SQL doesn’t prefix “Id” with table name.

For reference:

SQL: SELECT "UserAuth".*, 0 EOT, "UserAuthRole".*, 0 EOT
FROM "UserAuth" INNER JOIN "UserAuthRole" ON ("UserAuth"."Id" = "UserAuthRole"."UserAuthId")
WHERE "Id" IN (SELECT "UserAuthId"
FROM "UserAuthRole"
GROUP BY "UserAuthId"
HAVING count(1) = @0 )
PARAMS: @0=0
SQL logic error
ambiguous column name: Id

Gist here:

https://gist.cafe/d7b5af41c61697fc83601136446a5872

What I’m trying to do is actually finding all users who have all the roles and permissions queried for. Those will be added to the roles/permissions variables, but perhaps not relevant for the problem at hand. I realize that in Sqlite the query could have been written differently, no need for Sql.In, but it has to work with Sql Server as well.

2

You should always declare your joins first, i.e. after From<T>.

1 Like
3

This little trick is a workaround:

var outerQ = db.From<UserAuth>(db.TableAlias("UserAuth"))
                .Where(UserAuth => Sql.In(UserAuth.Id, q))
                .Join<UserAuthRole>((u, r) => u.Id == r.UserAuthId);

Note that I aliased the table to its real name, or else the SelectMulti didn’t work.

4

Thanks, that works.

var outerQ = db.From<UserAuth>()        
        .Join<UserAuthRole>((u, r) => u.Id == r.UserAuthId)
        .Where(u => Sql.In(u.Id, q));

creates the correct SQL. Earlier the Where() came before the Join().

1 Like