I'm using a (quite simple) prefetchpath in my application, but it's not behaving as I expect.

The situation is as follows: I have 2 tables: Personeel and TypePersoneel. The tables are related on field TypeCode, which is the PK of table TypePersoneel and an FK of table Personeel. The other field of TypePersoneel is Omschrijving.

I want to display the Omschrijving of each record of the Personeel table in a GridView, so I created a 'Field on related field' TypePersoneelString in the PersoneelEntity. This field is mapped on TypePersoneel.Omschrijving.

When I turn on tracing I see that for every record in Personeel a query is performed on the TypePersoneel table with the TypeCode of each record. This makes sense, since at this point I'm using lazy loading.

Now I try to use a prefetch path to reduce the number of queries as follows:

            PrefetchPath path = new PrefetchPath((int)EntityType.PersoneelEntity);
            path.Add(PersoneelEntity.PrefetchPathTypePersoneel);
            RelationCollection rel = new RelationCollection();
            rel.Add(PersoneelEntity.Relations.TypePersoneelEntityUsingTypecode);
            SortExpression sort = new SortExpression(PersoneelFields.Achternaam | SortOperator.Ascending);
            personeelCollection.GetMulti(null, 0, sort, rel, path);

but it doesn't make any difference. Is still see a select being performed on TypePersoneel for every row that's displayed in the GridView.

What am I doing wrong?

(I don't suppose it's relevant, but I'm using a MySql 5.0 database. I'm using the march 19th release of LLBLGen Pro)

EDIT: I just noticed that the query I'm expecting to see is actually executed at the end, but at that point it's useless since all the separate queries have already been executed.

Query: SELECT `typepersoneel`.Typecode, `typepersoneel`.Omschrijving FROM `typepersoneel` WHERE ( ( `typepersoneel`.Typecode IN (@Typecode1, @Typecode2, @Typecode3, @Typecode4, @Typecode5, @Typecode6)))
    Parameter: @Typecode1 : AnsiString. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "00".
    Parameter: @Typecode2 : AnsiString. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "3".
    Parameter: @Typecode3 : AnsiString. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "5".
    Parameter: @Typecode4 : AnsiString. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "2".
    Parameter: @Typecode5 : AnsiString. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "1".
    Parameter: @Typecode6 : AnsiString. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "4".

Apparantly I'm using the prefetch path alright, but I'm missing something else to stop the separate queries from executing. Any clues?

Funny how you suddenly think of other solutions after you ask a question.

First I executed the code above in the Load event of my Windows Form, but when I moved it to the constructor the separate queries were gone. I suppose it has something to do with the time when the GridView is filled.

Anyway, my problem is solved but perhaps someone else can benefit from this knowledge.