Hajo,

I have User, Role and UserRole entities. There is m:n realation between User and Role and it's stored in UserRole. Now I am wondering how I can convert below piece of sql to LLBLGen predicates. Any idea?

Thanks

Select * From

(Select * From UserRole WHERE UserRole.UserId = '2') AS User1

INNER JOIN

(Select * From UserRole WHERE UserRole.UserId = '6') AS User2

ON User1.RoleId = User2.RoleId

;

Isn't your query the same as the following:

SELECT UR1.Name, ...
FROM UserRole UR1
INNER JOIN UserRole UR2
ON UR1.RoleID = UR2.RoleID
WHERE UR1.UserId  = 2
AND UR2.UserId  = 6

You will have to add the relation in the RelationPredicateBucket Relations collection And add your filters to the Filters collection in the same object.

And then pass your bucket to the fecth method.

Walaa wrote:

Isn't your query the same as the following:

SELECT UR1.Name, ...
FROM UserRole UR1
INNER JOIN UserRole UR2
ON UR1.RoleID = UR2.RoleID
WHERE UR1.UserId  = 2
AND UR2.UserId  = 6

You will have to add the relation in the RelationPredicateBucket Relations collection And add your filters to the Filters collection in the same object.

And then pass your bucket to the fecth method.

How can I add a relation between UserRole and UserRole?

Sorry to chime in, but this sounds like the same task I recently learned. You will need to use an alias. Check out this thread I started which is essentially the same problem, slightly different, but invloves writing an alias...

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5249