static private void Subquery()
{
OrdersQuery orders = new OrdersQuery("o");
OrderDetailsQuery details = new OrderDetailsQuery("oi");
orders.Select
(
orders.OrderID,
orders.OrderDate,
details.Select
(
details.UnitPrice.Max()
)
.Where(orders.OrderID == details.OrderID).As("MaxUnitPrice")
);
OrdersCollection coll = new OrdersCollection();
if (coll.Load(orders))
{
foreach (Orders order in coll)
{
}
}
}
static private void CorrelatedSubQuery()
{
OrderDetailsQuery oiq = new OrderDetailsQuery("oi");
ProductsQuery pq = new ProductsQuery("p");
oiq.Select(oiq.OrderID, (oiq.Quantity * oiq.UnitPrice).Sum().As("Total"))
.Where(oiq.ProductID
.In(
pq.Select(pq.ProductID).Where(oiq.ProductID == pq.ProductID).Distinct()
)
)
.GroupBy(oiq.OrderID);
OrderDetailsCollection coll = new OrderDetailsCollection();
if (coll.Load(oiq))
{
}
}
public EmployeesCollection Employees_PrefetchSophisticated()
{
// EmployeeID = "1"
EmployeesCollection coll = new EmployeesCollection();
coll.Query.Where(coll.Query.EmployeeID == 1);
// Orders Query (nothing fancy, just ensure we're only getting Orders for EmployeeID = 1
OrdersQuery o = coll.Query.Prefetch <OrdersQuery>(Employees.Prefetch_OrdersCollectionByEmployeeID);
EmployeesQuery e1 = o.GetQuery <EmployeesQuery>();
o.Where(e1.EmployeeID == 1);
// OrderDetailsQuery (here we even limit the Select in addition to EmployeeID = 1) notice the "false"
OrderDetailsQuery od = coll.Query.Prefetch <OrderDetailsQuery>(false, Employees.Prefetch_OrdersCollectionByEmployeeID, Orders.Prefetch_OrderDetailsCollectionByOrderID);
EmployeesQuery e2 = od.GetQuery <EmployeesQuery>();
od.Where(e2.EmployeeID == 1);
od.Select(od.OrderID, od.ProductID, od.UnitPrice);
coll.Query.Load();
return(coll);
}
static private void CaseWhenThenEnd()
{
OrderDetailsQuery oq = new OrderDetailsQuery();
oq.Select
(
oq.Quantity,
oq.UnitPrice,
oq.UnitPrice
.Case()
.When(oq.Quantity < 50).Then(oq.UnitPrice)
.When(oq.Quantity >= 50 && oq.Quantity < 70).Then(oq.UnitPrice * .90)
.When(oq.Quantity >= 70 && oq.Quantity < 99).Then(oq.UnitPrice * .80)
.Else(oq.UnitPrice * .70)
.End().As("Adjusted Unit Price")
).OrderBy(oq.Quantity.Descending);
OrderDetailsCollection coll = new OrderDetailsCollection();
if (coll.Load(oq))
{
}
}