LLBLGen PRO, v.December 11th, 2015, adapter, MSSQL

Hi, I am fighting with TypedList and inheritance.

PARTY has subtype PERSON PARTYROLE has subtype WORKER

Relation PARTY : PARTYROLE is 1:N Relation PERSON : APPLICATIONUSER 1:N

I need to get from MSSQL WorkerLogins (something like ApplicationUser.UserID, Person.Name, Worker.PersonalNo) Looks simple to setup TypedList in designer.

Designer shows correct type relationships: ApplicationUser <- Person <- Worker

After while I can see in VB.NET that tlWorkerLogins.GetRelationInfo returns wrong info, because, there is missing Worker entity and its JOIN to PartyRole: ((( PartyEntity INNER JOIN PersonEntity ON PartyEntity.PartyId=PersonEntity.PartyId) INNER JOIN ApplicationUserEntity ON PersonEntity.PartyId=ApplicationUserEntity.PersonId) INNER JOIN PartyRoleEntity ON PersonEntity.PartyId=PartyRoleEntity.PartyId)

Anyway I run the code and then in SQL profiler I see following SQL query:

SELECT [LPA_L2].[PersonID] AS [PartyId], [LPA_L2].[PersonFullNameReverse], [LPA_L4].[WorkerID] AS [PartyRoleId], [FlexMARS_DEV].[App].[ApplicationUser].[UserID] AS [UserId], [LPA_L4].[WorkerKey] FROM (((( [FlexMARS_DEV].[Party].[Party] [LPA_L1] INNER JOIN [FlexMARS_DEV].[Party].[Person] [LPA_L2] ON [LPA_L1].[PartyID]=[LPA_L2].[PersonID]) INNER JOIN [FlexMARS_DEV].[App].[ApplicationUser] ON [LPA_L2].[PersonID]=[FlexMARS_DEV].[App].[ApplicationUser].[PersonID]) INNER JOIN [FlexMARS_DEV].[Party].[PartyRole] [LPA_L3] ON [LPA_L2].[PersonID]=[LPA_L3].[PartyID]) **LEFT **JOIN [FlexMARS_DEV].[Party].[Worker] [LPA_L4] ON [LPA_L3].[PartyRoleID]=[LPA_L4].[WorkerID])

Why there is LEFT JOIN between PartyRole and Worker? Worker is subtype of PartyRole, designer knows that well. There should be INNER JOIN

I tried to debug inside LLBL RelationCollection.cs at public RelationCollection PreprocessRelations() AliasLeftOperand "LPAA_PartyRoleEntity" AliasRightOperand "LPAA_WorkerEntity" JoinType Left

Please elaborate more on:

• Your view DDL
• The Typedlist description on your LLBLGen Project
• The .net code you run that generate such sql.
• Your LLBLGen version and runtime library version (http://www.llblgen.com/TinyForum/Threads.aspx?ForumID=10 )

Once again: LLBLGen PRO, v.December 11th, 2015, adapter, MSSQL 4.2.16.113

Into attachement I added 1/ sample DB backup

2/ full LLBL project "FlexMARS - TL tests.llblgenproj" I made there test groups "aaaApp" and "aaaParty" There is TypedList "aaaParty.AaaWorkerLogin"

3/ code is simple

Dim tlWorkerLogins as New TypedListClasses.AaaWorkerLoginTypedList
Using adapter As New DataAccessAdapter
         adapter.FetchTypedList(tlWorkerLogins)
End Using

Generated SQL is this (LEFT JOIN instead of INNER JOIN) SELECT [LPA_L4].[WorkerID] AS [PartyRoleId], [LPA_L4].[WorkerKey], [LPA_L2].[PersonFullNameReverse], [FlexTEST].[App].[ApplicationUser].[UserID] AS [UserId] FROM (((( [FlexTEST].[Party].[Party] [LPA_L1]
INNER JOIN [FlexTEST].[Party].[Person] [LPA_L2] ON [LPA_L1].[PartyID]=[LPA_L2].[PersonID]) INNER JOIN [FlexTEST].[Party].[PartyRole] [LPA_L3] ON [LPA_L2].[PersonID]=[LPA_L3].[PartyID]) INNER JOIN [FlexTEST].[App].[ApplicationUser] ON [LPA_L2].[PersonID]=[FlexTEST].[App].[ApplicationUser].[PersonID]) LEFT JOIN [FlexTEST].[Party].[Worker] [LPA_L4] ON [LPA_L3].[PartyRoleID]=[LPA_L4].[WorkerID])

But I expect to get this: SELECT [LPA_L4].[WorkerID] AS [PartyRoleId], [LPA_L4].[WorkerKey], [LPA_L2].[PersonFullNameReverse], [FlexTEST].[App].[ApplicationUser].[UserID] AS [UserId] FROM (((( [FlexTEST].[Party].[Party] [LPA_L1]
INNER JOIN [FlexTEST].[Party].[Person] [LPA_L2] ON [LPA_L1].[PartyID]=[LPA_L2].[PersonID]) INNER JOIN [FlexTEST].[Party].[PartyRole] [LPA_L3] ON [LPA_L2].[PersonID]=[LPA_L3].[PartyID]) INNER JOIN [FlexTEST].[App].[ApplicationUser] ON [LPA_L2].[PersonID]=[FlexTEST].[App].[ApplicationUser].[PersonID]) INNER JOIN [FlexTEST].[Party].[Worker] [LPA_L4] ON [LPA_L3].[PartyRoleID]=[LPA_L4].[WorkerID])

4/ It DOES matter the order in which I add entities into TypedList in designer. When I add first aaaParty.Worker1, then aaaParty.Person1, then aaaApp.ApplicationUser1 and run, then I see error: "Relation at index 1 doesn't cointan and entity already added to the FROM clause. Bad alias?" (RelationCollection.cs line 912)

I attached also this project modification as "FlexMARS - TL tests INDEX ERROR.llblgenproj", you can compare and find

We will look into it.

The left join is indeed odd.

By default subtypes are fetched using left joins, not inner joins, however here a worker field is in the projection so it should have added a type filter.

It might be it omits this type filter because in edge cases it might result in 0 rows (in the case of multiple types inherited from same supertype with a relationship between them), which isn't detectable at the level of join statement creation.

Looking into why this happens!

By default subtypes are fetched using left joins, not inner joins

But Person is subtype of Party and there is INNER JOIN (as expected) And also I think that subtype should be fetched by INNER JOIN

Say I have Party entity and subtype Person and another subtype Organization In simple case say I have these tables/fields: Party - PartyID - PartyName

subytype Person - PersonID (PK,FK)

subtype Organization - Organization (PK,FK)

When I create new Person, then new record is inserted into table Party, ID is fetched and fetch ID and new record is also inserted into into Person table Means subtype must have record in subytpe and supertype. Therefore INNER JOIN should be used, or LEFT JOIN + WHERE PersonID IS NOT NULL (but INNER JOIN is preffered way, right?)

Worker should be fetched with inner join indeed as the join is over worker. If the join was over its supertype PartyRole, a left join would be OK. So I'm not denying something's wrong here. It's just that there is an edge case where a type filter is supposed to be automatically added, but can't be emitted because it would mean another edge case will result in 0 rows, so the type filter has to be added manually. (a type filter is sufficient here, it would test whether Worker's fk field is null or not).

Subtypes are always fetched with left join if you fetch the supertype as siblings will otherwise create a 0-row resultset. Here, you fetch the subtype (worker) so that's not the case.

I tried a Linq query with the same structure:

using(var adapter = new DataAccessAdapter())
{
    var metaData = new LinqMetaData(adapter);
    var q = from p in metaData.Person1
            join a in metaData.ApplicationUser1 on p.PartyId equals a.PersonId
            join w in metaData.Worker1 on p.PartyId equals w.PartyId
            select new
                    {
                        w.PartyRoleId,
                        w.WorkerKey,
                        p.PersonFullNameReverse,
                        a.UserId,
                    };

    var l = q.ToList();
}

and it results in the proper query:

SELECT [LPA_L5].[WorkerID] AS [PartyRoleId],
       [LPA_L5].[WorkerKey],
       [LPA_L2].[PersonFullNameReverse],
       [LPA_L3].[UserID]   AS [UserId]
FROM   (((( [FlexTEST].[Party].[Party] [LPA_L1]
            INNER JOIN [FlexTEST].[Party].[Person] [LPA_L2]
                ON [LPA_L1].[PartyID] = [LPA_L2].[PersonID])
          INNER JOIN [FlexTEST].[App].[ApplicationUser] [LPA_L3]
              ON [LPA_L2].[PersonID] = [LPA_L3].[PersonID])
         INNER JOIN [FlexTEST].[Party].[PartyRole] [LPA_L4]
             ON [LPA_L2].[PersonID] = [LPA_L4].[PartyID])
        INNER JOIN [FlexTEST].[Party].[Worker] [LPA_L5]
            ON [LPA_L4].[PartyRoleID] = [LPA_L5].[WorkerID]) 

while the typed list indeed gives a left join as last join.

Looking into what the difference might be.

I also wrote:

4/ It DOES matter the order in which I add entities into TypedList in designer. When I add first aaaParty.Worker1, then aaaParty.Person1, then aaaApp.ApplicationUser1 and run, then I see error: "Relation at index 1 doesn't cointan and entity already added to the FROM clause. Bad alias?" (RelationCollection.cs line 912)

Looks to me problem in designer. Then there wrong join HINTS and those hists are then "just" processed by create query engine.

Btw there is an option in designer to change HINT, but not for subtype joins. That would solve this story

I think it's a limitation of the typed list 'old style' structure and the order in which the relationships are added. I'll first explain why, then what to do about it.

The relationships code is: (I aliased the entities here)

private void BuildRelationSet()
{
    _filterBucket.Relations.ObeyWeakRelations = base.ObeyWeakRelations;
    _filterBucket.Relations.Add(ApplicationUser1Entity.Relations.Person1EntityUsingPersonId, "A", "P", JoinHint.Inner);
    _filterBucket.Relations.Add(Person1Entity.Relations.PartyRole1EntityUsingPartyId, "P", "W", JoinHint.Inner);
    // __LLBLGENPRO_USER_CODE_REGION_START AdditionalRelations
    // __LLBLGENPRO_USER_CODE_REGION_END
    OnRelationSetBuilt(_filterBucket.Relations);
}

The second one is the problem: 'Worker' inherits the relationship from its supertype, 'PartyRole'. In theory, the relationship in the joins thus should be: Person1 - Worker. However, that relationship isn't in the generated code, as Worker inherits it from PartyRole: we don't generate properties in the Relations class for every subtype combination. The one which is, is the one which is the actual relationship: Person1 PartyRole1. This one is generated into the typedlist code.

This means that the join the code sees is Person1 - PartyRole1 and correctly defines it as an inner join. It then needs to join a subtype to it, and always does that using LEFT join. (As inner join would mean it could lead to empty resultset if 2 subtypes are joined. If PartyRole1 had more subtypes, they would all be joined here!)

So what to do about it?

One way to solve this is to add a filter on workerid to the typedlist filter:

adapter.FetchTypedList(tl, new PredicateExpression(Worker1Fields.PartyRoleId!=DBNull.Value));

Not ideal, but it makes the typedlist work again: it filters out subtypes of PartyRole which aren't Workers.

Changing the ordering of relationships doesn't solve it though:

private void BuildRelationSet()
{
    _filterBucket.Relations.ObeyWeakRelations = base.ObeyWeakRelations;
    _filterBucket.Relations.Add(Worker1Entity.Relations.Party1EntityUsingPartyId, "W", "P", JoinHint.Inner);
    _filterBucket.Relations.Add(ApplicationUser1Entity.Relations.Person1EntityUsingPersonId, "A", "P", JoinHint.Inner);
    // __LLBLGENPRO_USER_CODE_REGION_START AdditionalRelations
    // __LLBLGENPRO_USER_CODE_REGION_END
    OnRelationSetBuilt(_filterBucket.Relations);
}

Here I start with worker, then person, then applicationuser, but it has the same problem: the relationship defined should be Worker Person, but that would mean we need to generate all relationships with all subtypes, see above, so we define the relationship which actually is defined, namely Worker - Party. (actually PartyRole - Party, but Worker inherits that so we don't need to define additional code for this relationship here).

The next relationship is ApplicationUser - Person, but Person isn't in the join list, Party is. Even though Person is a subtype of Party, this can't be assigned automatically as they're not the same entity. And it fails with the exception you've seen.

If we generate the relationships differently, i.e. using DynamicRelation class instances:

_filterBucket.Relations.Add(new DynamicRelation(EntityType.Worker1Entity, JoinHint.Inner, EntityType.Person1Entity, "W", "P",
                            Worker1Fields.PartyId.Source("W").Equal(Person1Fields.PartyId.Source("P"))));
_filterBucket.Relations.Add(new DynamicRelation(EntityType.ApplicationUser1Entity, JoinHint.Inner, EntityType.Person1Entity, "A", "P",
                            ApplicationUser1Fields.PersonId.Source("A").Equal(Person1Fields.PartyId.Source("P"))));

Everything works again, as expected, as the join is defined between the actual entities. This is also what happens in queryspec and linq and why they work and the old style typedlist doesn't in this particular case.

Can you live with the type filter? The thing is that the old style typed lists are actually deprecated and we move users towards using poco typed lists using linq / queryspec: they're faster and result in less code, so if possible we don't want to alter the typedlist templates. If you say "no, I need this fixed", we'll look into changing the typedlist template to generate dynamicrelation instances instead. It's not said this is possible, as the typedlists template is in TDL, not lpt template syntax so it might be we don't have the statements available to emit the required predicates in all cases (as compound key relationships need additional logic).

Rosacek wrote:

I also wrote:

4/ It DOES matter the order in which I add entities into TypedList in designer. When I add first aaaParty.Worker1, then aaaParty.Person1, then aaaApp.ApplicationUser1 and run, then I see error: "Relation at index 1 doesn't cointan and entity already added to the FROM clause. Bad alias?" (RelationCollection.cs line 912)

Yes, see my reply above.

Looks to me problem in designer. Then there wrong join HINTS and those hists are then "just" processed by create query engine.

No, it's a limitation in the relationship code structure (the class/property system for the relationship objects has a limitation, see my previous post). The hints are correct, they apply to the wrong type though as teh supertype is in the relationship in the generated code, not the subtype as you would (logically) expect, due to the limitation.

Btw there is an option in designer to change HINT, but not for subtype joins. That would solve this story

No, that wouldn't fix it.

Thanks Otis for investigation!

First, I can live with type filter and later move to QuerySpecs if you plan to deprecate TypeLists. Means ticket is solved, thanks again.

It would be fine if you could give me simple example of the case when it may happen you "get empty resultset when joining two subtypes" ? I still cannot get the point why LEFT JOINs are used instead of INNER

Subtypes are exclusive, it is Person OR Organization, then they cannot share same PartyID and then INNER JOINS should work fine.

It's about fetching supertypes, and thus all subtypes: then the joins of the subtypes are using LEFT joins.

Supertype 'Super' and Subtypes 'Sub1' and 'Sub2', both subtypes of 'Super'.

If I want all Super instances, the joins of Sub1 and Sub2 are LEFT joins:

Super Left Join Sub1 ON ... Left Join Sub2 ON...

If they would be Inner joins, this would happen:

first the first join Super INNER Join Sub1 ON...

now, the resultset contains rows with pk/fk combination matching in both Super and Sub1.

The second join: INNER Join Sub2 ON...

now, this requires a matching pk-fk combination in the rows already in the join list and Sub2. But no row matches that criteria because there's no row in Super with a PK in both Sub1 and Sub2 if Sub1 and Sub2 are siblings.

so rule of thumb:

Joins towards hierarchy root: inner joins. Joins towards subtypes: left joins.