Nhibernate Left Outer Join Return First Record of the Join

Posted by Touch on Stack Overflow See other posts from Stack Overflow or by Touch
Published on 2010-05-24T08:45:11Z Indexed on 2010/05/24 8:51 UTC
Read the original article Hit count: 300

Filed under:
|
|

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.

© Stack Overflow or respective owner

Related posts about nhibernate

Related posts about criteria