How can I use OrderBy with an aggregate of a object property with Ardalis Specification?

Question

I am trying to query my postgresql database using Ef core and Ardalis Specification.

For the query I build I want to sort the results by using OrderBy with an aggregate of a property that is on a nested object.

The sorting I want is to sort the list of Clinics by the Clinic that has the most Reviews with high Grades. The grades are on a scale of 1-5.

So if a clinic has two reviews with Grade=5 it should come on top of a clinic that has 5 reviews with Grade=2 or Grade=4. To do this I have to calculate the mean value and then order by the highest

public class Clinic
    {
        public int Id { get; set; }
        public ICollection<Review> Reviews {get; set;}
    }

public class Review
    {
        public int Id { get; set; }
        public decimal Grade {get; set;}
    }

My query so far, which doesnt work as intended as it only gets the highest value. Can I insert a mean-value calculation here somehow?

public ClinicFilterPaginatedSpecification()
{
   Query.OrderByDescending(x => x.Reviews.Max(x => x.Grade ));
}

Running the query:

var filterSpec = new ClinicFilterSpecification();
var itemsOnPage= await _clinicRepo.ListAsync(filterSpec);

Answer 1

I should mention that this is not directly related to the Specification package, in the sense that the expression is not altered in any way. Whatever works on EF, should work through specs as well. We're passing the expression as it is.

Now the question is how EF would behave in this case when you need to aggregate some data from the collections. I think this is optimized in EF Core 5, and Ardalis' suggestion should work. Prior to EF Core 3, this scenario would have involved an explicit Join operation (not quite sure).

Answer 2

As Ivan Stoev notes in his comment, you should be able to use the .Average() command:

public ClinicFilterPaginatedSpecification()
{
   Query.OrderByDescending(clinic => clinic.Reviews.Average(review => review.Grade ));
}

Have you tried this and if so is it working or producing an error?