Hi,

I am using LLBLGen Pro 2.6, Self servicing pattern, Not using Linq Database : SQL Server 2008

I have a Product entity and CustomerRating entity While displaying product details, I want to get average rating of the product. So I am using:

var product = new ProductEntity(productId);
var rating = product.CustomerRating.GetScalar(CustomerRatingFieldIndex.Rating, 
null, AggregateFunction..Avg, new PredicateExpression(ReviewFields.ProductId == product.Id)),

(Please, Let me know if there is an elegant way to do this)

The problem is: The AVG function in SQL Server returns INT So, if 5 reviews have total rating of 23, it returns 4 instead of 4.6 So, I want to execute a query similar to this -

Select
    AVG(Cast(Rating as Float))
From
    CustomeReview
WHERE
    .....

I found some examples using Linq, but I am not using that as I am not comfortable with it yet.

Thanks!

Hi there,

You should use DBFuncionCall and use it as the expression in the GetScalar. Something like this:

IExpression castExp = new DbFunctionCall("CAST({0} AS FLOAT)",
    new object[] { CustomerRatingFields.Rating });

var rating= product.CustomerRating.GetScalar(CustomerRatingFieldIndex.Rating, castExp, AggregateFunction.Avg, new PredicateExpression(ReviewFields.ProductId == product.Id));