views:

186

answers:

0

I am looking to do the following using an NHibernate Criteria Query

I have "Product"s which has 0 to Many "Media"s

A product can be associated with 1 to Many ProductCategories

These use a table in the middled to create the join

ProductCategories
Id
Title

ProductsProductCategories
ProductCategoryId
ProductId

Products
Id
Title

ProductMedias
ProductId
MediaId

Medias
Id
MediaType

I need to implement a criteria query to return All Products in a ProductCategory and the top 1 associated Media or no media if none exists.

So although for example a "T Shirt" may have 10 Medias associated, my result should be something similar to this

Product.Id Product.Title MediaId
1 T Shirt 21
2 Shoes Null
3 Hat 43

I have tried the following solutions using JoinType.LeftOuterJoin

1) productCriteria.SetResultTransformer(Transformers.DistinctRootEntity);

This hasnt worked as the transform is done code side and as I have .SetFirstResult() and .SetMaxResults() for paging purposes it wont work.

2) .SetProjection(
Projections.Distinct(
Projections.ProjectionList() .Add(Projections.Alias(Projections.Property("Id"), "Id")) ...
.SetResultTransformer(Transformers.AliasToBean());

This hasn't worked as I cannot seem to populate a value for Medias.Id in the projections. (Similar to http://stackoverflow.com/questions/1036116/nhibernate-criteria-api-projections)

Any help would be greatly appreciated