Hi guys,

I'm currently not even half way unleashing the power of llblgen, but i'm trying to get the best out of it, in terms of performance and efficiency. Posting this question probably reveals my level of competence on this, but so be it

The thing that i'm trying to do is working so that's not what my question's about, but i'm not sure if i'm using the techniques in a way they're supposed to be used, so i'd like your advice on that please.

See below a snippet from the PerformSelect eventhandler of a LLBLGenProDatasource object for a EntityCollection ActiviteitCollection. The object CurrentUser is the current logged on user on my site, being a UserEntity, which, in this case will also be of the type Ouder. (Ouder is subtyped from User) Also see the attached, somewhat simplified diagram. (I'm sorry, the design is in Dutch )

            if (CurrentUser != null)
            {
                PredicateExpression filter = new PredicateExpression();
                RelationCollection relations = new RelationCollection();

                relations.Add(ActiviteitEntity.Relations.OrganisatieEntityUsingOrganisatieId);
                relations.Add(OrganisatieEntity.Relations.LocatieEntityUsingOrganisatieId);
                relations.Add(LocatieEntity.Relations.KindLocatieEntityUsingLocatieId);
                relations.Add(KindLocatieEntity.Relations.KindEntityUsingKindId);
                relations.Add(KindEntity.Relations.KindOuderEntityUsingKindId);
                relations.Add(KindOuderEntity.Relations.OuderEntityUsingUserId);

                filter.AddWithAnd(OuderFields.UserId == CurrentUser.UserId);
                filter.AddWithAnd(ActiviteitFields.BeginDatum <= SelectedDate.GetValueOrDefault(DateTime.Now));
                filter.AddWithAnd(ActiviteitFields.EindDatum >= SelectedDate.GetValueOrDefault(DateTime.Now));

                RelationCollection relations2 = new RelationCollection();
                relations2.Add(RegistratieEntity.Relations.KaartEntityUsingKaartId);
                relations2.Add(KaartEntity.Relations.KaartKindEntityUsingKaartId);
                relations2.Add(KaartKindEntity.Relations.KindEntityUsingKindId);
                relations2.Add(KindEntity.Relations.KindOuderEntityUsingKindId);
                relations2.Add(KindOuderEntity.Relations.OuderEntityUsingUserId);

                PrefetchPath prefetch = new PrefetchPath(EntityType.ActiviteitEntity);
                prefetch.Add(ActiviteitEntity.PrefetchPathRegistratie, 0, null, relations2);

                e.ContainedCollection.GetMulti(filter, 0, null, relations, prefetch);

            }

Now, my goal is, to get ALL Activiteit entities for a certain period, and, because i'll be needing them in a nested repeater, also prefetch the Registratie entities for that Activiteit entity (if any), but then only Registratie entities that matter for the current user (namely, only registrations belonging to his children, having cards, having done registrations)

The way i did it now, seems a bit overdone to me, because, given the only fact the logged on user, i already have to climb half the relations-tree to get from Ouder to the OrganisatieID, going via Kind that i was thinking, is it really needed to make that relations2 collection or can it be done much simpler...?

I hope i explained it clear enough. Again, it does what it has to do, but i'm just trying to use LLBGen's features to the max and in the intended way.

Thanks alot for any responses!

To help us more understand your schema and hence the query, and to better answer you.

Would you please post the current produced SQL query (with all these joins), and then post the query you think should be more effeicient to use?