I want to use custom relation to create a PrefetchPath. Instead of using only the PK column for joining, I want to add one additional column.

I come up with this code but its not working, the MetaData property would not populated.

       public static MemberEntity GetWithMetadata(int csmId)
        {
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                MemberEntity member = new MemberEntity(csmId); 

                IPrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.MemberEntity);

                IEntityRelation relation = new EntityRelation(RelationType.OneToMany, "MetaData", true);
                //-- Add PK and FK fields 
                relation.AddEntityFieldPair(MemberFields.CsmId, MemberMetadataFields.CsmId);
                //-- Will not work if add one more Non PK fields, but if I remove this, the MetaData will be populated 
                //-- with expected resultset.
                relation.AddEntityFieldPair(MemberFields.ContextsetId, MemberMetadataFields.ContextsetId);                          
                relation.InheritanceInfoPkSideEntity = InheritanceInfoProviderSingleton.GetInstance().GetInheritanceInfo("MemberEntity", true);
                relation.InheritanceInfoFkSideEntity = InheritanceInfoProviderSingleton.GetInstance().GetInheritanceInfo("MemberMetadataEntity", false);
                                
                IPrefetchPathElement2 pathElement = new PrefetchPathElement2(
                    new EntityCollection<MemberMetadataEntity>(EntityFactoryCache2.GetEntityFactory(typeof(MemberMetadataEntityFactory))),
                    relation,
                    (int)EntityType.MemberEntity,
                    (int)EntityType.MemberMetadataEntity, 
                    0, 
                    null, 
                    null, 
                    null, 
                    null, 
                    "MetaData", 
                    RelationType.OneToMany);

                prefetchPath.Add(pathElement);
            

                adapter.FetchEntity(member, prefetchPath);
                return member;

            }
        }

Could you please give more info (http://llblgen.com/TinyForum/Messages.aspx?ThreadID=7725)?: - Approximate SQL code you would expect. - The basic structure info of the involved tables (at least PKs, FKs) - LLBLGen version and runtime library version?

I'm using LLBLGen version 2.6 Database: Oracle 8i

Expected Query (I want to fetch member with matching csm_id and all child MetaData using combination of contextset_id and csm_id instead of just csm_id which is the primary key for the member table and the prefetch link was already provided by the generated code):

  SELECT * 
  FROM Member a,
        MemberMetaData b
  WHERE a.contextset_id = b.contextset_id
       AND a.csm_id = b.csm_id
       AND a.csm_id = <somevalue>

Table Structure:

Member CSM_ID - PK CONTEXTSET_ID

MemberMetaData CSM_ID - FK CONTEXTSET_ID METADATA_VALUE PROPERTY_CODE

Although the 2 tables can be joined just by csm_id (PrefetchUsingCsmId), I want to use both csm_id and contextset_id for joining.

SELECT * FROM Member a, MemberMetaData b WHERE a.contextset_id = b.contextset_id AND a.csm_id = b.csm_id AND a.csm_id = <somevalue>

The above query suggests you want to fetch fields from both tables in a flat set, is this the case?

If yes, Then you don't need a prefetchPath, just a a DynamicList where you define the EntityRelation between these table with a CustomFilter predicated too.

Sorry if my query might be misleading for what I want to do.

If you see my function on my first post, what I'm trying to do is to load a Member including its child related MetaData (EntityCollection<MetaDataEntity>) on a single call. A Prefetch is was already provided by the generated code using the MemberEntity.PrefetchPatchUsingCsmId and its working fine, both Member and Metada was successfully fetched. But instead of using this, I want to use my custom prefetch mechanism using 2 columns (CsmID and ContextSetID). The reason I'm doing this is because currently our source table only have index on this two columns. Using the 2 columns will fetch the data a lot faster. I don't have access to add more index and I was just curious if I can do it on the code.

Thanks.

Please can you post the generated SQL for the working and failing versions of your code? This will give us a clearer idea of what is going on...

Matt