To be honest I'm not that hot at the SQL...
OK, the inheritance is something like this, and it's Target-Per-Entity:
User is a subclass of History
UserSecurity is a subclass of SecurityHistory
Naming is t_user, t_history, t_user_security and t_security_history respectively on the database side, and fields are camelCased. Every table has an 'id' field which is the primary key.
UserSecurity.PUserId is a foreign key to User.Id.
Something like this is probably required:
SELECT TOP 1 t_security_history.securityPermission FROM t_user, t_security_history
INNER JOIN t_user_security ON t_user_security.id = t_security_history.id
WHERE
(
t_user.id = '00000000000000000000000000000001'
AND t_security_history.securityPermission = @Permission
AND t_user_security.pUserId = t_user.id
AND t_security_history.isCurrent = @IsCurrent
)
The redundant use of t_user.id is a result of the modular way the security library builds the query; I figured it'd get optimised out at the database side. When the security library and its queries become more complex, such short cuts will be necessary to keep the code maintainable.
With my previous query attempts, LLBLGen was not generating the INNER JOIN. Do I have to explicitly specify the relations to use? Since these relations are part of the inheritance structure, shouldn't they be inserted automagically by the DQE?
Or is this because IPredicateExpression does not determine joins, and I need to use RelationPredicateBucket for that?