Sql.In doesn't add table prefix

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:


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.

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

1 Like

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.

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