Match nHibernate expressions to nested tables?

Question

Hi,

Stupid nHibernate noob question, but I can't find the answer anywhere ...

I have a "product" class successfully mapped to a product table, and an "sku" class. Sku is a child object of "product" and the two tables in the DB are related by primary key.

So far all the basic nHibernate stuff works - I can run CRUD opeations against the databasse successfully. I can make expression based queries successfully, as in ...

    Dim results As ArrayList = session.CreateCriteria(Of DataTransferObjects.Product) _
        .Add(Expression.Like("Name", nameSearchString)) _
    .List()

.. And the result is a list of product objects which successfully expose the expected Sku child objects. However, if I try the above code searching on field names from the Sku table, NHibernate throws an error:

    Dim results As ArrayList = session.CreateCriteria(Of DataTransferObjects.Product) _
        .Add(Expression.Like("SkuCode", skuSearchString)) _
    .List()

Results in "could not resolve property: SkuCode of: Product"

This works:

    Dim results As ArrayList = session.CreateCriteria(Of DataTransferObjects.Sku _
        .Add(Expression.Like("SkuCode", skuSearchString)) _
    .List()

But, unsurprisingly, it only returns Sku objects whereas I need the product object.

This also compiles:

    Dim results As ArrayList = session.CreateCriteria(Of DataTransferObjects.Product) _
        .Add(Expression.Like("Name", nameSearchString)) _
        .CreateCriteria("Skus").Add(Expression.Like("SkuCode", skuSearchStrung)) _
    .List()

But it returns nothing at all, even though the first expression is valid

How can I run an Expression.Like against fields in the Sku table?

Answer 1

I've solved this - thought I'd leave it here for anyone else to discover. This sort of thing seems very poorly documented:

    Dim results As ArrayList = session.CreateCriteria(Of DataTransferObjects.Product) _
        .CreateAlias("Skus", "sku") _
        .Add(Expression.Or( _
        Expression.Like("Name", nameSearchString), _
        Expression.Like("sku.SkuCode", skuSearchStrung))) _
    .List()

I've blogged about my frustrations with this framework: http://mattthr.blogspot.com/2010/02/quey-across-join-in-nhibernate.html