Hi All, I want to translate this SQL, where there's no relation between T_Client and T_ClientProcess.

    
 //delete  T_UserRight 
      //   where not exists (Select userRightId
      //                       FROM T_UserRight b, T_USER, T_Client, T_ClientProcess 
      //                        Where T_UserRight.userRightId = b.userRightId
      //                          and b.userId = T_User.userId 
      //                          and T_Client.clientId = T_User.clientId
      //                          and T_Client.clientId = T_ClientProcess.ClientId )

I've come up with this

      DynamicRelation dr = new DynamicRelation(EntityType.TUserRightEntity, JoinHint.Right, EntityType.TUserRightEntity, "r1", "r2", TUserRightFields.UserRightId.SetObjectAlias("r1") == TUserRightFields.UserRightId.SetObjectAlias("r2"));
      DynamicRelation drClientProcess = new DynamicRelation(EntityType.TClientEntity, JoinHint.Inner, EntityType.TClientProcessEntity, String.Empty, String.Empty, TClientFields.ClientId == TClientProcessFields.ClientId);
      IRelationPredicateBucket bucket = new RelationPredicateBucket();
      bucket.Relations.Add(dr);
      bucket.Relations.Add(drClientProcess);

      bucket.Relations.Add(TUserEntity.Relations.TClientEntityUsingClientId, JoinHint.Inner);
      bucket.Relations.Add(TUserRightEntity.Relations.TUserEntityUsingUserId, JoinHint.Inner);
      bucket.PredicateExpression.Add(TUserRightFields.UserRightId.SetObjectAlias("r1") == DBNull.Value);

      adapter.DeleteEntitiesDirectly("TUserRightEntity", bucket);

Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?

I've got this exception when I'm running this... Any clues someone?

Your code doesn't seem to implement the required SQL query. Please use FieldCompareSetPredicate to implement the Not Exists or better Not In predicate.

As I think the following query would be better:

DELETE T_UserRight 
WHERE userRightId NOT IN 
(SELECT userRightId
 FROM T_UserRight b, T_USER, T_Client, T_ClientProcess 
 WHERE b.userId = T_User.userId 
 AND T_Client.clientId = T_User.clientId
 AND T_Client.clientId = T_ClientProcess.ClientId )

The query I sent was the original query some dude write... I rewrite it as to drop not in or exists as:

DELETE T_UserRight
FROM   T_UserRight
       LEFT OUTER JOIN T_UserRight AS B
       INNER JOIN T_ClientProcess
       INNER JOIN T_Client
       ON    T_ClientProcess.clientId  = T_Client.clientId
       INNER JOIN T_User
       ON    T_Client.clientId            = T_User.clientId
       ON    B.userID                         = T_User.userID
       ON    T_UserRight.userRightId = B.userRightId
WHERE
       (
              b.userRightId IS NULL
       )

So the previous C# try to implement this query.

Thanx!

The query doesn't seem to be valid. Please rewrite and test your query and post again. Also, that the C# code could be accomplish with a serie of normal relations (specifying LEFT JOIN hint when applicable) and a simple predicate expression.