Querying entity in NHibernate by average value of subentity

Question

Hi all. I've got an entity Product like this:

[Class(0, Name = "Product", Table = "Products")]
public class Product
{
    private readonly ISet<ProductPropertyValue> _productPropertyValues;
    private readonly ISet<ProductImage> _productImages;
    private readonly ISet<ProductComment> _productComments;
    private readonly IList<Category> _categories;
    private readonly ISet<Rate> _rates;

    public Product()
    {
        _productPropertyValues = new HashedSet<ProductPropertyValue>();
        _productImages = new HashedSet<ProductImage>();
        _productComments = new HashedSet<ProductComment>();
        _categories = new List<Category>();
        _rates = new HashedSet<Rate>(); ;
    }

    [Id(0, Name = "ProductId", Type = "Int32", Column = "ProductID")]
    [Generator(1, Class = "native")]
    public virtual Int32 ProductId { get; set; }

    [Property(0, Name = "Description", Column = "Description", Type = "string")]
    public virtual String Description { get; set; }

    [Property(0, Name = "ShortDescription", Column = "ShortDescription", Type = "string")]
    public virtual String ShortDescription { get; set; }

    [Property(0, Name = "ProductName", Column = "ProductName", Type = "string")]
    public virtual String ProductName { get; set; }

    [Property(0, Name = "UnitPrice", Column = "UnitPrice", Type = "double")]
    public virtual double UnitPrice { get; set; }

    [Property(0, Name = "UnitsInStock", Column = "UnitsInStock", Type = "int")]
    public virtual int UnitsInStock { get; set; }

    [Property(0, Name = "MainImagePath", Column = "MainImagePath", Type = "string")]
    public virtual String MainImagePath { get; set; }

    [Property(0, Name = "NumberOfSales", Column = "NumberOfSales", Type = "int")]
    public virtual int NumberOfSales { get; set; }

    [Property(0, Name = "NumberOfViews", Column = "NumberOfViews", Type = "int")]
    public virtual int NumberOfViews { get; set; }

    [Property(0, Name = "IsSpecial", Column = "IsSpecial", Type = "boolean")]
    public virtual bool IsSpecial { get; set; }

    [Property(0, Name = "AdditionDate", Column = "AdditionDate", Type = "DateTime")]
    public virtual DateTime AdditionDate { get; set; }

    [Property(Name = "Enabled", Column = "Enabled", Type = "boolean")]
    public virtual bool Enabled { get; set; }

    [Bag(0, Name = "Categories", Lazy = true, Table = "Products_Categories",
        Access = "field.camelcase-underscore", Cascade = "none")]
    [Key(1, Column = "ProductID")]
    [ManyToMany(2, Class = "Category", Column = "CategoryID")]
    public virtual ReadOnlyCollection<Category> Categories
    {
        get { return new ReadOnlyCollection<Category>(_categories); }
    }

    [Set(0, Name = "ProductComments", Lazy = true, Access = "field.camelcase-underscore",
        Cascade = "save-update", Inverse = true)]
    [Key(1, Column = "ProductID")]
    [OneToMany(2, Class = "ProductComment")]
    public virtual ReadOnlyCollection<ProductComment> ProductComments
    {
        get { return new ReadOnlyCollection<ProductComment>(new List<ProductComment>(_productComments)); }
    }

    [Set(0, Name = "ProductImages", Lazy = true, Access = "field.camelcase-underscore",
        Cascade = "save-update")]
    [Key(1, Column = "ProductID")]
    [OneToMany(2, Class = "ProductImage")]
    public virtual ReadOnlyCollection<ProductImage> ProductImages
    {
        get { return new ReadOnlyCollection<ProductImage>(new List<ProductImage>(_productImages)); }
    }

    [Set(0, Name = "ProductPropertyValues", Lazy = true, Access = "field.camelcase-underscore",
        Cascade = "save-update")]
    [Key(1, Column = "ProductID")]
    [OneToMany(2, Class = "ProductPropertyValue")]
    public virtual ReadOnlyCollection<ProductPropertyValue> ProductPropertyValues
    {
        get { return new ReadOnlyCollection<ProductPropertyValue>(new List<ProductPropertyValue>(_productPropertyValues)); }
    }

    [Set(0, Name = "Rates", Lazy = true, Access = "field.camelcase-underscore",
        Cascade = "save-update")]
    [Key(1, Column = "ProductID")]
    [OneToMany(2, Class = "Rate")]
    public virtual ReadOnlyCollection<Rate> Rates
    {
        get { return new ReadOnlyCollection<Rate>(new List<Rate>(_rates)); }
    }

    /// <summary>
    /// Gets everage rate.
    /// </summary>
    /// <value>The everage rate.</value>
    public double AverageRate
    {
        get
        {
            if (_rates.Count != 0)
            {
                return _rates.Average(x => x.Value);    
            }
            return 0;
        }
    }

    /// <summary>
    /// Gets rounded average rate.
    /// </summary>
    /// <value>The rounded average rate.</value>
    public Int32 AverageRateRounded
    {
        get
        {
            return (int)Math.Round(AverageRate);
        }
    }

    /// <summary>
    /// Gets the number of votes.
    /// </summary>
    /// <value>The number of votes.</value>
    public Int32 NumberOfVotes
    {
        get
        {
            return _rates.Count;
        }
    }


    /// <summary>
    /// Add new comment to current product
    /// </summary>
    /// <param name="commentToAdd">Comment to add</param>
    public virtual void AddComment(ProductComment commentToAdd)
    {
        if (commentToAdd != null)
        {
            if (!_productComments.Contains(commentToAdd))
            {
                _productComments.Add(commentToAdd);
            }
        }
    }

    /// <summary>
    /// Delete comment from current product 
    /// </summary>
    /// <param name="commentToDelete"></param>
    public virtual void DeleteComment(ProductComment commentToDelete)
    {
        if (commentToDelete != null)
        {
            if (_productComments.Contains(commentToDelete))
            {
                _productComments.Remove(commentToDelete);
            }
        }
    }


    /// <summary>
    /// Add image to current product 
    /// </summary>
    /// <param name="imageToAdd">Image to add</param>
    public virtual void AddImage(ProductImage imageToAdd)
    {
        if (imageToAdd != null)
        {
            if (!_productImages.Contains(imageToAdd))
            {
                _productImages.Add(imageToAdd);
            }
        }
    }

    /// <summary>
    /// Delete image from current product
    /// </summary>
    /// <param name="imageToDelete">Image to delete</param>
    public virtual void DeleteImage(ProductImage imageToDelete)
    {
        if (imageToDelete != null)
        {
            if (_productImages.Contains(imageToDelete))
            {
                _productImages.Remove(imageToDelete);
            }
        }
    }


    /// <summary>
    /// Add property to current product
    /// </summary>
    /// <param name="valueToAdd">Value to add</param>
    public virtual void AddProrerty(ProductPropertyValue valueToAdd)
    {
        if (valueToAdd != null)
        {
            if (!_productPropertyValues.Contains(valueToAdd))
            {
                _productPropertyValues.Add(valueToAdd);
            }
        }
    }

    /// <summary>
    /// Delete property from current product
    /// </summary>
    /// <param name="valueToDelete">Value to delete</param>
    public virtual void DeleteProperty(ProductPropertyValue valueToDelete)
    {
        if (valueToDelete != null)
        {
            if (_productPropertyValues.Contains(valueToDelete))
            {
                _productPropertyValues.Remove(valueToDelete);
            }
        }
    }


    /// <summary>
    /// Adds rate to current product.
    /// </summary>
    /// <param name="rate">The rate.</param>
    public virtual void AddRate(Rate rate)
    {
        if (rate != null)
        {
            if (!_rates.Contains(rate))
            {
                _rates.Add(rate);
            }
        }
    }

    /// <summary>
    /// Deletes rate from product.
    /// </summary>
    /// <param name="rate">The rate.</param>
    public virtual void DeleteRate(Rate rate)
    {
        if (rate != null)
        {
            if (_rates.Contains(rate))
            {
                _rates.Remove(rate);
            }
        }
    }
}

and a Rate entity like this:

[Class(0, Name = "Rate", Table = "Rates")]
public class Rate
{
    [CompositeId(1)]
    [KeyManyToOne(2, Name = "Product", Class = "Product", Column = "ProductId")]
    [KeyManyToOne(3, Name = "User", Class = "User", Column = "UserId")]
    public virtual Product Product { get; set; }

    public virtual User User { get; set; }

    [Property(0, Name = "Value", Column = "Value", Type = "Int32")]
    public virtual Int32 Value { get; set; }

}

And I need to fetch all products with average rating in DESC order. Also it should be filterable by any field from Product, e.g. price: from $1000 to $5000. Thanks.

Answer 1

Should work mostly how you'd think.

In HQL:

select p
from Product p join p.Rates r
where (p.Price >= 1000 and p.Price <= 5000)
order by avg(r.Value) desc

Answer 2

I'd like to play a little bit with it, but I'm a bit too lazy to setup a little test project.

In any case, if you want to fetch those products, to display them in an overview or something like that, I would make use of Projections and the AliasToBean transformer, so that NHibernate issues a query that will be simpler, and retrieve only those values that you need to display your overview. In order to do so, you will have to create another class offcourse, which will hold the values that will be retrieved.