Query in Nhibernate

I am creating a开发者_开发知识库 winform application where I use Nhibernate. In my app I need to select all products in my database (74000 rows).

On my product object I have a list of objects, called barcodes.

I want to select all my products but the list of barcodes on earch product should only be filled with objects if the barcodetype = 20 and groupid = 4

I don't know how to write this query, I have been trying with detached criteria, but I can't get it to work. Can anybody help me writing the query, or with a link to a page where I can see how its done?

---

Since a collection can't be "half-loaded", you need a projection.

This is how I usually do it:

var result = session.CreateQuery(@"
    select product, barcode
    from Product product
    join product.Barcodes barcode
    where barcode.Barcodetype = 20
    and barcode.Groupid = 4
    ")
    .List<object[]>()
    .ToLookup(x => (Product)x[0], x => (Barcode)x[1]);

(I have assumed you have scalar properties called Barcodetype and Groupid, since you didn't specify any relationships, and that all products have at least one barcode of that type)

---

Rather than instantiate all the Product objects and a subset of their Barcodes, how about using Criteria against Barcode, filtering by the type/group mentioned and joining onto the parent product.

Then use an AliasToBeanResultTransformer to populate a list of simple DTO objects.

---

I found the answer, i did it with an outer left join crit.CreateCriteria("barcodeses", "bc", SqlCommand.JoinType.LeftOuterJoin).Add(Expression.Or(Expression.Eq("bc.Groupid", CType(cbBarcodeGroup.SelectedItem, Domain.Barcodegroup).ID), Expression.IsNull("bc.Groupid")))