Is it possible to set up custom relation on two fields that are neither primary or foreign keys?

Yes. Here is an example:

Dim myAggregateRelation As IEntityRelation = New EntityRelation(RelationType.OneToOne, True)
myAggregateRelation.AddEntityFieldPair(PlanFields.BenefitId, AggregateFactorFields.BenefitId)

Hope this helps!

Ryan D. Hatch

lilliebean wrote:

Is it possible to set up custom relation on two fields that are neither primary or foreign keys?

No at LLBLGen Designer. However you can create it at code for fetching/filtering purposes, as Ryan showed.

Once I have the tables related, how do I access and display the fields from the related table?

LLBLGEN V 2.6 Adapter C#

I am relating the tables on an id field common to the tables but not a primary key in either however I need only the Name field from the related table. Here is my code to get a list of items.

public static EntityView2<EntityBase2> RetrieveInstitutionList(int wId) { var institutions = new EntityCollection(new UWInstitutionEntityFactory());

        var myCustRelation = new EntityRelation(RelationType.OneToMany, true);
        myCustRelation.AddEntityFieldPair(UWInstitutionFields.InstitutionId, InstitutionFields.InstitutionId);


        using (var adapter = new DataAccessAdapter())
        {
            var pageBucket = new RelationPredicateBucket();
            IPredicateExpression filter = new PredicateExpression();

            filter.Add(UWInstitutionFields.uwId == wId);
            pageBucket.Relations.Add(myCustRelation);
            pageBucket.PredicateExpression.Add(filter);

            var sorter = new SortExpression(UWInstitutionFields.Id | SortOperator.Ascending);

            adapter.FetchEntityCollection(institutions, pageBucket, 5000, sorter);
        }
        return institutions.DefaultView;
    }

That's another story. See, you use relations to filter/sorting on related fields. If you want to fetch related entities properties, you need to use PrefetchPaths. Is there any chance you set InstitutionFields.InstitutionId as primary key on your DB or at least unique constraint?

InstitutionId is not a primary key or unique constraint... is that a requirement for prefetch paths?

Yes. To be able to use PrefetchPaths, a relation must exist between the two entities, thus you have to create it at DB or at LLBLGen Designer. For that, the PK side must be PK or unique constraint.