Hello,

Using the search I've found this post on the forum: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=16664&HighLight=1

I've the same problem, and tried the proposed solution. Here is the code:

             /*
             * SELECT *  FROM EMPLOYEE e
             *   INNER JOIN (SELECT DISTINCT E_ID FROM VW_POPULATION WHERE USR_ID=70) vw ON vw.E_Id=e.E_Id
             */

            // first specify the elements in the derived table select : E_ID
            ResultsetFields innerJoinFields = new ResultsetFields(1);
            innerJoinFields.DefineField(VwPopulationFields.EId, 0);
            
            DerivedTableDefinition dtDefinition = new DerivedTableDefinition(
                innerJoinFields, "vw",
                new PredicateExpression(VwPopulationFields.UsrId == this.User.UsrId)
                );

            // then specify the relation.
            // derivedtable spec, join type, end entity type, alias first element, alias end element, on clause filter
            DynamicRelation rel = new DynamicRelation(
                dtDefinition, 
                JoinHint.Inner,
                EntityType.EmployeeEntity, 
                "e",                    
                EmployeeFields.EId.SetObjectAlias("e") == VwPopulationFields.EId.SetObjectAlias("vw")
            );

            filter.Relations.Add(rel);

(I can't make a distinct directly on the employee table because there are ntext fields)

And this code generate the query in the wrong way (it the text from DebugVisualizer):

(
    (
        SELECT VwPopulationEntity.[EId] FROM VwPopulationEntity WHERE 
        (
            VwPopulationEntity.[UsrId] = @UsrId1
        )
    )
    LPA_v1  INNER JOIN EmployeeEntity LPA_e2  ON  LPA_e2.[EId] = LPA_v1.[EId]
)

In fact I think there is no DynamicRelation CTor to create it in the correct order:

public DynamicRelation(DerivedTableDefinition leftOperand, JoinHint joinType, DerivedTableDefinition rightOperand, IPredicate onClause); public DynamicRelation(DerivedTableDefinition leftOperand, JoinHint joinType, EntityType rightOperand, string aliasRightOperand, IPredicate onClause); public DynamicRelation(EntityType leftOperand, JoinHint joinType, EntityType rightOperand, string aliasLeftOperand, string aliasRightOperand, IPredicate onClause);

I should have :

public DynamicRelation(EntityType leftOperand, JoinHint joinType, DerivedTableDefinition rightOperand, string aliasRightOperand, IPredicate onClause);

Is it possible to do it manually ? The property on the DynamicRelation object are read-only

Tx a lot

I think I've found:

I see that the DynamicRelation is a generated class ... so I've modified the class to (temporary) test if it works, adding a new CTor that call the base.InitClass with correct parameters.

If it works I'll modify the template. If you see any reason it'll not works, you can tells me so I'll not loose my time

Does it matter to which order the Join is generated? I don't think ordering will affect the query execution or the returned results.

Maybe you right, but I slept only 3 hours this night

Btw, I've my query but can't put the correct aliases ... will still try a bit before posting the code

Good luck and sorry to disappoint you.

Tx, itworks now: (it was an alias pb from the beginning, but as I'm stupid I thought the order was important )

            ResultsetFields innerJoinFields = new ResultsetFields(1);
            innerJoinFields.DefineField(VwPopulationFields.EId, 0);
            
            DerivedTableDefinition dtDefinition = new DerivedTableDefinition(
                innerJoinFields, "versionInner",
                new PredicateExpression(VwPopulationFields.UsrId == this.User.UsrId)
                );
            dtDefinition.AllowDuplicates = false; // Specify the 'DISTINCT'

            // then specify the relation.
            DynamicRelation rel = new DynamicRelation(
                dtDefinition, 
                JoinHint.Inner,
                EntityType.EmployeeEntity, string.Empty,
                EmployeeFields.EId == VwPopulationFields.EId.SetObjectAlias("versionInner")
            );
            
            filter.Relations.Add(rel);

Give this kind of query:

            SELECT ...
            FROM 
            ( 
                (
                    SELECT DISTINCT [DDF].[dbo].[VW_POPULATION].[E_ID] AS [EId] 
                    FROM [DDF].[dbo].[VW_POPULATION] (nolock) 
                    WHERE ( [DDF].[dbo].[VW_POPULATION].[USR_ID] = 70)
                ) [LPA_v1]  
                INNER JOIN [DDF].[dbo].[EMPLOYEE]  
                    ON  [DDF].[dbo].[EMPLOYEE].[E_ID] = [LPA_v1].[EId]
            ) 
            WHERE...

Tx for your help to wake me up