I have an entity, PrivilegeLevelMemberScope, with two fields called ScopeCode and ScopeValueCode (both strings) ScopeCode can be either "DIVISION" or "TEAM" ScopeValueCode will hold the PK of either the DIVISION or TEAM tables.

In the designer, I have added custom relationships (m:1) from PrivilegeLevelMemberScope to both the DIVISION and TEAM tables.

I need to prefetch for X the correct entity (Team or Division) depending on PrivilegeLevelMemberScope's ScopeCode and ScopeValueCode.

I am using this code for the prefetch:

var prefetchScope = prefetch.Add(PrivilegeLevelMemberEntity.PrefetchPathPrivilegeLevelMemberScopes);
prefetchScope.SubPath.Add(PrivilegeLevelMemberScopeEntity.PrefetchPathDivision);
prefetchScope.SubPath.Add(PrivilegeLevelMemberScopeEntity.PrefetchPathTeam);

This works fine unless of course the ScopeValueCode happens to be common to both Division and Team whereby it returns both Team and a Division entities.

For my current purpose this isn't a problem because I am using a custom property to return the relevant description for Division or Team and can work out which to use.

But I was wondering if I could somehow use the RelationFilter on the prefetch paths (or some other way) to bring back the Divisions where ScopeCode == "DIVISION" and Teams where ScopeCode == "TEAM"?

Cheers Simon

The following should work:

IPredicateExpression filter = new PredicateExpression();
filter.Add(PrivilegeLevelMemberScopeFields.Type == "TEAM");
prefetchPath.Add(PrivilegeLevelMemberEntity.PrefetchPathPrivilegeLevelMemberScopes, 0, filter);

goose wrote:

The following should work:

IPredicateExpression filter = new PredicateExpression();
filter.Add(PrivilegeLevelMemberScopeFields.Type == "TEAM");
prefetchPath.Add(PrivilegeLevelMemberEntity.PrefetchPathPrivilegeLevelMemberScopes, 0, filter);

This just filters PrivilegeLevelMemberEntitys which isn't what I need to do.

I want to bring back all PrivilegeLevelMemberEntitys but only the Division OR Team property should be set depending on the value of PrivilegeScopeCode. What is currently happening is that when PrivilegeScopeCode == "China" (for example) which is a PK in both the Team and Division tables, then the PrivilegeLevelMemberEntity gets both a Team and a Division entity set on its properties (a Team which is correct and a Division because it was prefetched for another entity).

I have optimized the prefetch paths so that only the relevant Teams and/or Divisions are prefetched with this code:

var prefetchScope = prefetch.Add(PrivilegeLevelMemberEntity.PrefetchPathPrivilegeLevelMemberScopes); var prefetchDivision = prefetchScope.SubPath.Add(PrivilegeLevelMemberScopeEntity.PrefetchPathDivision); var prefetchTeam = prefetchScope.SubPath.Add(PrivilegeLevelMemberScopeEntity.PrefetchPathTeam);

// Bring back only the Divisions actually used prefetchDivision.Filter.Add(PrivilegeLevelMemberScopeFields.PrivilegeScopeCode == PrivilegeScopeCodes.Division.Value); prefetchDivision.FilterRelations.Add(PrivilegeLevelMemberScopeEntity.Relations.DivisionEntityUsingPrivilegeScopeValueCode);

// Bring back only the Teams actually used prefetchTeam.Filter.Add(PrivilegeLevelMemberScopeFields.PrivilegeScopeCode == PrivilegeScopeCodes.Team.Value); prefetchTeam.FilterRelations.Add(PrivilegeLevelMemberScopeEntity.Relations.TeamEntityUsingPrivilegeScopeValueCode);

this produces this query (for Team): DECLARE @Code1 VarChar(10); SET @Code1='USA' DECLARE @Code2 VarChar(10); SET @Code2='SGP' DECLARE @Code3 VarChar(10); SET @Code3='X' DECLARE @Code4 VarChar(10); SET @Code4='BPA' DECLARE @Code5 VarChar(10); SET @Code5='UK' DECLARE @Code6 VarChar(10); SET @Code6='LNG' DECLARE @Code7 VarChar(10); SET @Code7='EM' DECLARE @Code8 VarChar(10); SET @Code8='OILUK' DECLARE @Code9 VarChar(10); SET @Code9='CHINA' DECLARE @Code10 VarChar(10); SET @Code10='SA' DECLARE @PrivilegeScopeCode11 VarChar(20); SET @PrivilegeScopeCode11='TEAM'

SELECT DISTINCT [dbo].[RD_TEAM].[team_cd] AS [Code], [dbo].[RD_TEAM].[division_cd] AS [DivisionCode], [dbo].[RD_TEAM].[team_nm] AS [Name], [dbo].[RD_TEAM].[commercial_user_id] AS [CommercialUserID], [dbo].[RD_TEAM].[operator_user_id] AS [OperatorUserID], [dbo].[RD_TEAM].[control_user_id] AS [ControlUserID], [dbo].[RD_TEAM].[default_bench_cd] AS [DefaultBenchCode] FROM ( [dbo].[RD_TEAM]
INNER JOIN [dbo].[OSS_PRIVILEGE_LEVEL_MEMBER_SCOPE] ON [dbo].[RD_TEAM].[team_cd]=[dbo].[OSS_PRIVILEGE_LEVEL_MEMBER_SCOPE].[privilege_scope_value_cd]) WHERE ( [dbo].[RD_TEAM].[team_cd] IN (@Code1, @Code2, @Code3, @Code4, @Code5, @Code6, @Code7, @Code8, @Code9, @Code10) AND ( [dbo].[OSS_PRIVILEGE_LEVEL_MEMBER_SCOPE].[privilege_scope_cd] = @PrivilegeScopeCode11))

so @Code1 to @Code9 contain the complete set of ScopeValues (which may correspond to Division, Teams or both) and the additional filter returns only those which have a ScopeCode of "Team" and a similar query will be generated for Division.

All this is good since all of the needed prefetched child entities are now in memory. No problem with what child entities to prefetch, its just how they get related after fetching.

At this point, I understand that LLBLGen will attached the prefetched TeamEntities onto each PrivilegeLevelMemberScopeEntity where TeamEntity.Code == PrivilegeLevelMemberScopeEntity.PrivilegeScopeValue and the prefetched DivisionEntities onto each PrivilegeLevelMemberScopeEntity where DivisionEntity.Code == PrivilegeLevelMemberScopeEntity.PrivilegeScopeValue.

But what I actually woudl like it to do (for Team) is attach where TeamEntity.Code == PrivilegeLevelMemberScopeEntity.PrivilegeScopeValue & PrivilegeLevelMemberScopeEntity.PrivilegeScopeCode == "TEAM" and (for Division) DivisionEntity.Code == PrivilegeLevelMemberScopeEntity.PrivilegeScopeValue & PrivilegeLevelMemberScopeEntity.PrivilegeScopeCode == "DIVISION"

otherwise PrivilegeLevelMemberScopeEntity can end up with both Team and Division entities set.

Is this actually possible to do in LLBLGenPro?

Cheers Simon

Your solution does work, doesn't it?

I have an entity, PrivilegeLevelMemberScope, with two fields called ScopeCode and ScopeValueCode (both strings) ScopeCode can be either "DIVISION" or "TEAM" ScopeValueCode will hold the PK of either the DIVISION or TEAM tables.

Anyway the above scenario is better realized using Inheritance.

You should create a TaregtPerHierarchy Inheritance hierarchy on the PrivilegeLevelMemberScope table, using the ScopeCode as the discriminator field. ScopeCode = "DIVISION" -> PrivilegeLevelMemberScopeDivision entity ScopeCode = "TEAM" -> PrivilegeLevelMemberScopeTeam entity

And then your custom relation should be defined between PrivilegeLevelMemberScopeTeam - Team and PrivilegeLevelMemberScopeDivision - Division

And then you would be able to specify prefetchPaths for different subTypes, which would only join to the table related to the subType.

Walaa wrote:

Your solution does work, doesn't it?

I have an entity, PrivilegeLevelMemberScope, with two fields called ScopeCode and ScopeValueCode (both strings) ScopeCode can be either "DIVISION" or "TEAM" ScopeValueCode will hold the PK of either the DIVISION or TEAM tables.

Anyway the above scenario is better realized using Inheritance.

You should create a TaregtPerHierarchy Inheritance hierarchy on the PrivilegeLevelMemberScope table, using the ScopeCode as the discriminator field. ScopeCode = "DIVISION" -> PrivilegeLevelMemberScopeDivision entity ScopeCode = "TEAM" -> PrivilegeLevelMemberScopeTeam entity

And then your custom relation should be defined between PrivilegeLevelMemberScopeTeam - Team and PrivilegeLevelMemberScopeDivision - Division

And then you would be able to specify prefetchPaths for different subTypes, which would only join to the table related to the subType.

I really must look into this inheritance stuff - our legacy database doesn't implement inheritance by design at least but If I understand your reply, we are using inheritance 'by accident' and only the LLBLGen project needs to define this - no changes at all to the database??

Cheers Simon

Exactly. For the TargetPerHierarchy Inheritance (which is all the entities are based on one target database table), you don't need to touch the database. Just some clicks in the LLBLGen Pro Designer.

Walaa wrote:

Exactly. For the TargetPerHierarchy Inheritance (which is all the entities are based on one target database table), you don't need to touch the database. Just some clicks in the LLBLGen Pro Designer.

Bloody hell! This is good!!!!!!!

My prefetch code is down to:

var prefetchScope = prefetch.Add(PrivilegeLevelMemberEntity.PrefetchPathPrivilegeLevelMemberScopes);
prefetchScope.SubPath.Add(PrivilegeLevelMemberScopeDivisionEntity.PrefetchPathDivision);
prefetchScope.SubPath.Add(PrivilegeLevelMemberScopeTeamEntity.PrefetchPathTeam);

Exactly the right things brought back in the right place and generated queries that look fully optimized!

I still don't know exactly how LLCoolJ knows what to do by me just adding the two SubPaths but it works! Thanks!

Couple of questions:- 1) I don't know what I should specify for the Discriminator Value for PrivilegeLevelMemberScope itself - I just stuck in a "Q" for now.

2) If I make PrivilegeLevelMemberScope abstract, I still seem to need a Discriminator Value for it - why is this?

3) The docs mention that "Discriminator fields shouldn't be foreign key fields". It this case it is since we have a reference table (with all of two rows in it) to limit the allowed values of PrivilegeScopeCode. Since we don't need to change the discriminator column, I assume this is OK?

Cheers Simon

1) I don't know what I should specify for the Discriminator Value for PrivilegeLevelMemberScope itself - I just stuck in a "Q" for now.

Anything other than "Divison" or "Team" would do.

2) If I make PrivilegeLevelMemberScope abstract, I still seem to need a Discriminator Value for it - why is this?

From docs:

An abstract entity means that you can have instances of that entity in multi-entity polymorphic fetches but you can't instantiate an entity instance in code by using its constructor.

The same question was asked here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=11150

3) The docs mention that "Discriminator fields shouldn't be foreign key fields". It this case it is since we have a reference table (with all of two rows in it) to limit the allowed values of PrivilegeScopeCode. Since we don't need to change the discriminator column, I assume this is OK?

I think this would be OK too.