When I run the following code with a single query to get the 3 collections for the Product: pricelist, Materials and Colors. And when product.PriceList access the collection, I have the data
ProductDTO Product = (ProductDTO)session.CreateCriteria(typeof(ProductDTO)) .Add(Expression.IdEq(code)) .SetFetchMode("Colors", FetchMode.Eager) .SetFetchMode("PriceList", FetchMode.Eager) .SetFetchMode("Materials", FetchMode.Eager) .UniqueResult(); The problem is that I need to list the collection PriceList in order by Num and I use the following code:
ProductDTO Product = (ProductDTO)session.CreateCriteria(typeof(ProductDTO)) .Add(Expression.IdEq(code)) .SetFetchMode("Colors", FetchMode.Eager) .SetFetchMode("PriceList", FetchMode.Eager) .SetFetchMode("Materials", FetchMode.Eager) .UniqueResult(); OR next code:
ProductDTO Product = (ProductDTO)session.CreateCriteria(typeof(ProductDTO)) .Add(Expression.IdEq(code)) .SetFetchMode("Colors", FetchMode.Eager) .SetFetchMode("PriceList", FetchMode.Eager) .SetFetchMode("Materials", FetchMode.Eager) .CreateCriteria("PriceList").AddOrder(Order.Asc("Num")) .UniqueResult(); This restriction means that when I access the collection product.PriceList FORCE a NEW QUERY for PriceList (not with order clause) that is unnecessary. And I occasionally get "failed lazily initialize collection role no session or session was closed"
Please if anyone can guide me about it. I like to solve in a single query and understand what happens. I found similar post like one that use "not-found=ignore". I'm using NHIBERNATE 2.1.2
I reproduce below part of the mappings:
<?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"> <class name="DotAR.M.Logica.DTO.ProductDTO, DotAR.M" table="products"> <id name="Code" type="string" unsaved-value="null"> <generator class="assigned" /> </id> <set name="PriceList" lazy="true" inverse="true" cascade="all"> <key column="code" /> <one-to-many class="DotAR.M.Logica.DTO.ProductPriceNumDTO, DotAR.M" /> </set> <set name="Colors" lazy="true" inverse="true" cascade="all"> <key column="code" /> <one-to-many class="DotAR.M.Logica.DTO.ProductColorDTO, DotAR.M" /> </set> <set name="Materials" lazy="true" inverse="true" cascade="all"> <key column="code" /> <one-to-many class="DotAR.M.Logica.DTO.ProductMaterialDTO, DotAR.M" /> </set> </class> </hibernate-mapping> Every collection has a composite-id. Example ProductColor
<?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"> <class name="DotAR.M.Logica.DTO.ProductColorDTO, DotAR.M" table="product_colors"> <composite-id unsaved-value="any"> <key-property name="Code" type="string" /> <key-property name="Name" type="string" /> </composite-id> <many-to-one name="Product" column="code" class="DotAR.M.Logic.DTO.ProductDTO, DotAR.M" insert="false" update="false" /> </class> </hibernate-mapping> 
