annoying N+1 problem when calling criteria.List()

see detailed description of my N+1 queries when performing a criteria.list with a join to another joined entity. I'm quite clueless by now, perhaps someone will have an idea.

these are the objects: (properly renamed :) ) Order -> Pot <-> Cover

order is many-to-one associated to Pot. (so different orders can point to the same Pot). Order table has a "PotId" field (with keys from Pot table).

Pot is one-to-one, bidirectionaly associated to Cover - one side is "one-to-one", the other is "many-to-one". Cover table has a "PotId" field (with keys from Pot table).

The troubles begin when I perform: session.CreateCriteria(typeof (Order)).CreateAlias("pot", "pot", JoinType.LeftOuterJoin).List();

what I get is a single query fetching orders+pots, and then N queries fetching cover (by cover.PotId). tried tweaking this way and the other but to no avail. I thought this may be linked to the issu开发者_高级运维e of "no lazy loading for one-to-one associations", but I can't get around it. any ideas? will be happy to supply more information.

I think you want a second alias/join specified between pot and cover (or to eager load the pot -> cover relationship in your mappings).

There's also a .SetFetchMode on ICriteria that you might find interesting.

Try setting your batch-size on the Cover entity to something > 1. When it goes to get the first Cover, it should load any other ones that are referenced by existing Pot entities, up to the limit of batch-size.