CODEWITHSUNDEEP
c#linq
webworm
webworm

Reputation: 11019

SQL to LINQ with Case .. When in Order By

I have a SQL query that I would like to transform into a LINQ query but I am not sure how to proceed. I have two entities Product and Price. A Price is linked to a Product by the ProductId. The query grabs the best price for a product based on the quantity of the Product requested.

For example: A product may have a price of $5/unit if the customer wants to purchase 10 units of the product. However, if the customer wants to purchase 100 units of the product then the cost may be $3/unit. This query will obtain the best unit price based upon the requested quantity. The WHERE clause makes sure the correct Product is chosen and excludes any prices if the dates have expired. The CASE statement orders the prices such that the best price is displayed first as long as the requested quantity is reached.

Price Table
--------------------------
Id    ProductId      Quantity     Price    BeginDate   EndDate
=========================================================================
1     1234           10           $5       1/1/2016    2/1/2016
2     1234           100          $3       1/1/2016    2/1/2016
3     1234           100          $1       1/1/2016    1/9/2016
4     1234           500          $2       1/1/2016    2/1/2016



SELECT TOP 1
  Product.Id,
  Product.Name,
  Prices.Price
FROM Products INNER JOIN Prices ON Products.Id = Prices.ProductId
WHERE Product.Id = @ProductId
  AND ((Price.BeginDate IS NULL OR Price.BeginDate < GETDATE()) AND (Price.EndDate IS NULL OR Price.EndDate > GETDATE())) 
ORDER BY
  CASE WHEN Price.Quantity <= @RequestedQuantity THEN Price.Quantity 
   ELSE -Price.Quantity
  END

The parts of the query that are confusing me in LINQ ..

  1. How to filter out those prices where the dates of the price have expired (for example Id = 3).
  2. How to order the collection using a case statement.

Once these are in place then I can select the top result using FirstOrDefault

Upvotes: 1

Views: 1146

Answers (3)

Blue Eyed Behemoth
Blue Eyed Behemoth

Reputation: 3872

If you're using Entity Framework

SELECT TOP 1
  Product.Id,
  Product.Name,
  Prices.Price
FROM Products INNER JOIN Prices ON Products.Id = Prices.ProductId
WHERE Product.Id = @ProductId
  AND ((Price.BeginDate IS NULL OR Price.BeginDate < GETDATE()) AND (Price.EndDate IS NULL OR Price.EndDate > GETDATE())) 
ORDER BY
  CASE WHEN Price.Quantity <= @RequestedQuantity THEN Price.Quantity 
   ELSE -Price.Quantity
  END

Should Look something like

var SelectedProduct = ObjectYourDatabaseIsLabeledAs.Products
    .FirstOrDefault(p => p.Id == ProductId) // Gets matching Ids

Or

var SelectedProduct = ObjectYourDatabaseIsLabeledAs.Products.Find(ProductId)

Then to get the Proper Price

var SelectedPrice = SelectedProduct?.Prices
    .OrderByDesc(o => o.Quantity) // Sets the highest number at the top of the IEnum
    .FirstOrDefault(p => // Take the first/default one where...
        p.EndDate >= DateTime.Today &&
        p.StartDate <= DateTime.Today && // Gets All With Valid Prices
        p.Quantity <= RequestedQuantity) // Removes all the high quantities that you don't want

Upvotes: 2

Ivan Stoev
Ivan Stoev

Reputation: 205589

It's pretty much one to one translation.

SQL

SELECT TOP 1 Product.Id, Product.Name, Prices.Price
FROM Products
INNER JOIN Prices ON Products.Id = Prices.ProductId
WHERE Product.Id = @ProductId
    AND (Price.BeginDate IS NULL OR Price.BeginDate < GETDATE())
    AND (Price.EndDate IS NULL OR Price.EndDate > GETDATE()) 
ORDER BY CASE WHEN Price.Quantity <= @RequestedQuantity THEN Price.Quantity ELSE -Price.Quantity END

LINQ

var productId = ...;
var requestedQuantity = ...;
var date = DateTime.Today;

var query = 
    (from product in db.Products
     join price in db.Prices on product.Id equals price.ProductId
     where product.Id == productId
         && (price.BeginDate == null || price.BeginDate < date)
         && (price.EndDate == null || price.EndDate > date)
     orderby price.Quantity <= requestedQuantity ? price.Quantity : -price.Quantity
     select new { product.Id, product.Name, price.Price }
    ).Take(1);

var result = query.FirstOrDefault();

The only difference is that select goes last, CASE WHEN condition THEN a ELSE b END maps to condition ? a : b and TOP n becomes .Take(n).

Actually Take(1) is not needed here, I've included it just for comparison in case you need TOP 10 for instance.

Upvotes: 1

Felipe Oriani
Felipe Oriani

Reputation: 38598

Try using let keyword to define the column and order it. I didn't test it and I'm not sure it'll work as you expect. Make sure you have all entities and properties defined on it, for sample:

var query = from product in Products 
            join price in Prices on product.Id equals price.ProductId
            let priceOrder = price.Quantity <= requestValue ? price.Quantity : -price.Quantity 
            where ((price.BeginDate == null || Price.BeginDate < DateTime.Now) && (Price.EndDate == null || Price.EndDate > DateTime.Now))
            orderby priceOrder 
            select { product.Id, product.Name, price.Price };

var result = query.FirstOrDefault();

Upvotes: 3

Related Questions