I am using Adapter with LLBLGen v2.6.

Is it possible to create a dynamic prefetch path based on a unique constraint?

I have an orders table with a client code field. The client code is not the client table's PK but it it does have a unique constraint. I have added a relationship to the database but it isn't picked up in the designer (I think the designer only works with relationships based on PKs).

Due to it not being picked in the designer, I can't use it as a prefetch path. I know you can dynamically create a relationship but can you use it to create a dynamic prefetch path?

Designer can pick relations based referencing Unique Keys, not Unique Indexes. Please try it out.

The client table does have a unique key for "ClientCode" but obviously the orders table doesn't.

There is no indication that I am able to create a relationship (it only offers the PK of the client table) with a unique key using the relations tab for the Client Entity.

If I use the catalog explorer, I can see that it is picking up the unique constraint (which is definitely a unique key).

Will upgrading to v3.5 help?

3.5 as well only works with relationships where one side has to be the PK. If the side with the unique constraint has a different set of fields as PK, you can in v3 set the unique constraint fields as PK and switch off the setting that PKs in the model follow the pks in the DB.

Could you describe the two tables in detail? so with the fields/pks/ucs ? thanks.

Here are the 2 tables (I've left out the irrelevant fields)

CLIENT -> Id int (PK) -> ClientCode nvarchar(50) (Unique Key)

ORDER -> Id int (PK) -> ClientCode nvarchar(50)

Relationship: ORDER.ClientCode -> CLIENT.ClientCode

So I want to fetch a collection of orders and prefetch the clients based on their client code.

Why Order reference the ClientCode not the Client ID? If this can not be changed, then ClientCode is qualified to be the PK as least in the model (set in the designer).

The client ID is not referenced as it may change but the client code is guaranteed to be unique.

The order records are also archived so the Id of the client may no longer be valid but we still want to be able to get an idea of who it was (we can cross reference with invoices, etc).

This supports the suggestion to have the ClientCode as the PK. Changing PK values is not a best practice. So the Id should not be the PK, at least in the model.

Indeed. ClientCode won't be duplicated in the table, so 'Id' is irrelevant. You could change the Identifying fields in teh designer to ClientCode to solve this.

You could also (if this relationship / prefetch path is for readonly purposes for example) map two additional entities onto the same tables and instead of using Id there as the PK, use the ClientCode as the PK. But other than that, it's not possible to get the prefetch path you want. (you can always fetch manually of course, e.g. through a nested query in linq / queryspec, just not through the prefetch path)

I understand.

Is there any technical reason why a dynamic prefetch isn't possible? I am surprised it isn't requested quite often as it would certainly be useful.

These threads comment on that: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=3285 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=2017