I have the following tables:

User (Id, Name, GroupId, ...) Group (Id, Name) Role (Id, Name) UserRole (UserId, RoleId)

LLBLGen sees the following relations:

User -> Group (n:1) User -> UserRole (1:n) User -> Role via UserRole (n:m)

I have hidden the second relation in the designed, as all I want is a Roles collection in the UserEntity. UserEntity has Group and Roles as fields mapped on relations, nothing else. Yet, when fetching a user and pre-fetching its roles, I get four, not three, queries:

SELECT `user`.Id, `user`.Name FROM `user` WHERE ( ( `user`.Name = 'admin' AND `user`.UserGroup_Id = 1)) LIMIT 1
SELECT `usergroup`.Id, `usergroup`.Name FROM `usergroup` WHERE ( ( ( `usergroup`.Id = 1)))
SELECT DISTINCT `role`.Id, `role`.Name FROM (( `user` `LPA__1`  INNER JOIN `user_role` `LPA_U2`  ON  `LPA__1`.`Id`=`LPA_U2`.`User_Id`) INNER JOIN `role`  ON  `role`.`Id`=`LPA_U2`.`Role_Id`) WHERE ( ( ( `LPA_U2`.User_Id = 1)))
SELECT DISTINCT `LPA__1`.Id AS Id0, `role`.Id AS Id1 FROM (( `user` `LPA__1`  INNER JOIN `user_role` `LPA_U2`  ON  `LPA__1`.`Id`=`LPA_U2`.`User_Id`) INNER JOIN `role`  ON  `role`.`Id`=`LPA_U2`.`Role_Id`) WHERE ( ( ( ( `LPA_U2`.User_Id = 1))))

Queries 1 and 2 seem normal to me, but I don't understand why I have query 3 and query 4?

What LLBLGen version and runtime libraries are you using? Could you post your code snippet to see the prefetchPaths?

daelmo wrote:

What LLBLGen version and runtime libraries are you using? Could you post your code snippet to see the prefetchPaths?

This is with ORMSupportClasses.Net20.dll v2.0.7.424, on MySql. Here is the code snippet.

    using llbl = MyProject.Namespace;

    IPrefetchPath2 pp = new PrefetchPath2((int)llbl.EntityType.UserEntity);
    pp.Add(llbl.EntityClasses.UserEntity.PrefetchPathUserGroup);
    pp.Add(llbl.EntityClasses.UserEntity.PrefetchPathRoles);

    llbl.DataAccessAdapter adapt = new llbl.DataAccessAdapter();
    user = (llbl.EntityClasses.UserEntity)adapt.FetchNewEntity(
        new llbl.FactoryClasses.UserEntityFactory(),
        new RelationPredicateBucket(
            (llbl.HelperClasses.UserFields.Logon == logon) &
            (llbl.HelperClasses.UserFields.UserGroupId == group.Id)),
             pp);
    adapt.CloseConnection();

sgay wrote:

I have the following tables:

User (Id, Name, GroupId, ...) Group (Id, Name) Role (Id, Name) UserRole (UserId, RoleId)

LLBLGen sees the following relations:

User -> Group (n:1) User -> UserRole (1:n) User -> Role via UserRole (n:m)

I have hidden the second relation in the designed, as all I want is a Roles collection in the UserEntity. UserEntity has Group and Roles as fields mapped on relations, nothing else. Yet, when fetching a user and pre-fetching its roles, I get four, not three, queries:

If you hide User -> UserRole, the relation User -> Role (m:n) is then also hidden as it's based on a hidden relation.

So which relation exactly did you hide? Or what exactly did you hide: did you just hide hte field mapped onto the relation perhaps?

SELECT `user`.Id, `user`.Name FROM `user` WHERE ( ( `user`.Name = 'admin' AND `user`.UserGroup_Id = 1)) LIMIT 1
SELECT `usergroup`.Id, `usergroup`.Name FROM `usergroup` WHERE ( ( ( `usergroup`.Id = 1)))
SELECT DISTINCT `role`.Id, `role`.Name FROM (( `user` `LPA__1`  INNER JOIN `user_role` `LPA_U2`  ON  `LPA__1`.`Id`=`LPA_U2`.`User_Id`) INNER JOIN `role`  ON  `role`.`Id`=`LPA_U2`.`Role_Id`) WHERE ( ( ( `LPA_U2`.User_Id = 1)))
SELECT DISTINCT `LPA__1`.Id AS Id0, `role`.Id AS Id1 FROM (( `user` `LPA__1`  INNER JOIN `user_role` `LPA_U2`  ON  `LPA__1`.`Id`=`LPA_U2`.`User_Id`) INNER JOIN `role`  ON  `role`.`Id`=`LPA_U2`.`Role_Id`) WHERE ( ( ( ( `LPA_U2`.User_Id = 1))))

Queries 1 and 2 seem normal to me, but I don't understand why I have query 3 and query 4?

THey're not the same . Let me explain the queries. Query 1 is simply fetching the user data, no problems there Query 2 fetches the usergroup data, no problems there either Query 3 and 4 are used for m:n prefetch path fetching. m:n relations use an intermediate entity to define which entity A relates to which entity B via an m:n relation. In this case, we have a set of users and a set of roles, and which role belongs to which user is only known when the intermediate data is fetched, namely the FK's to both PK's, in this case userid and roleid.

So query 3 fetches the role data and to link each role fetched with a user already loaded, it has to fetch the data which links them together, namely the userid and roleid from the set which joins them all together: query 4. The thing is: the prefetch paths fetch entities. This means that query 3 is a simple FetchEntityCollection call for the roles which match a given filter. THe prefetch path code then gets this set back and has now 2 sets of entities: users and roles. It then fetches a dyn. list to obtain the two PK values together per row so which user entity belongs to which role entity. That's query 4.

You might think: "but query 4 is actually unnecessary because it can obtain that value right away in query 3!". That's true, but the prefetch path code then has to duplicate the code to fetch entities, as the resultset has an extra column(or extra columns, with compound FKs).

So it then has to do a projection instead of a full collection fetch. A projection of a datareader onto an entity collection to fetch the entities is still slower than a full entity collection fetch, so that's why this option is chosen.

Otis wrote:

sgay wrote:

I have the following tables:

User (Id, Name, GroupId, ...) Group (Id, Name) Role (Id, Name) UserRole (UserId, RoleId)

LLBLGen sees the following relations:

User -> Group (n:1) User -> UserRole (1:n) User -> Role via UserRole (n:m)

If you hide User -> UserRole, the relation User -> Role (m:n) is then also hidden as it's based on a hidden relation.

So which relation exactly did you hide? Or what exactly did you hide: did you just hide the field mapped onto the relation perhaps?

Carefully looking at it, I realize I have hidden fields e.g. UserRole (User - UserRole (1:n)) but not relations, e.g. User - UserRole (1:n). I had not realized it was making a difference.

Yet, since I want to be able to prefetch a user's roles, I suppose I should not hide the relations, just the fields I don't want to see.

Otis wrote:

sgay wrote:

Queries 1 and 2 seem normal to me, but I don't understand why I have query 3 and query 4?

[...] Query 3 and 4 are used for m:n prefetch path fetching. m:n relations use an intermediate entity to define which entity A relates to which entity B via an m:n relation. In this case, we have a set of users and a set of roles, and which role belongs to which user is only known when the intermediate data is fetched, namely the FK's to both PK's, in this case userid and roleid.

[...]

You might think: "but query 4 is actually unnecessary because it can obtain that value right away in query 3!". That's true, but the prefetch path code then has to duplicate the code to fetch entities, as the resultset has an extra column(or extra columns, with compound FKs).

So: one query to fetch the roles themselves, and another query to fetch the relation between users and roles. Seems like an overkill in my particular case, when fetching one single user, but it makes sense when fetching all users, or in less simple situations (compound FKs...).

Question considered answered