public void SQLWithISNull_1()
{
var p = new Models.Products();
Verify(
Select(p.ProductName, Multiply(p.UnitPrice, Add(p.UnitsInStock, IfNull(p.UnitsOnOrder, 0))))
,
@"SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0))
FROM Products"
);
}
public void SQLWithNotBetween()
{
var p = new Models.Products();
Verify(
Select(p.ProductID).Where(Not(p.UnitPrice.IsBetween(10, 20)))
,
@"SELECT ProductID FROM Products
WHERE UnitPrice Not BETWEEN 10 AND 20"
);
}
public void SQLWithISNull()
{
var p = new Models.Products();
Verify(
Select(p.ProductName, IfNull(p.UnitsOnOrder, 0))
,
@"SELECT ProductName, ISNULL(UnitsOnOrder, 0)
FROM Products"
);
}
public void SQLWithAvg()
{
var p = new Models.Products();
Verify(
Select(Average(p.UnitPrice)).From(p)
,
@"SELECT AVG(UnitPrice)
FROM Products"
);
}
public void SQLWithCount()
{
var p = new Models.Products();
Verify(
Select(Count(p.ProductID)).From(p)
,
@"SELECT COUNT(ProductID)
FROM Products"
);
}
public void SQLWithMax()
{
var p = new Models.Products();
Verify(
Select(Max(p.UnitPrice)).From(p)
,
@"SELECT MAX(UnitPrice) AS LargestPrice
FROM Products"
);
}
public void SQLWithMin()
{
var p = new Models.Products();
Verify(
Select(Min(p.UnitPrice)).From(p)
,
@"SELECT MIN(UnitPrice) AS SmallestPrice
FROM Products"
);
}
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) "
);
}
