linq中使用case when

select中使用case when

linq代码：

Products.Select(P => new
{
  ID = P.ProductID,
  Name = P.Name,
  Color = P.Color,
  Price = (P.Color == "Red" ? P.StandardCost : (P.Color == "Black" ? P.StandardCost + 10 : P.ListPrice))
});

sql原型：

SELECT ProductID, Name, Color,
    CASE
      WHEN Color = 'Red' THEN StandardCost
      WHEN Color = 'Black' THEN StandardCost + 10
      ELSE ListPrice   
    END Price
FROM SalesLT.Product

 

 

where中使用case when

linq代码：

Products
.Where(P => (P.Color == "Red" ? (P.StandardCost > 100) : (P.Color == "Black" ? P.ListPrice > 100 : P.ListPrice == P.StandardCost)))
.Select(P => new
{
  ID = P.ProductID,
  Name = P.Name,
  Color = P.Color,
  StandardCost = P.StandardCost,
  ListPrice = P.ListPrice
});

sql原型：

SELECT ProductID, Name, Color, StandardCost, ListPrice
FROM SalesLT.Product
WHERE (
  (CASE
    WHEN Color = 'Red' THEN
      (CASE
        WHEN StandardCost > 100 THEN 1
        WHEN NOT (StandardCost > 100) THEN 0
        ELSE NULL
       END)
    WHEN Color = 'Black' THEN
      (CASE
        WHEN ListPrice > 100 THEN 1
        WHEN NOT (ListPrice > 100) THEN 0
        ELSE NULL
       END)
    ELSE
      (CASE
        WHEN ListPrice = StandardCost THEN 1
        WHEN NOT (ListPrice = StandardCost) THEN 0
        ELSE NULL
       END)
   END)) = 1

 

 

 

group by中使用case when

linq代码：

Products.GroupBy(P => P.StandardCost > 1000? P.Color : P.SellStartDate.ToString())

 

sql原型：

-- Region Parameters
DECLARE @p0 Decimal(8,4) = 1000
-- EndRegion
SELECT [t1].[value] AS [Key]
FROM (
  SELECT
    (CASE
      WHEN [t0].[StandardCost] > @p0 THEN CONVERT(NVarChar(MAX),[t0].[Color])
      ELSE CONVERT(NVarChar(MAX),[t0].[SellStartDate])
     END) AS [value]
  FROM [SalesLT].[Product] AS [t0]
  ) AS [t1]
GROUP BY [t1].[value]
GO