Hi,

We’re having a little problem concerning prefetching. First, let me describe our situation: We have an entity from an oracle table “Person”, this table has relations with lots of other tables (“Order”, …) so the entity also has a lot of related entities. We are now trying to create a new related entity “ItemsConcerned” inside “Person” based on an Oracle View. (Mind you, this view has no primary key..I don’t know if this is important) An entity was created in the llblgen designer for this view, the relations also -> a new related entity exists, everything’s ok.. Now, we want to fetch a collection of “Person” entities where the “ItemsConcerned” related entities are prefetched. This is the code we use:

Public Function FetchCollection(ByVal personNr() As Integer) As EntityCollection(Of PersonEntity)
            Dim persons As New EntityCollection(Of PersonEntity)(New PersonEntityFactory())
            Dim bucket As New RelationPredicateBucket()
            With bucket.PredicateExpression
                .Add(New FieldCompareRangePredicate(PersonFields.PersonNr, Nothing, personNr))
            End With

            Dim PersonPath As IPrefetchPath2 = New PrefetchPath2(CInt(EntityType.PersonEntity))
            PersonPath.Add(PersonEntity.PrefetchPathItemsConcerned)

            Dim adapter As New DataAccessAdapter
            adapter.FetchEntityCollection(persons, bucket, PersonPath)
            Return persons
End Function

When we run this, the related entities “ItemsConcerned” inside the “Person” entities never have any items, but when we run the queries from our tracing output in our Oracle, the data returns correctly. We have always used this code for prefetching and everything works smoothly, but in this case with a view there seem to be some problems.. Extra information: We're using Oracle 8 and Llblgen v2.5 (updated to the build from today) Any help would be greatly appreciated. Thanks!

Greetings,

Yannick

Would you please describe the relation between the Person entity and the view? Would you please post the generated queries?

Please set a PK to the View from the LLBLGen Pro Designer, regenerate the code and try again.

-The relation "Person" - "ItemsConcerned" is 1:n.

-We tried setting a pk to the view..doesn't make a difference.

-The (2) generated queries:

Person query:

SELECT PERSONS."PERSON_NR" AS "PersonNr", 
PERSONS."NAAM" AS "Naam", 
PERSONS."VOORNAAM" AS "Voornaam", 
PERSONS."VOLLEDIGE_NAAM" AS "VolledigeNaam", 
PERSONS."STRAAT" AS "Straat", 
PERSONS."HUIS_NR" AS "HuisNr", 
PERSONS."BUS_NR" AS "BusNr", 
PERSONS."POST_NR_EXTRA" AS "PostNrExtra", 
PERSONS."POST_NR" AS "PostNr", 
PERSONS."GEMEENTE" AS "Gemeente", 
PERSONS."LAND_KOD" AS "LandKod", 
PERSONS."TELEFOON_NR" AS "TelefoonNr", 
PERSONS."TELEFOON_UUR" AS "TelefoonUur", 
PERSONS."TELEFOON_NR_WERK" AS "TelefoonNrWerk", 
PERSONS."TELEFOON_WERK_UUR" AS "TelefoonWerkUur", 
PERSONS."TELEFAX_NR" AS "TelefaxNr", 
PERSONS."GEBOORTE_DAT" AS "GeboorteDat", 
PERSONS."OVERLIJDEN_DAT" AS "OverlijdenDat", 
PERSONS."RIJKSREGISTER_NR" AS "RijksregisterNr", 
PERSONS."BURG_STAND_KOD" AS "BurgStandKod", 
PERSONS."TAAL_KOD" AS "TaalKod", 
PERSONS."ALFA_NAAM" AS "AlfaNaam", 
PERSONS."GESLACHT_KOD" AS "GeslachtKod", 
PERSONS."ALFA_STRAAT" AS "AlfaStraat", 
PERSONS."FINREK_NR" AS "FinrekNr", 
PERSONS."DOMICIL_NR" AS "DomicilNr", 
PERSONS."BTW_KOD" AS "BtwKod", 
PERSONS."BTW_NR" AS "BtwNr", 
PERSONS."PROSPEKT_BEGIN_DAT" AS "ProspektBeginDat", 
PERSONS."PROSPEKT_BEGIN_VIA_KOD" AS "ProspektBeginViaKod", 
PERSONS."RELATIE_BEGIN_DAT" AS "RelatieBeginDat", 
PERSONS."RELATIE_BEGIN_VIA_KOD" AS "RelatieBeginViaKod", 
PERSONS."LAATSTE_REAKTIE_DAT" AS "LaatsteReaktieDat", 
PERSONS."MAILING_KOD" AS "MailingKod", 
PERSONS."STATUS_KOD" AS "StatusKod", 
PERSONS."ROOD_DOSSIER_KOD" AS "RoodDossierKod", 
PERSONS."BVVO_KOD" AS "BvvoKod", 
PERSONS."BEROEP_KOD" AS "BeroepKod", 
PERSONS."SOCIALE_KLASSE_KOD" AS "SocialeKlasseKod", 
PERSONS."WIJZE_KONTAKT" AS "WijzeKontakt", 
PERSONS."ANNULATIE_KOD" AS "AnnulatieKod", 
PERSONS."ONZEKER_ADRES_KOD" AS "OnzekerAdresKod", 
PERSONS."AANTAL_POLISSEN" AS "AantalPolissen", 
PERSONS."USER_DOSSIER_BEHEERDER" AS "UserDossierBeheerder", 
PERSONS."WIJZIGING_DAT" AS "WijzigingDat", 
PERSONS."CONTROLEGETAL" AS "Controlegetal", 
PERSONS."KODE_ROKER" AS "KodeRoker", 
PERSONS."STREET_ID" AS "StreetId", 
PERSONS."EENHEDEN_KOD" AS "EenhedenKod", 
PERSONS."GSM_NR" AS "GsmNr", 
PERSONS."E_MAIL" AS "EMail", 
PERSONS."LAATST_GEBRUIKT_BANKREKENINGNR" AS "LaatstGebruiktBankrekeningnr", 
PERSONS."MOD_DATE" AS "ModDate", 
PERSONS."MOD_TIME" AS "ModTime", 
PERSONS."C_INCASSOLABEL" AS "CIncassolabel", 
PERSONS."DVV_FA59_PSI" AS "DvvFa59Psi", 
PERSONS."C_WITWAS_CHECK" AS "CWitwasCheck", 
PERSONS."C_CHECK_STREETID" AS "CCheckStreetid", 
PERSONS."AFFINITY_REF" AS "AffinityRef", 
PERSONS."C_AFFINITY" AS "CAffinity", 
PERSONS."OPT_IN_DERDEN" AS "OptInDerden", 
PERSONS."N_BEDRIJF" AS "NBedrijf", 
PERSONS."NATIONALITEIT" AS "Nationaliteit" 
FROM PERSONS" WHERE ( ( PERSONS."PERSON_NR" IN (:PersonNr1, :PersonNr2, :PersonNr3)))

ItemsConcerned query (the prefetch query):

SELECT ITEMSCONCERNED."OORSPRONG" AS "Oorsprong", 
ITEMSCONCERNED."BETROKKENE_RELATIE_NR" AS "BetrokkeneRelatieNr", 
ITEMSCONCERNED."PERSON_NR" AS "PersonNr", 
ITEMSCONCERNED."SOORT_TUSSENPERSOON_KOD" AS "SoortTussenpersoonKod", 
ITEMSCONCERNED."TUSSENPERSOON_NR" AS "TussenpersoonNr", 
ITEMSCONCERNED."SCHADE_DOSSIERNR_JJMM" AS "SchadeDossiernrJjmm", 
ITEMSCONCERNED."SCHADE_DOSSIERNR_VOLGNR" AS "SchadeDossiernrVolgnr", 
ITEMSCONCERNED."VERZAMELPOLIS_NR" AS "VerzamelpolisNr", 
ITEMSCONCERNED."VERZAMELPOLIS_VOLGKOD" AS "VerzamelpolisVolgkod", 
ITEMSCONCERNED."POLIS_VOORWERP_NR" AS "PolisVoorwerpNr", 
ITEMSCONCERNED."OFFERTE_VOLGNR" AS "OfferteVolgnr", 
ITEMSCONCERNED."OFFERTE_JJMM" AS "OfferteJjmm", 
ITEMSCONCERNED."HOOFDPRODUKT" AS "Hoofdprodukt", 
ITEMSCONCERNED."AARD_BETROKKENE" AS "AardBetrokkene", 
ITEMSCONCERNED."VERZEKERINGSNEMER" AS "Verzekeringsnemer" 
FROM ITEMSCONCERNED 
WHERE ( ITEMSCONCERNED."PERSON_NR" IN (:PersonNr1, :PersonNr2, :PersonNr3))

I can't reproduce it. Would you provide a small repro solution based on Northwind database?

And please follow the following steps in the specified order: 1- Map the view as an entity 2- Assign a PK to the view 3- Create a custom relation bewteen the view and an entity 4- Make sure the relation is visible at both sides 5- Inside the entity make sure a field is mapped to that relation 6- Generate and test

Thanks.

Please check the types of PersonNr in both the view and the table. They should be THE SAME. If one is a NUMBER(10, 0) for example (table) and the view uses NUMBER, it means that the table has an int32 typed PK field and the view has a decimal typed FK field, which gives different hashcodes so the rows won't match.