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?