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
TitleProductsProductCategories
ProductCategoryId
ProductIDProduct
Id
NameProductMedias
PRODUCTID
MediaIdMedias
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
a source to share
No one has answered this question yet
See similar questions:
or similar: