Nhibernate Left Outer Join Return First Record of the Join
- by Touch
I have the following mappings of which Im trying to bring back 0 - 1 Media Id associated with a Product using a left join (I havnt included my attempt as it confuses the situation)
ICriteria productCriteria = Session.CreateCriteria(typeof(Product));
productCriteria
.CreateAlias("ProductCategories", "pc", JoinType.InnerJoin)
.CreateAlias("pc.ParentCategory", "category")
.CreateAlias("category.ParentCategory", "group")
.Add(Restrictions.Eq("group.Id", 333))
.SetProjection(
Projections.Distinct(
Projections.ProjectionList()
.Add(Projections.Alias(Projections.Property("Id"), "Id"))
.Add(Projections.Alias(Projections.Property("Title"), "Title"))
.Add(Projections.Alias(Projections.Property("Price"), "Price"))
.Add(Projections.Alias(Projections.Property("media.Id"), "SearchResultMediaId")) // I NEED THIS
)
)
.SetResultTransformer(Transformers.AliasToBean<Product>());
IList<Product> products = productCriteria
.SetFirstResult(0)
.SetMaxResults(10)
.List<Product>();
I need the query to populate the SearchResultMediaId with Media.Id, I only want to bring back the first Media in a left outer join, as this is 1 to many association between Product and Media
Product is mapped to Media in the following way
mapping.HasManyToMany<Media>(x => x.Medias)
.Table("ProductMedias")
.ParentKeyColumn("ProductId")
.ChildKeyColumn("MediaId")
.Cascade.AllDeleteOrphan()
.LazyLoad()
.AsBag();
Any Help would be fantastic.
