I have the following database structure where products have a default price that can be overridden if a row for that product is found for a given rateID, if that row does not exist the default price is returned. Prices is EntityCollection so It does not implement the IQueryable interface, I want to have this logic inside my model and the current implementation works but using a forloop does not seems very optimal, any idea on how to improve that code?

Tables

Product

ProductID
Name
Price

Rate

RateID
Name

Price

PriceID
ProductID
RateID
Price

My product model:

public partial class Product
{
    public decimal GetPrice(Guid rateId) {

        foreach (Price p in Prices)
            if (p.Rate.RateId == rateId)
                return p.NewPrice;

        return DefaultPrice;
    }
}

My controller :

    public ActionResult Prices() {

        var products = storeDB.Products
            .Include("Family")
            .Include("Prices")
            .OrderBy(product => product.Name)
            .ToList();

        var viewModel = new  ProductPricesViewModel {
            Products = products.ToList(),
            Rates = storeDB.Rates.ToList()
        };

        return View(viewMod开发者_高级运维el);
    }

try

return (from p in Prices where p.Rate.RateId == rateId select p.NewPrice).DefaultIfEmpty (DefaultPrice);

Alternative:

return Prices.Where ( p => p.Rate.RateId == rateId ).Select (p.NewPrice).DefaultIfEmpty (DefaultPrice);

see http://msdn.microsoft.com/de-de/library/bb356814.aspx