Hi there,

I am not sure how to go about this, and it's the one thing I cannot seem to find a solution to either here or in the reference guide, but here's the scenario:

• I have 3 tables in the database, one called 'Party', one called 'Organisation' and another called 'Individual'.
• The Party table contains the same primary key as Organisation and Individual (it's a unique ID/Guid). I have told the designer that Individual and Organisation are derived classes for Party (Party is the base, and this table is also the table that all other tables use for their FK to Individuals/Organisations).

The problem comes in where I need to perform a sort on some derived fields, being 'Individual.LastName' and 'Organisation.TradeName' (these are not on the Party table). I need to return a collection of all 'VariationApplicationEntity' instances related to 'Party', sorted by either TradeName or LastName, depending on the derived entity type.

Any ideas as to how to go about this? I am using SelfServicing, with LLBLGEN Pro 1.0.2005.1 Final using Oracle 9i as the database.

Here is a code snippet for the sort portion of the query which is not working:

VariationApplicationCollection applications = new VariationApplicationCollection();

IPredicateExpression whereExpression = new PredicateExpression();
whereExpression.Add(VcmsCaseAssignFields.AssignToUserId == userGuid);
whereExpression.Add(VcmsCaseAssignFields.StatusId != VariationStatus.ClosedStatuses);

IRelationCollection relationCollection = new RelationCollection();
relationCollection.Add(VcmsCaseEntity.Relations.VcmsCaseAssignEntityUsingCurrentAssignId);
relationCollection.Add(VcmsCaseEntity.Relations.PartyEntityUsingPartyId);
relationCollection.Add(VcmsCaseAssignEntity.Relations.VcmsCaseStatusEntityUsingStatusId);

ISortExpression sortExpression = new SortExpression();
sortExpression.Add(OrganisationFields.TradeName | SortOperator.Ascending);
sortExpression.Add(CitizenFields.Lastname | SortOperator.Ascending);
sortExpression.Add(CitizenFields.Firstname | SortOperator.Ascending);

// This fails
applications.GetMulti(whereExpression, 0, sortExpression, relationCollection);

The above code produces the following error (for obvious reasons):

An exception was caught during the execution of a retrieval query: ORA-00904: "VCMS"."CITIZEN"."FIRSTNAME": invalid identifier. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Thanks a stack! Brian Johnson.

An exception is thrown, because you are using fields (CitizenFields.Lastname) in the sort clause (order by clause) from a table that is not in the Query (not in the FROM / JOIN)

To join this table, add a relation to the corresponding entity (Citizen Entity).

Walaa wrote:

An exception is thrown, because you are using fields (CitizenFields.Lastname) in the sort clause (order by clause) from a table that is not in the Query (not in the FROM / JOIN)

To join this table, add a relation to the corresponding entity (Citizen Entity).

Thanks Walaa,

Since the CitizenEntity is a derived entity it does not have any relationship defined between itself and its base class and vice-versa (e.g. Party.Relations.CitizenEntityUsingPartyId does not exist, and neither does CitizenEntity.Relations.PartyEntityUsingCitizenId, or anything similar). LLBLGEN seems to 'remove' these relations by default on the derived classes?

Any other suggestions?

Cheers, Brian

But is there a relation between VariationApplicationEntity & CitizenEntity?

I'm not aware of the relations/ inheritance between all the entities types used in your code.

For example you are fetching a VariationApplication collection and you are sorting on CitizenFields, and I don't see the relation between them.

Please post your database structure for all the entities used in this query (VariationApplicationEntity, VcmsCaseAssignEntity, VcmsCaseEntity, PartyEntity, VcmsCaseStatusEntity, OrganisationEntity, CitizenEntity), and inheritance relations and whether they are target per entity or target per entity hierarchy.

Walaa wrote:

But is there a relation between VariationApplicationEntity & CitizenEntity?

I'm not aware of the relations/ inheritance between all the entities types used in your code.

For example you are fetching a VariationApplication collection and you are sorting on CitizenFields, and I don't see the relation between them.

Please post your database structure for all the entities used in this query (VariationApplicationEntity, VcmsCaseAssignEntity, VcmsCaseEntity, PartyEntity, VcmsCaseStatusEntity, OrganisationEntity, CitizenEntity), and inheritance relations and whether they are target per entity or target per entity hierarchy.

Thanks for the reply Walaa,

No, there are no direct relations between any entities and Citizen or Organisation. All these relations are in fact to the Party table. The party table has physical relations to Citizen and Organisation, but the designer generated code does not include these as the Citizen and Organisation were marked as derived classes of Party.

I am not sure how to attach images here, so I will use snippets of the generated Oracle DDL for these tables:

CREATE TABLE PARTY(
    PARTYID     CHAR(32)        NOT NULL,
    PARTYTYPE     NUMBER(4, 0)  NOT NULL
                   CONSTRAINT CC_PARTYTYPE CHECK (partyType IN (1,2)),
    CREATEDBY     VARCHAR2(20)  DEFAULT SYS_CONTEXT ('USERENV', 'SESSION_USER'),
    CREATEDDATE DATE            DEFAULT SYSDATE,
    UPDATEDBY     VARCHAR2(20),
    UPDATEDDATE DATE,
    CONSTRAINT PK_PARTY_ID PRIMARY KEY (PARTYID)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE CORE_TBSP
        STORAGE(INITIAL 64K
                MINEXTENTS 1
                MAXEXTENTS UNLIMITED
                PCTINCREASE 0
                BUFFER_POOL DEFAULT
                )
)

CREATE TABLE ORGANISATION(
    ORGID                   CHAR(32)         NOT NULL,
    TRADENAME               VARCHAR2(120)   NOT NULL,
    INDUSTRYSECTORCODE     VARCHAR2(4)    NOT NULL,
    LANGUAGE                 NUMBER(4, 0)    NOT NULL,
    CONTACTMETHOD           NUMBER(4, 0)     NOT NULL,
    OWNERSHIPTYPE           NUMBER(4, 0)     NOT NULL,
    REGISTRATIONNAME         VARCHAR2(120),
    PAYEREFERENCE           VARCHAR2(20),
    REGISTRATIONNUMBER     VARCHAR2(20),
    MUNICIPALITYID         NUMBER(4, 0),
    MAGISTERIALDISTRICTID   NUMBER(4, 0),
    DOLCENTREID           NUMBER(4, 0),
    CREATEDBY               VARCHAR2(20)     DEFAULT SYS_CONTEXT ('USERENV', 'SESSION_USER'),
    CREATEDDATE           DATE           DEFAULT SYSDATE,
    UPDATEDBY               VARCHAR2(20),
    UPDATEDDATE           DATE,
    CONSTRAINT PK_ORG PRIMARY KEY (ORGID)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE CORE_TBSP
        STORAGE(INITIAL 64K
                MINEXTENTS 1
                MAXEXTENTS UNLIMITED
                PCTINCREASE 0
                BUFFER_POOL DEFAULT
                ), 
    CONSTRAINT FK_LNGG_ORGNSTN FOREIGN KEY (LANGUAGE)
    REFERENCES LANGUAGE(LANGUAGEID),
    CONSTRAINT FK_MAGISTERIALDISTRICT FOREIGN KEY (MAGISTERIALDISTRICTID)
    REFERENCES MAGISTERIALDISTRICT(MAGISTERIALDISTRICTID),
    CONSTRAINT FK_MUNICIPALITY FOREIGN KEY (MUNICIPALITYID)
    REFERENCES MUNICIPALITY(MUNICIPALITYID),
    CONSTRAINT FK_OWNRSHPTYP_ORGNSTN FOREIGN KEY (OWNERSHIPTYPE)
    REFERENCES OWNERSHIPTYPE(OWNERSHIPTYPEID),
    CONSTRAINT FK_CNTCTMTH_ORGNSTN FOREIGN KEY (CONTACTMETHOD)
    REFERENCES CONTACTMETHOD(CONTACTMETHODID),
    CONSTRAINT FK_DLCNTR_ORGN FOREIGN KEY (DOLCENTREID)
    REFERENCES DOLCENTRES(DOLCENTREID),
    CONSTRAINT FK_INDSTRYSCTR_ORGNSTN FOREIGN KEY (INDUSTRYSECTORCODE)
    REFERENCES INDUSTRYSECTOR(INDUSTRYSECTORCODE),
    CONSTRAINT FK_PRTY_ORGNSTN FOREIGN KEY (ORGID)
    REFERENCES PARTY(PARTYID)
)

CREATE TABLE CITIZEN(
    CITIZENID               CHAR(32)         NOT NULL,
    IDNUMBER                 CHAR(13),
    PASSPORTNUMBER         VARCHAR2(20),
    DATEOFBIRTH           DATE,
    DATEOFDEATH           DATE,
    TITLE                   NUMBER(4, 0),
    INITIALS                 VARCHAR2(10)    NOT NULL,
    FIRSTNAME               VARCHAR2(120)   NOT NULL,
    LASTNAME                 VARCHAR2(120),
    LANGUAGE                 NUMBER(4, 0)    NOT NULL,
    CONTACTMETHOD           NUMBER(4, 0)     NOT NULL,
    MARITALSTATUS           NUMBER(4, 0),
    GENDER                 NUMBER,
    RACE                     NUMBER(4, 0),
    CITIZENSHIPID           NUMBER(4, 0),
    MUNICIPALITYID         NUMBER(4, 0),
    MAGISTERIALDISTRICTID   NUMBER(4, 0),
    QUALIFICATIONID       NUMBER(4, 0),
    OCCUPATIONCODE         VARCHAR2(10),
    DOLCENTREID           NUMBER(4, 0),
    CREATEDBY               VARCHAR2(20)     DEFAULT SYS_CONTEXT ('USERENV', 'SESSION_USER'),
    CREATEDDATE           DATE           DEFAULT SYSDATE,
    UPDATEDBY               VARCHAR2(20),
    UPDATEDDATE           DATE,
    VENDORID                 VARCHAR2(10),
    CONSTRAINT PK_CITIZEN PRIMARY KEY (CITIZENID)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE CORE_TBSP
        STORAGE(INITIAL 64K
                MINEXTENTS 1
                MAXEXTENTS UNLIMITED
                PCTINCREASE 0
                BUFFER_POOL DEFAULT
                ), 
    CONSTRAINT FK_CITIZENSHIP FOREIGN KEY (CITIZENSHIPID)
    REFERENCES CITIZENSHIP(CITIZENSHIPID),
    CONSTRAINT FK_CNTCTMTH_CTZ FOREIGN KEY (CONTACTMETHOD)
    REFERENCES CONTACTMETHOD(CONTACTMETHODID),
    CONSTRAINT FK_CTZN_MGSTRLDSTRCT FOREIGN KEY (MAGISTERIALDISTRICTID)
    REFERENCES MAGISTERIALDISTRICT(MAGISTERIALDISTRICTID),
    CONSTRAINT FK_DLCNTRS_CTZ FOREIGN KEY (DOLCENTREID)
    REFERENCES DOLCENTRES(DOLCENTREID),
    CONSTRAINT FK_GNDR_CTZN FOREIGN KEY (GENDER)
    REFERENCES GENDER(GENDERID),
    CONSTRAINT FK_LNGG_CTZN FOREIGN KEY (LANGUAGE)
    REFERENCES LANGUAGE(LANGUAGEID),
    CONSTRAINT FK_MRTLSTTS_CTZ FOREIGN KEY (MARITALSTATUS)
    REFERENCES MARITALSTATUS(MARITALSTATUSID),
    CONSTRAINT FK_MUNICIPALITY1 FOREIGN KEY (MUNICIPALITYID)
    REFERENCES MUNICIPALITY(MUNICIPALITYID),
    CONSTRAINT FK_OCCUPATION FOREIGN KEY (OCCUPATIONCODE)
    REFERENCES OCCUPATION(OCCUPATIONCODE),
    CONSTRAINT FK_QUALIFICATION FOREIGN KEY (QUALIFICATIONID)
    REFERENCES QUALIFICATION(QUALIFICATIONID),
    CONSTRAINT FK_RCE_CTZN FOREIGN KEY (RACE)
    REFERENCES RACE(RACEID),
    CONSTRAINT FK_TTL_CTZN FOREIGN KEY (TITLE)
    REFERENCES TITLE(TITLEID),
    CONSTRAINT FK_PRTY_CTZN FOREIGN KEY (CITIZENID)
    REFERENCES PARTY(PARTYID)
)

Any other tables in the system that are related to an organisation or citizen actually have their FK pointing to the Party table.

In the LLBLGen generated code, I defined the Party as a base class to Citizen and Organisation. This works very well throughout the system, except when I need to return records that are sorted (or filtered) by any fields on OrganisationEntity or CitizenEntity when PartyEntity is included in a join. Since there is no way to directly include organisation or citizen as a relation in a RelationCollection, I can only assume that this relation should appear by default for derived types (using a left join?).

If there are any alternate methods, please let me know. I cannot break the derived class relationship. Can I just add a custom relation for these?

EDIT: The hierarchy is displayed as "One target (view/table) per entity type" in the "Inheritance Info" tab. EDIT 2: Custom relations causes 'Hashtable Key' errors in the designer when trying to add custom relations that map to the implied inheritance relation.

Thanks, Brian Johnson

Hi all,

Seems there is no known solution as there has not been a response to this, so my workaround was simply to create a View on the data I needed.

Still would love to know if this is possible

Brian

This thread must have slipped through the cracks of the unanswered threads queue. We'll look into it.

I tried to reproduce this and indeed it fails:

[Test]
public void SortOnRelatedDerivedEntityTest()
{
    EntityCollectionBase toDelete = new DepartmentCollection();
    UnitOfWork uow = CreateTestBatchForSave2(ref toDelete);
    uow.Commit(new Transaction(IsolationLevel.ReadCommitted, "Save"), true);

    try
    {
        DepartmentCollection departments = new DepartmentCollection();
        RelationCollection relations = new RelationCollection();
        relations.Add(DepartmentEntity.Relations.EmployeeEntityUsingWorksForDepartmentId);
        SortExpression sorter = new SortExpression(BoardMemberFields.CompanyCarId | SortOperator.Ascending);
        departments.GetMulti(null, 0, sorter, relations);
    }
    finally
    {
        toDelete.DeleteMulti();
    }
}

Boardmember is a subtype in a hierarchy of targetperentity of manager which is a subtype of employee.

The thing is that the fields of the subtypes are unknown because the relation is with the supertype (root of the hierarchy) and for optimization reasons the hierarchy of the supertype isn't joined into the relation, as it's not the entity fetched.