How to get average based on list of Ids in entity framework

Question

I have a table let says Products which contains two column(ProductId, Price). I am getting list of product Ids from client(IOS, Android). Instead of iterating over the list and getting the price one by one and then calculate average, Is there any better way to do so?

Answer 1

If I understand correctly, you have a list with product Ids and want to get the average price for the selected products. You can use a combination of Contains and Average like this:

List<int> productIds = ...;
var averagePrice = db.Products
    .Where(p => productIds.Contains(p.ProductId))
    .Select(p => p.Price)
    .DefaultIfEmpty()
    .Average();

The Select followed by DefaultIfEmpty is needed to avoid exception when the filtered set contains no records.

Update: As per comment, getting several aggregates with a single query is also possible by using group by constant construct:

var info = (from p in db.Products
            where productIds.Contains(p.ProductId)
            group p by 0 into g // any constant would work
            select new
            {
               MinPrice = g.Min(p => p.Price),
               MaxPrice = g.Max(p => p.Price),
               AveragePrice = g.Average(p => p.Price),
            }).FirstOrDefault();
var minPrice = info != null ? info.MinPrice : 0;
var maxPrice = info != null ? info.MaxPrice : 0;
var averagePrice = info != null ? info.AveragePrice : 0;

Answer 2

var average = ProductList.Select(p => p.Price).Average();