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")))