SQL Server 2008 .Net 4.0 C# LLBLGen version 3.1 Final

Here is the SQL I am trying to create:

select distinct p.ProgramID, pl.Name, pl.Description, p.StartDate, p.EndDate, p.Active
from Program p
inner join ProgramLanguage pl on p.ProgramID = pl.ProgramID 
left join ProgramUserList u on p.ProgramID = u.ProgramID
left join (
    select prl.ProgramID, url.UserID 
        from ProgramRoleList prl  
    inner join Role r on prl.RoleID = r.RoleID
    inner join UserRoleList url on r.RoleID = url.RoleID
) RoleLookup on p.ProgramID = temp.ProgramID
where  
    p.Deleted = 0
    and (u.UserID = 'sys_admin' or RoleLookup .UserID = 'sys_admin') 
order by p.ProgramID

The part I struggling with is the derived table. I am getting close, but I'm just not quite grasping how this all fits together in LLBLGen (I've never used derived tables or dynamic relations before).

Here is the part of my code that I am working on:

ResultsetFields dtFields = new ResultsetFields(1);
dtFields.DefineField(ProgramRoleListFields.ProgramID, 0);
DerivedTableDefinition dt = new DerivedTableDefinition(dtFields, "RoleLookup");
PredicateExpression exp = new PredicateExpression(ProgramFields.ProgramID == ProgramRoleListFields.ProgramID.SetObjectAlias("RoleLookup"));
DynamicRelation rel = new DynamicRelation(dt, JoinHint.Left, EntityType.ProgramEntity, string.Empty, exp);
bucket.Relations.Add(rel);

which generates:

SELECT DISTINCT TOP 50 [dbo].[Program].[ProgramID], [dbo].[ProgramLanguage].[Name], [dbo].[ProgramLanguage].[Description], [dbo].[Program].[StartDate], [dbo].[Program].[EndDate], [dbo].[Program].[Active] 
FROM ((( [dbo].[Program]  
INNER JOIN [dbo].[ProgramLanguage]  ON  [dbo].[Program].[ProgramID]=[dbo].[ProgramLanguage].[ProgramID]) 
LEFT JOIN [dbo].[ProgramUserList]  ON  [dbo].[Program].[ProgramID]=[dbo].[ProgramUserList].[ProgramID]) 
LEFT JOIN (
            SELECT prl.[ProgramID]
            FROM [ProgramRoleList] prl
    ) [LPA_r1] ON ([dbo].[Program].[ProgramID] = [LPA_r1].[ProgramID])) 
WHERE [dbo].[Program].[Deleted] = 0 
    AND [dbo].[ProgramLanguage].[LanguageKey] = 1
ORDER BY Program.ProgramID ASC

All I need is to know how to add the two joins to the derived table. Can you please point me in the right direction?

jg wrote:

All I need is to know how to add the two joins to the derived table. Can you please point me in the right direction?

There is a DerivedTableDefinition constructor overload that receives a RelationCollection, also a PredicateExpression or a GroupBy if you need them. An approximate code would be:

// BUILD DERIVED TABLE

// fields
ResultsetFields dtFields = new ResultsetFields(1);
dtFields.DefineField(ProgramRoleListFields.ProgramID, 0);

// relations
RelationCollection relsInsideDerivedTable = new RelationCollection();
relsInsideDerivedTable.Add(ProgramRoleEntity.Relations.RoleEntityUsingRoleId);
relsInsideDerivedTable.Add(RoleEntity.Relations.UserRoleListEntityUsingRoleId);

DerivedTableDefinition dt = new DerivedTableDefinition(dtFields, "RoleLookup", null, relsInsideDerivedTable);

// BUILD DYNAMIC RELATION
PredicateExpression exp = new PredicateExpression(ProgramFields.ProgramID == ProgramRoleListFields.ProgramID.SetObjectAlias("RoleLookup"));
DynamicRelation rel = new DynamicRelation(dt, JoinHint.Left, EntityType.ProgramEntity, string.Empty, exp);

bucket.Relations.Add(rel);

Thanks! That was exactly what I needed.