Hi

I have 3 tables: USER USER_ROLE ROLE

USER.ID and ROLE.ID are PKs in their respective tables and FKs and PKs in USER_ROLE. A user does not have to have a role so I guess this is a weak relationship in LLBLGen parlance. I've created a TypedList to query these tables but it nevers returns users with no roles. Looking at the SQL it's because there's an INNER JOIN between USER_ROLE and ROLE. I'm reproducing part of the SQL below:

SELECT DISTINCT [dbo].[USER].[ID], [dbo].[USER].[FIRSTNAME], [dbo].[USER].[SURNAME], [dbo].[ROLE].[ID] AS [ROLE_ID], [dbo].[ROLE].[DESCRIPTION] AS [ROLE_DESCRIPTION], FROM [dbo].[USER] LEFT JOIN [dbo].[USER_ROLE] ON [dbo].[USER].[ID]=[dbo].[USER_ROLE].[USER_ID] INNER JOIN [dbo].[ROLE] ON [dbo].[ROLE].[ID]=[dbo].[USER_ROLE].[ROLE_ID] WHERE ....

The only reason I need to add the ROLE table in the query is because I need the ROLE.DESCRIPTION. Is there anyway I can force LLBLGen to use a LEFT join between USER_ROLE and ROLE?

Cheers S

Set the TypeLists's ObeyWeakRelations property to true, this will cause left joins when the relation is weak.

I have done when I create it:

USERTypedList users = new USERTypedList(true);

This is caused by the fact that the User_Role -> Role relation is strong from the User_Role POV, and because User_Role is added first, this is the way the query generator looks at it. It's not possible at the moment to specify this specific behaviour.

If you're interested in all users which have no roles, you can leave out Role, and query with a filter on User_Role.RoleID IS NULL.

A workaround then can be: use 2 typed lists: one with all users with roles, and one with users without a role. The users in the last one will not have a role description anyway in the first. I admit, it is a bit of a pain for now...

I think I found a solution, however it will be implemented in the new 1.0.2003.3 betas.

When A, B and C are in the join list, and this is the case:

SELECT * FROM A LEFT JOIN B ON A.foo = B.foo

and C is added to the join list via a relation with B, it will be B which will 'drive' the join, thus will control which rows of C which will be added. Now, if B is added via a WEAK relation, (in our example this is the case), C should also be added as a weak relation, no matter what the relation is between B and C from the POV of B. This will then result in the LEFT JOIN.

No-one would be interested in an always strong relation between B and C, because B will already introduce NULLs, which are requested due to the ObeyWeakRelations setting. If the caller didn't want any NULLs in the B fields, s/he would not have set ObeyWeakRelations to true, and an INNER JOIN would have been the right option.

A repro with Northwind: Customer - Order - Employee.

I've decided to leave out ROLE from the TypedList as suggested and where I need to access the descrption, I do this:

for(int i = 0; i < users.Rows.Count; i++) { if(!users[i].IsROLE_IDNull()) { lblRole.Text = new ROLEEntity(users[i].ROLE_ID).DESCRIPTION; } }

This is probably quite resource intensive as I'm going back to the database each time... Working with 2 typedlists was just too painful to contemplate as I would have had to union the resultsets from both to get the results I want...

Thanks for your help.

I think I found a solution, however it will be implemented in the new 1.0.2003.3 betas.

Cool. Will look forward to it

That's pretty intensive indeed.