Posts tagged by: "calculations"

Making Calculations In LINQ

Posted in: ASP.NET

I am currently working on an ASP.NET 4.0  e-commerce site using Entity Framework alongside LINQ. I came across a small issue when I needed to carry out some calculations based on product pricing and the discounts that would need to be applied based on a specific customers allowance.

You maybe thinking, what’s the issue? Well I wanted to be able to make the calculations within my LINQ query since both product pricing and customer discount amounts are stored in the database. So initially wrote the following code:

using (MyEntities myContext = new MyEntities())
    int productPrice = (from p in myContext.Products
                        where p.ProductID == 1
                        select p.Price).SingleOrDefault(); 
    int customerDiscount = (from cd in myContext.CustomerDiscounts
                            where cd.CustomerID == 15
                            select cd.Discount).SingleOrDefault(); 
    int productDiscountedPrice = productPrice - ((productPrice * customerDiscount) / 100);

As you can see from my code above, I had to write two separate LINQ queries in order to get the values I wanted and then base my calculations on those values. But I was determined to carry out my calculations in one query. Luckily, LINQ has has a really cool keyword that I totally missed. It’s the “let” keyword which allows you to declare a variable and assign it a calculated value.

using (MyEntities myContext = new MyEntities())
    int productDiscount = (from cd in myContext.CustomerDiscounts
                           join p in myContext.Products on cd.ProductID equals cd.ProductID
                           where p.ProductID == 1 && cd.CustomerID == 15
                           let discountAmount = p.Price - ((p.Price * cd.Discount) / 100)
                           select discountAmount).SingleOrDefault();

Since my database schema allowed me to join my “CustomerDiscount” and “Products” table, I was able to join the two tables and retrieve values I required through one query.