public void LoadJoined()
{
CustomerQuery cq = new CustomerQuery("c");
EmployeeQuery eq = new EmployeeQuery("e");
EmployeeQuery eq2 = new EmployeeQuery("e2");
OrderQuery oq = new OrderQuery("o");
OrderItemQuery oiq = new OrderItemQuery("oi");
ProductQuery pq = new ProductQuery("p");
cq.Select(
cq.CustomerID,
cq.CustomerSub,
cq.CustomerName,
eq,
eq2.LastName.As("ReportsTo"),
oq.PlacedBy,
oq.OrderDate,
oiq,
pq.ProductName,
pq.Discontinued);
cq.LeftJoin(eq).On(eq.EmployeeID == cq.Manager);
cq.LeftJoin(eq2).On(eq.Supervisor == eq2.EmployeeID);
cq.LeftJoin(oq).On(cq.CustomerID == oq.CustID
&& cq.CustomerSub == oq.CustSub);
cq.LeftJoin(oiq).On(oq.OrderID == oiq.OrderID);
cq.LeftJoin(pq).On(oiq.ProductID == pq.ProductID);
CustomerCollection coll = new CustomerCollection();
coll.es.Connection.Name = "ForeignKeyTest";
Assert.IsTrue(coll.Load(cq));
Assert.AreEqual(69, coll.Count);
}
public bool GetActiveProductIds(int employeeId)
{
ProductQuery prd = new ProductQuery("pq");
//prd.es.Connection.Name = "ForeignKeyTest";
OrderItemQuery item = new OrderItemQuery("oiq");
//item.es.Connection.Name = "ForeignKeyTest";
OrderQuery ord = new OrderQuery("oq");
//ord.es.Connection.Name = "ForeignKeyTest";
CustomerQuery cust = new CustomerQuery("cq");
//cust.es.Connection.Name = "ForeignKeyTest";
EmployeeQuery emp = new EmployeeQuery("eq");
//emp.es.Connection.Name = "ForeignKeyTest";
prd.Select(prd.ProductID);
prd.InnerJoin(item).On(prd.ProductID == item.ProductID);
prd.InnerJoin(ord).On(item.OrderID == ord.OrderID);
prd.InnerJoin(cust).On(ord.CustID == cust.CustomerID &
ord.CustSub == cust.CustomerSub);
prd.InnerJoin(emp).On(cust.Manager == emp.EmployeeID);
prd.Where(emp.EmployeeID == employeeId);
prd.Where(prd.Discontinued == false);
prd.es.Distinct = true;
return this.Load(prd);
}
public void ManualWhereOr()
{
ProductQuery pq = new ProductQuery("p");
pq.es2.Connection.Name = "ForeignKeyTest";
List<int> inList = new List<int>();
inList.Add(8);
inList.Add(9);
esComparison comp = null;
comp = pq.ManualWhere("ProductName", "LIKE", "W%", null, "OR");
comp = pq.ManualWhere("UnitPrice", "LESSTHAN", 10.0, null, "OR");
comp = pq.ManualWhere("ProductID", "IN", inList, null, "OR");
pq.Where(comp);
ProductCollection coll = new ProductCollection();
coll.es.Connection.Name = "ForeignKeyTest";
Assert.IsTrue(coll.Load(pq));
Assert.AreEqual(7, coll.Count);
}
public void ManualWhereAnd()
{
ProductQuery pq = new ProductQuery("p");
pq.es2.Connection.Name = "ForeignKeyTest";
esComparison comp = null;
comp = pq.ManualWhere("Discontinued", "EQUAL", false, null, "AND");
comp = pq.ManualWhere("UnitPrice", "BETWEEN", 0.15, 0.20, "AND");
comp = pq.ManualWhere("ProductID", "GREATERTHAN", 2, null, "AND");
pq.Where(comp);
ProductCollection coll = new ProductCollection();
coll.es.Connection.Name = "ForeignKeyTest";
Assert.IsTrue(coll.Load(pq));
Assert.AreEqual(1, coll.Count);
}
public bool Load(ProductQuery query)
{
this.query = query;
InitQuery(this.query);
return(Query.Load());
}
public void Correlated()
{
OrderItemCollection collection = new OrderItemCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
OrderItemQuery oiq = new OrderItemQuery("oi");
ProductQuery pq = new ProductQuery("p");
// oiq.ProductID in the inner Select is pulled from
// the outer Select, making a correlated SubQuery.
oiq.Select(
oiq.OrderID,
(oiq.Quantity * oiq.UnitPrice).Sum().As("Total")
);
oiq.Where(oiq.ProductID
.In(
pq.Select(pq.ProductID)
.Where(oiq.ProductID == pq.ProductID)
)
);
oiq.GroupBy(oiq.OrderID);
Assert.IsTrue(collection.Load(oiq));
Assert.AreEqual(5, collection.Count);
}
public void MixedANDAndORInOn()
{
ProductCollection collection = new ProductCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
int empId = 1;
ProductQuery prd = new ProductQuery("pq");
OrderItemQuery item = new OrderItemQuery("oiq");
OrderQuery ord = new OrderQuery("oq");
CustomerQuery cust = new CustomerQuery("cq");
EmployeeQuery emp = new EmployeeQuery("eq");
prd.Select(prd.ProductID);
prd.InnerJoin(item).On(prd.ProductID == item.ProductID);
prd.InnerJoin(ord).On(item.OrderID == ord.OrderID);
prd.InnerJoin(cust).On(ord.CustID == cust.CustomerID &
(ord.CustSub == cust.CustomerSub |
ord.EmployeeID == cust.StaffAssigned));
prd.InnerJoin(emp).On(cust.Manager == emp.EmployeeID);
prd.Where(emp.EmployeeID == empId);
prd.Where(prd.Discontinued == false);
prd.OrderBy(prd.ProductID.Ascending);
Assert.IsTrue(collection.Load(prd));
Assert.AreEqual(9, collection.Count);
}
