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 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);
}
private bool LoadByPrimaryKeyDynamic(System.Int32 productID)
{
ProductQuery query = new ProductQuery();
query.Where(query.ProductID == productID);
return(this.Load(query));
}
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 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);
}