Hi,

I have Linq query as below:

var query = (from ua in metaData.Useraccount select ua)
.WithPath<UseraccountEntity>(uaPath => uaPath.Prefetch<UsergrouprelEntity>(ua => ua.Usergrouprel)
.SubPath<UsergrouprelEntity>(ugrPath => ugrPath.Prefetch<UsergroupEntity>(ugr => ugr.Usergroup)));

List<UseraccountEntity> ualist = query.ToList<UseraccountEntity>();

To make references between entities there are executed four queries on DB. Is it possible to do it in one query?

Thanks in advance

Unfortunately no. As you are using WithPaths, you are retrieving data for 4 sets of different entities - therefore 4 seperate queries are needed.

If you wanted to run one query you could retrieve the data you wanted as a dynamic list, join the required tables using relations. Obvioulsy in this case the data would come back as a flat table rather than an entity graph - so may be not what you need.

Matt

Thanks for reply. Yes, I would like to have references between entities. If I am not wrong in NHibernate is possibility to set how the queries will be executed, in one query or as in llblgen in a few queries, one for each entity.

stone wrote:

Thanks for reply. Yes, I would like to have references between entities. If I am not wrong in NHibernate is possibility to set how the queries will be executed, in one query or as in llblgen in a few queries, one for each entity.

NHibernate offers indeed that option, but their prefetch path feature is less powerful (you have to specify the parent-child setup yourself for example with the merging as well). The feature of having everything in 1 query isn't implemented in LLBLGen Pro as it is very often less optimal. The problem is that you have to join every node element's set to the parent's set. This leads to duplicate rows (for the parents) and this increases even more if you have multiple branches in your path. So if I want to fetch customer - Order - OrderDetails and also Order - Employee, I get a set with Customer JOIN Order Join Order Details Join Employee

this could lead to a lot of duplicate Customer rows. This is particular bad, because the duplicated rows have to be filtered out on the client. With multiple queries, one per set, you don't have this problem, the sets are small and materializing the entities is easier too and thus faster.