Hi, We have a situation where we have a DevExpress grid displaying one source of data, and one column involves a lookup from an foreign table. Obviously we use Prefetch to add the table of associated rows for display, but when editing an entry we must display all of the foreign table entities to the user, meaning we currently have another entity collection for the dropdown. This contains all of the rows that the prefetch would require... if it knew it existed. Is it possible to add this second collection as a prefetch path, or is there a different architecture you would suggest for this process?

Cheers, Jason

Is it possible to add this second collection as a prefetch path, or is there a different architecture you would suggest for this process?

Don't use a prefetch Path for the dropdownList collection. This second collection should be loaded on its own. And for databinding with the main entity, you should bind the corresponding FK property of the main entity to the dropDownList SelectedValue property.

Walaa wrote:

Don't use a prefetch Path for the dropdownList collection. This second collection should be loaded on its own. And for databinding with the main entity, you should bind the corresponding FK property of the main entity to the dropDownList SelectedValue property.

We already do this, but this results in three queries to the database:

(Simple scenario) Query 1: Select all users from the database Query 2: Prefetch selects all Company rows from tblCompany where CompanyID in (x,y,z...) Query 3: Select all Company rows to populate dropdown list

Query 2 is obviously a subset of Query 3 records, so I was wondering if we could do away with Query 2 by saying "Here, everything is in Query3's collection, don't build up another one".

Obviously the normal method gets more expensive as you have more related tables, effectively doubling the number of rows stored in memory, and unnecessarily requiring use on the IN() statement on the prefetch.

It's mainly a minor issue provided the prefetch collection remains small, but could get sticky if you have 5000 customers from 4000 companies, requiring a prefetch with IN(n1..n4000) and another collection of 4000 companies for the dropdown. For situations where there are 5000 customers from 10 companies, the current solution is fine. And yes, I know that this is an unrealistic example, but it's a case of removing redundancy, and I was interested as to whether or not I could do it in a more efficient manner, and if not, why not

Cheers, Jason

JSobell wrote:

but it's a case of removing redundancy, and I was interested as to whether or not I could do it in a more efficient manner, and if not, why not

Fetch your lookup table only one time, keep them in a cache, ad instantiate some entityview object, based on the cached entitycollection, to fill up your lookup combo.

I'm using this approach in our new windows-form application.

(Simple scenario) Query 1: Select all users from the database Query 2: Prefetch selects all Company rows from tblCompany where CompanyID in (x,y,z...) Query 3: Select all Company rows to populate dropdown list

I was suggesting using Query 1 & 3 only. i.e. don't use a prefetchPath, since the databinding with the dropdownList selectedValue will show the corresponding CountryName.

Is there another reason for using the PrefetchPath, other than showing the CountryName? I might be missing something out or misunderstand your case.

This is fine so long as there is only a single grid used in this way, but suppose I have a 'detail' panel below, or decide to use the same ContactEntity object elsewhere, then I really do need the 'field from related table' feature, so I don't want to remove the prefetch, just re-use an existing set of appropriate data. If it's not possible, fine, I just thought it was worth investigating.

Cheers, Jason