Nhibernate join with select columns from both tables
Ok after several attempts I am stuck on this one!
I am using NHibernate with QueryOver as below. I have a Product and ProductReview as
public class Product
{
....
public virtual IList<ProductReview> CustomerReviews {get;set;}
....
}
public class ProductReview
{
....
public virtual Product Product {get;set;}
....
}
Mapping on Product side is
HasMany(x => x.CustomerReviews).KeyColumn("ProductId").Inverse().Cascade.None().LazyLoad();
The Query is
Product px = null;
ProductReview rev = null;
var result = CurrentSession
.QueryOver<ProductReview>()
.Where(r => r.IsActive && !r.IsDraft)
.Select(
Projections.Property<ProductReview>(r => r.Id).WithAlias(() => rev.Id),
Projections.Property<ProductReview>(r => r.Title).WithAlias(() => rev.Title)
)
.OrderBy(r => r.ReviewDate).Desc()
.TransformUsing(Transformers.AliasToBean<ProductReview>())
.JoinAlias(r => r.Product, () => px)
.Select(
Projections.Property(() => px.UPC).WithAlias(() => px.UPC),
Projections.Property(() => px.FullName).WithAlias(() => px.FullName)
)
.TransformUsing(Transformers.AliasToBean<Product>())
.Take(5)
.List();
The error is: The value "Reviews.Models.Product" is not of type "Reviews.Models.ProductReview" and cannot be used in this generic collection. Parameter name: value
I really do not want to create another DTO. I would like to get the list of Last 5 ne开发者_StackOverfloww reviews and have it's Product populated (only a few required fields on both entities should be filled).
Is this possible by any means (except raw sql) in NHibernate 3.0?
Product px = null;
ProductReview rev = null;
var result = CurrentSession.QueryOver<ProductReview>()
.Where(r => r.IsActive && !r.IsDraft)
.JoinQueryOver(r => r.Product)
.OrderBy(r => r.ReviewDate).Desc()
.Take(5)
.List();
精彩评论