Self-referencing and inheritance in relations

Posts   
 
    
Posts: 20
Joined: 02-Apr-2004
# Posted on: 10-Jan-2014 03:54:23   

Environment

LLBLGen Pro 4.0 Framework: LLBLGen Pro Runtime Framework Template group: Adapter Database: Postgresql 9.2

Background

Four database tables are modelled in LLBLGen Pro as a TargetPerEntity inheritance hierarchy.

Component is the base table:
  • Component.ComponentId is the primary key column.
  • Component.ParentId is a foreign key column which references another record on the same table. The Site table inherits from the Component table: Site.ComponentId = Component.ComponentId The Panel table inherits from the Component table: Panel.ComponentId = Component.ComponentId The Loop table inherits from the Component table: Loop.ComponentId = Component.ComponentId

The following relationships between the four tables are enforced by the application that updates the database:

A Loop's parent is always a Panel, i.e.
  • ComponentId of Loop = ComponentId of Loop's Component
  • ParentId of Loop's Component = ComponentId of Panel's Component = ComponentId of Panel A Panel's parent is always a Site, i.e.
  • ComponentId of Panel = ComponentId of Panel's Component
  • ParentId of Panel's Component = ComponentId of Site's Component = ComponentId of Site So one Site has many Panels and one Panel has many Loops.

Objective

I want to populate an EntityCollection<LoopEntity> with all the Loops that belong to a specified Site. I know the Site's ComponentId: 2 in the example below. The following SQL does this. (For simplicity, I've omitted some columns from the SELECT clause)

SELECT
    loopComponent.ComponentId,
    loopComponent.ParentId,
    loopComponent.Name,
    Loop.LoopNo,
FROM Loop
JOIN Component AS loopComponent
    ON loopComponent.ComponentId = Loop.ComponentId
JOIN Component AS panelComponent
    ON panelComponent.ComponentId = loopComponent.ParentId
WHERE panelComponent.ParentId = 2

Notice that there is no need to access the Site table: Because I know that a Panel's parent is always a Site, I can just match the known Site.ComponentId (2 in the example) to the ParentId of the Panel's Component.

If I have to, I can have my application directly execute the above SQL and use the data returned to populate the EntityCollection<LoopEntity>. But I would rather use an LLBLGen Pro Runtime Framework adapter, if I can can get it working.

I suspect that it is the combination of self-referencing and inheritance in relations that is at the root of the problems I have been having. Everything I have tried populates the EntityCollection<LoopEntity> with zero LoopEntitys. (I expect 22 with my current test data).

Here is one the several variations I have tried:

var loops = new EntityCollection<LoopEntity>();
var bucket = new RelationPredicateBucket();
bucket.Relations.Add(new EntityRelation(
    LoopFields.ParentId,
    PanelFields.ComponentId,
    RelationType.ManyToOne));
bucket.PredicateExpression.Add(PanelFields.ParentId == 2);
using (var adapter = new DataAccessAdapter()) {
    adapter.FetchEntityCollection(loops, bucket);
}

I used a RelationPredicateBucket Visualizer in Visual Studio to diagnose the SQL parts that would be generated for the RelationPredicateBucket that is called bucket in the above code fragment. (Sorry, I don't know how to get the SQL for the whole query, though I would like to learn how.)

Here is the Pseudo SQL for the RelationCollection part:

(
    (
            ComponentEntity  INNER JOIN PanelEntity  ON  ComponentEntity.ParentId=PanelEntity.ComponentId
    )
    INNER JOIN LoopEntity  ON  ComponentEntity.ComponentId=LoopEntity.ComponentId
)

That looks right.

Here is the Pseudo SQL for the PredicateExpression part:

(
    ComponentEntity.[ParentId] = ParentIdp1 AND 
    (
        LoopEntity.[ComponentId] IS NOT NULL
    )
)

Parameters:
    ParentIdp1 = 2

That is ambiguous. I cannot tell by looking at it whether "ComponentEntity.[ParentId]" refer to the Loop's Component or the Panel's Component. The test result shows that it must refer to the Loop's Component, which is not what is required. In other words the generated SQL is looking for a Loop's parent Panel with ComponentId 2 (none such exists on the database) when it should be looking for a Panel's parent Site with ComponentId 2. That is why the result set contains zero Loops.

I would appreciate any suggestions.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 10-Jan-2014 07:34:48   

If you use Panel on the relationship, it will add the relation to Component on the SQL.

Try to make the relation with Component.ParentId -> Component.ParentId, and use the overload that passes an alias (i.e. "parent"), then when you filter, apply that alias. Aprox:

bucket.Relations.Add(new EntityRelation(
    ComponentFields.ParentId,
    ComponentFields.ComponentId,
    RelationType.ManyToOne), "parent");
bucket.PredicateExpression.Add(ComponentFields.ParentId.SetObjectAlias("parent") == 2);
David Elizondo | LLBLGen Support Team
Posts: 20
Joined: 02-Apr-2004
# Posted on: 12-Jan-2014 04:03:06   

Thanks, David! I just had to make one adjustment to your suggested approximate code and it worked:

bucket.Relations.Add(new EntityRelation(
    ComponentFields.ComponentId,
    ComponentFields.ParentId,
    RelationType.ManyToOne), "parent");
bucket.PredicateExpression.Add(ComponentFields.ParentId.SetObjectAlias("parent") == 2);

Cheers, Simon