Nhibernate Criteria Query with Join
- by John Peters
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
