Nhibernate criterion query with union

I want to do the following using NHibernate criteria query

I have a "Product" from 0 to many "Media"

Product can be linked to 1 in many ProductCategories

They use a table in a tool to create a join

ProductCategories
Id
Title

ProductsProductCategories
ProductCategoryId
ProductID

Product
Id
Name

ProductMedias
PRODUCTID
MediaId

Medias
Id
MediaType

I need to run a criteria query to return all products in ProductCategory and the first 1 associated media, or no media if it doesn't exist.

So, although for example "T Shirt" might have 10 associated Medias, my result should be something like this

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

I tried the following solutions using JoinType.LeftOuterJoin

1) productCriteria.SetResultTransformer (Transformers.DistinctRootEntity);

It doesn't work as the conversion is code-sided and since I have .SetFirstResult () and .SetMaxResults () for lookups it doesn't work.

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

It didn't work as I can't fill in the value for Medias.Id in the projections. (Looks like nNibernate API applications) )

Any help would be greatly appreciated

+2


a source to share





All Articles