public void SQLWithCastRoundAvg()
{
var od = new Models.Order_Details();
Verify(
Select(CastAsDecimal(Average(Round(od.Discount, 3)), 3)).From(od)
,
@"SELECT cast(avg(round(discount,3)) AS DECIMAL(9,3))
FROM [Order Details]"
);
}
public void SQLWithCastSum()
{
var od = new Models.Order_Details();
Verify(
Select(CastAsDecimal(Sum(od.Discount), 3)).From(od)
,
@"SELECT cast(Sum(discount) AS DECIMAL(9,3))
FROM [Order Details]"
);
}
public void SQLWithSum()
{
var od = new Models.Order_Details();
Verify(
Select(Sum(od.Quantity)).
From(od)
,
@"SELECT SUM(Quantity)
FROM dbo.[Order Details]"
);
}
public void SQLWithNotExists()
{
var od = new Models.Order_Details();
var p = new Models.Products();
Verify(
Select(od.OrderID).From(od).
Where(NotExists(p, p.ProductID.IsEqualTo(od.ProductID).And(p.UnitPrice.IsEqualTo(22))))
,
@"SELECT OrderID
FROM [Order Details]
WHERE NOT EXISTS (SELECT ProductName FROM Products WHERE Products.ProductID = [Order Details].ProductID AND UnitPrice = 22) "
);
}
public void SQLWithCase()
{
var od = new Models.Order_Details();
Verify(
Select(od.OrderID, od.Quantity,
Case(od.Quantity.IsGreaterThan(30), "The quantity is greater than 30")
.When(od.Quantity.IsEqualTo(30), "The quantity is 30")
.Else("The quantity is under 30"))
,
@"SELECT OrderID, Quantity,
CASE WHEN Quantity > 30 THEN 'The quantity is greater than 30'
WHEN Quantity = 30 THEN 'The quantity is 30'
ELSE 'The quantity is under 30'
END AS QuantityText
FROM [Order Details]"
);
}
