Hi

I'm having problems translating the following sql statement in LLBLGen terms:

select C.CODE as Code, C.LIBELLE_CONTRAT 'Libellé du contrat',
    G.RAISON_SOCIALE as Groupe,
    S.RAISON_SOCIALE as Société, 
                     TLC1.LIBELLE as 'Niveau 1',
                     TLC2.LIBELLE as 'Niveau 2',
                     TLC3.LIBELLE as 'Niveau 3'
from (((((((CONTRAT as C 
    left outer join SOCIETE as S on S.REFERENCE_SOCIETE = C.REFERENCE_NOEUD_HIERARCHIQUE)
    left outer join SOCIETE as G on G.REFERENCE_SOCIETE = S.REFERENCE_GROUPE_SOCIETE or (G.REFERENCE_SOCIETE = C.REFERENCE_NOEUD_HIERARCHIQUE and S.INDICATEUR_GROUPE=1))
                            left outer join NIVEAU_3 as Niv3 on Niv3.REFERENCE_NIVEAU_2=C.REFERENCE_NIVEAU_HIERARCHIQUE)
                            left outer join NOEUD_HIERARCHIQUE as NH3 on Niv3.REFERENCE_NIVEAU_3=NH3.REFERENCE_NOEUD_HIERARCHIQUE
                            left outer join TRADUCTION_LIBELLE_CONTROLE as TLC3 on NH3.REFERENCE_LIBELLE=TLC3.REFERENCE_LIBELLE)
                        left outer join NIVEAU_2 as Niv2 on Niv2.REFERENCE_NIVEAU_2=Niv3.REFERENCE_NIVEAU_2 or Niv2.REFERENCE_NIVEAU_1=C.REFERENCE_NIVEAU_HIERARCHIQUE)
                        left outer join NOEUD_HIERARCHIQUE as NH2 on Niv2.REFERENCE_NIVEAU_2=NH2.REFERENCE_NOEUD_HIERARCHIQUE
                        left outer join TRADUCTION_LIBELLE_CONTROLE as TLC2 on NH2.REFERENCE_LIBELLE=TLC2.REFERENCE_LIBELLE)
                    left outer join NIVEAU_1 as Niv1 on Niv1.REFERENCE_NIVEAU_1=Niv2.REFERENCE_NIVEAU_1 or Niv1.SOCIETE_REFERENCE_NOEUD=C.REFERENCE_NIVEAU_HIERARCHIQUE)
                    left outer join NOEUD_HIERARCHIQUE as NH1 on Niv1.REFERENCE_NIVEAU_1=NH1.REFERENCE_NOEUD_HIERARCHIQUE
                    left outer join TRADUCTION_LIBELLE_CONTROLE as TLC1 on NH1.REFERENCE_LIBELLE=TLC1.REFERENCE_LIBELLE;

More precisely, the problem comes from the custom joins: I thought adding custom filters to the corresponding relations would make it. I've created PExpressions with PCompareExpression predicates, the expressions of which are custom instances of EntityField2 (using adapter) with object aliases updated by hand. However, as I've understood from the ToQueryText function in the RelationCollection class, the join filter corresponding to the foreign keys defining the relation is hard written, and then the custom filter is only there to add extra statements after an additional "AND".

So here is the kind of the statement which I get:

LEFT JOIN [Acolea].[dbo].[SOCIETE] GROUPE ON  GROUPE.[REFERENCE_SOCIETE]=SOCIETE.[REFERENCE_GROUPE_SOCIETE] AND ( ( GROUPE.[REFERENCE_SOCIETE] = SOCIETE.[REFERENCE_GROUPE_SOCIETE]) Or ( GROUPE.[REFERENCE_SOCIETE] = SOCIETE.[REFERENCE_SOCIETE] And SOCIETE.[INDICATEUR_GROUPE] = 1)))

whereas I was looking for:

LEFT JOIN [Acolea].[dbo].[SOCIETE] GROUPE ON ( ( GROUPE.[REFERENCE_SOCIETE] = SOCIETE.[REFERENCE_GROUPE_SOCIETE]) Or ( GROUPE.[REFERENCE_SOCIETE] = SOCIETE.[REFERENCE_SOCIETE] And SOCIETE.[INDICATEUR_GROUPE] = 1)))

Any idea on how to define a purely customized join? Or if not possible on how to reformulate my statement to stick to LLBLGen limitations?

Thanks in advance.

This is not possible at the moment, the idea with the current custom filters is that you want to add additional filters (predicates) to the ON clause. What you want is a different ON clause altogether. You could try to move the filter to the WHERE clause, though this can be impossible, as sometimes this gives different results.

Ok thanks Frans, I'll go with that.

Probably not much code to add though, to allow for custom "on" clauses. Maybe just a Boolean in the EntityRelation class resulting in RelationCollection ToQueryText() stripping off the first part of the clause to keep only the custom filter.

Anyway, good luck with the incoming version and thanks for the answer as quick as always.