public void LeftWithExplicitParen()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery eq = new EmployeeQuery("eq");
CustomerQuery cq = new CustomerQuery("cq");
eq.Select(eq.EmployeeID, eq.LastName, cq.CustomerName);
eq.LeftJoin(cq).On(eq.EmployeeID == cq.StaffAssigned);
eq.Where(eq.Age == 20);
eq.Where(new esComparison(esParenthesis.Open));
eq.es.DefaultConjunction = esConjunction.Or;
for (int i = 0; i < 4; i++)
{
eq.Where(
eq.Supervisor == i &
eq.EmployeeID == i + 1);
}
eq.Where(new esComparison(esParenthesis.Close));
Assert.IsTrue(collection.Load(eq));
Assert.AreEqual(5, collection.Count);
}
public void WhereExists()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
// SubQuery of Employees with a null Supervisor column.
EmployeeQuery sq = new EmployeeQuery("s");
sq.es.Distinct = true;
sq.Select(sq.EmployeeID);
sq.Where(sq.Supervisor.IsNull());
// If even one employee has a null supervisor,
// i.e., the above query has a result set,
// then run a list of all employees.
EmployeeQuery eq = new EmployeeQuery("e");
eq.Select(eq.EmployeeID, eq.Supervisor);
eq.Where(eq.Exists(sq));
Assert.IsTrue(collection.Load(eq));
Assert.AreEqual(5, collection.Count);
}
public void TestSerializeQuery()
{
EmployeeQuery query = new EmployeeQuery("e");
query.Where(query.EmployeeID.In(1, 2, 3, new List <object>()
{
1, 2, 3
}));
string qq = EmployeeQuery.SerializeHelper.ToXml(query);
List <Type> types = new List <Type>();
types.Add(typeof(EmployeeQuery));
EmployeeQuery employeeQuery = EmployeeQuery.SerializeHelper.FromXml(
qq, typeof(EmployeeQuery), types) as EmployeeQuery;
EmployeeCollection c = new EmployeeCollection();
c.es.Connection.Name = "ForeignKeyTest";
c.Load(employeeQuery);
Assert.IsTrue(c.Count == 3);
}
public void InnerJoinFourTables()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery emp = new EmployeeQuery("e");
EmployeeTerritoryQuery empTerr = new EmployeeTerritoryQuery("et");
TerritoryQuery terr = new TerritoryQuery("t");
TerritoryExQuery terrEx = new TerritoryExQuery("tx");
emp.Select(emp.FirstName, emp.LastName, terr.Description.As("Territory"), terrEx.Notes);
emp.InnerJoin(empTerr).On(emp.EmployeeID == empTerr.EmpID);
emp.InnerJoin(terr).On(terr.TerritoryID == empTerr.TerrID);
emp.InnerJoin(terrEx).On(terrEx.TerritoryID == terr.TerritoryID);
emp.Where(terrEx.Notes.IsNotNull());
Assert.IsTrue(collection.Load(emp));
Assert.AreEqual(2, collection.Count);
string theName = collection[1].GetColumn("Territory") as string;
Assert.AreEqual("North", theName);
}
public void WhereInANDedTogetherOperator()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery eq1 = new EmployeeQuery("e1");
EmployeeQuery eq2 = new EmployeeQuery("e2");
EmployeeQuery eq3 = new EmployeeQuery("e3");
eq2.Select(eq2.EmployeeID);
eq3.Select(eq3.EmployeeID);
eq1.Where(eq1.EmployeeID.In(eq2) & eq1.EmployeeID.In(eq3));
Assert.IsTrue(collection.Load(eq1));
Assert.AreEqual(5, collection.Count);
string lq = collection.Query.es.LastQuery;
string[] one = lq.Split('1');
Assert.AreEqual(4, one.GetLength(0));
string[] two = lq.Split('2');
Assert.AreEqual(3, two.GetLength(0));
string[] three = lq.Split('3');
Assert.AreEqual(3, three.GetLength(0));
}
public void RightWithWhereIn()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SQLiteProvider":
Assert.Ignore("RIGHT JOIN not supported.");
break;
default:
List <string> custList = new List <string>();
custList.Add("01001");
custList.Add("40000");
custList.Add("XXXXX");
EmployeeQuery eq = new EmployeeQuery("eq");
CustomerQuery cq = new CustomerQuery("cq");
eq.Select(eq.EmployeeID, eq.LastName, cq.CustomerID, cq.CustomerName);
eq.RightJoin(cq).On(eq.EmployeeID == cq.Manager);
eq.Where(cq.CustomerID.In(custList));
EmployeeCollection coll = new EmployeeCollection();
coll.es.Connection.Name = "ForeignKeyTest";
Assert.IsTrue(coll.Load(eq));
Assert.AreEqual(14, coll.Count);
break;
}
}
public void WhereWithJoin()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
// SubQuery of Territories
TerritoryQuery tq = new TerritoryQuery("t");
tq.Select(tq.TerritoryID);
tq.Where(tq.Description == "North" |
tq.Description == "West");
// EmployeeTerritory Query for Join
EmployeeTerritoryQuery etq = new EmployeeTerritoryQuery("et");
// Employees matching those territories
EmployeeQuery eq = new EmployeeQuery("e");
eq.es.Distinct = true;
eq.Select(eq.EmployeeID, etq.TerrID);
eq.LeftJoin(etq).On(
eq.EmployeeID == etq.EmpID);
eq.Where(etq.TerrID.In(tq));
eq.OrderBy(eq.EmployeeID.Ascending);
Assert.IsTrue(collection.Load(eq));
Assert.AreEqual(3, collection.Count);
}
public void LeftWithContains()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SqlClientProvider":
string nameTerm =
"acme NEAR company";
EmployeeQuery eq = new EmployeeQuery("eq");
CustomerQuery cq = new CustomerQuery("cq");
eq.Select(eq.EmployeeID, eq.LastName, cq.CustomerName);
eq.LeftJoin(cq).On(eq.EmployeeID == cq.StaffAssigned);
eq.Where(cq.CustomerName.Contains(nameTerm));
Assert.IsTrue(collection.Load(eq));
Assert.AreEqual(2, collection.Count);
break;
default:
Assert.Ignore("Not supported");
break;
}
}
public void SingleZeroToMany()
{
// The main Employee query
EmployeeQuery eq1 = new EmployeeQuery("e");
eq1.Where(eq1.EmployeeID < 3);
eq1.OrderBy(eq1.EmployeeID.Ascending);
// The Order Collection
OrderQuery oq1 = eq1.Prefetch <OrderQuery>(Employee.Prefetch_OrderCollectionByEmployeeID);
EmployeeQuery eq2 = oq1.GetQuery <EmployeeQuery>();
oq1.Where(eq2.EmployeeID < 3);
// Pre-test the Order query
OrderCollection oColl = new OrderCollection();
oColl.es.Connection.Name = "ForeignKeyTest";
oColl.Load(oq1);
Assert.AreEqual(3, oColl.Count, "Order pre-test");
// This will Prefetch the Order query
EmployeeCollection coll = new EmployeeCollection();
coll.es.Connection.Name = "ForeignKeyTest";
//coll.es.IsLazyLoadDisabled = true;
coll.Load(eq1);
foreach (Employee emp in coll)
{
emp.es.IsLazyLoadDisabled = true;
switch (emp.EmployeeID.Value)
{
case 1:
Assert.AreEqual(1, emp.EmployeeID.Value);
Assert.AreEqual(1, emp.OrderCollectionByEmployeeID.Count);
break;
case 2:
Assert.AreEqual(2, emp.EmployeeID.Value);
Assert.AreEqual(2, emp.OrderCollectionByEmployeeID.Count);
break;
default:
Assert.Fail("Only employees 1 and 2 should be loaded.");
break;
}
}
}
public void SimpleIntersect()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.Name = "ForeignKeyTest";
EmployeeQuery eq1 = new EmployeeQuery("eq1");
EmployeeQuery eq2 = new EmployeeQuery("eq2");
// Only includes rows with both "n" and"a"
eq1.Where(eq1.FirstName.Like("%n%"));
eq1.Intersect(eq2);
eq2.Where(eq2.FirstName.Like("%a%"));
Assert.IsTrue(collection.Load(eq1));
Assert.AreEqual(2, collection.Count);
}
public void SimpleUnion()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.Name = "ForeignKeyTest";
EmployeeQuery eq1 = new EmployeeQuery("eq1");
EmployeeQuery eq2 = new EmployeeQuery("eq2");
// This leaves out the record with Age 30
eq1.Where(eq1.Age < 30);
eq1.Union(eq2);
eq2.Where(eq2.Age > 30);
Assert.IsTrue(collection.Load(eq1));
Assert.AreEqual(4, collection.Count);
}
public void SimpleExcept()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.Name = "ForeignKeyTest";
EmployeeQuery eq1 = new EmployeeQuery("eq1");
EmployeeQuery eq2 = new EmployeeQuery("eq2");
// Includes all "J"s except "Jim"
eq1.Where(eq1.FirstName.Like("%J%"));
eq1.Except(eq2);
eq2.Where(eq2.FirstName == "Jim");
Assert.IsTrue(collection.Load(eq1));
Assert.AreEqual(3, collection.Count);
}
public void LeftWithDateComparisonInOn()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery eq = new EmployeeQuery("eq");
CustomerQuery cq = new CustomerQuery("cq");
eq.Select(eq.LastName);
eq.LeftJoin(cq).On(eq.EmployeeID == cq.StaffAssigned &
cq.DateAdded < Convert.ToDateTime("2005-12-31"));
eq.Where(cq.DateAdded < Convert.ToDateTime("2000-12-31"));
eq.OrderBy(eq.LastName.Ascending);
Assert.IsTrue(collection.Load(eq));
Assert.AreEqual(7, collection.Count);
}
public void SerializeDeserializeJoin()
{
if (aggTest.es.Connection.Name == "SqlCe")
{
Assert.Ignore("Not tested for SqlCe.");
}
else
{
// Test serializing a DynamicQuery
// Must use binary serialization
// Xml serialization doesn't serialize all of your private properties
// 1) Create our Query on the client
EmployeeQuery emp = new EmployeeQuery("eq");
//emp.es.Connection.Name = "ForeignKeyTest";
EmployeeTerritoryQuery et = new EmployeeTerritoryQuery("etq");
//et.es.Connection.Name = "ForeignKeyTest";
emp.Select(emp.FirstName, emp.LastName, et.TerrID);
emp.InnerJoin(et).On(emp.EmployeeID == et.EmpID);
emp.Where(emp.LastName.Like("S%"));
// 2) Serialize it in binary
BinaryFormatter bf = new BinaryFormatter();
MemoryStream ms = new MemoryStream();
bf.Serialize(ms, emp);
byte[] query = ms.ToArray();
// 3) Send it over the wire
// 4) Deserialize it on the Server
bf = new BinaryFormatter();
ms = new MemoryStream(query);
EmployeeQuery newQuery = bf.Deserialize(ms) as EmployeeQuery;
// Now load it
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.Name = "ForeignKeyTest";
collection.Load(newQuery);
Assert.AreEqual(5, collection.Count);
Assert.AreEqual("S", collection[0].LastName.Substring(0, 1));
}
}
public void TwoAddStringsWithToUpper()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery eq = new EmployeeQuery("eq");
eq.Select(eq.EmployeeID,
(eq.LastName.ToUpper() + ", " + eq.FirstName).As("FullName"));
eq.Where(eq.LastName == "Doe");
eq.OrderBy(eq.FirstName.Ascending);
Assert.IsTrue(collection.Load(eq));
string theName = collection[0].GetColumn("FullName") as string;
Assert.AreEqual("DOE, Jane", theName);
}
public void AnyNestedBySubQuery()
{
OrderCollection collection = new OrderCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SQLiteProvider":
Assert.Ignore("Not supported by SQLite.");
break;
default:
// Employees whose LastName begins with 'S'.
EmployeeQuery eq = new EmployeeQuery("e");
eq.Select(eq.EmployeeID);
eq.Where(eq.LastName.Like("S%"));
// DateAdded for Customers whose Managers are in the
// EmployeeQuery above.
CustomerQuery cq = new CustomerQuery("c");
cq.es.Any = true;
cq.Select(cq.DateAdded);
cq.Where(cq.Manager.In(eq));
// OrderID and CustID where the OrderDate is
// less than any one of the dates in the CustomerQuery above.
OrderQuery oq = new OrderQuery("o");
oq.Select(
oq.OrderID,
oq.CustID
);
oq.Where(oq.OrderDate < cq);
Assert.IsTrue(collection.Load(oq));
Assert.AreEqual(8, collection.Count);
break;
}
}
public void NestedBySubQuery()
{
OrderCollection collection = new OrderCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
// This is the same as the traditional nested SubQuery
// in the 'Nested' test, but is easier to construct
// and understand.
// The key is to start with the innermost SubQuery,
// and work your way out to the outermost Query.
// Employees whose LastName begins with 'S'.
EmployeeQuery eq = new EmployeeQuery("e");
eq.Select(eq.EmployeeID);
eq.Where(eq.LastName.Like("S%"));
// DateAdded for Customers whose Managers are in the
// EmployeeQuery above.
CustomerQuery cq = new CustomerQuery("c");
cq.Select(cq.DateAdded);
cq.Where(cq.Manager.In(eq));
// OrderID and CustID where the OrderDate is in the
// CustomerQuery above.
OrderQuery oq = new OrderQuery("o");
oq.Select(
oq.OrderID,
oq.CustID
);
oq.Where(oq.OrderDate.In(cq));
Assert.IsTrue(collection.Load(oq));
Assert.AreEqual(2, collection.Count);
}
public void WhereExistsANDed()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery eq1 = new EmployeeQuery("e1");
EmployeeQuery eq2 = new EmployeeQuery("e2");
eq1.Where(eq1.EmployeeID > 2, eq1.Exists(eq2));
Assert.IsTrue(collection.Load(eq1));
Assert.AreEqual(3, collection.Count);
string lq = collection.Query.es.LastQuery;
string[] one = lq.Split('1');
Assert.AreEqual(4, one.GetLength(0));
string[] two = lq.Split('2');
Assert.AreEqual(2, two.GetLength(0));
}
public void LeftJoinFourTablesWithWhere()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery emp = new EmployeeQuery("e");
EmployeeTerritoryQuery empTerr = new EmployeeTerritoryQuery("et");
TerritoryQuery terr = new TerritoryQuery("t");
TerritoryExQuery terrEx = new TerritoryExQuery("tx");
emp.Select(emp.FirstName, emp.LastName, terr.Description.As("Territory"), terrEx.Notes);
emp.LeftJoin(empTerr).On(emp.EmployeeID == empTerr.EmpID);
emp.LeftJoin(terr).On(empTerr.TerrID == terr.TerritoryID);
emp.LeftJoin(terrEx).On(terr.TerritoryID == terrEx.TerritoryID);
emp.Where(emp.FirstName.Trim().Like("J___"));
Assert.IsTrue(collection.Load(emp));
Assert.AreEqual(7, collection.Count);
}
public void CombineJoinQueriedCollections()
{
EmployeeQuery eq1 = new EmployeeQuery("e1");
CustomerQuery cq1 = new CustomerQuery("c1");
eq1.Select(eq1, cq1.CustomerName);
eq1.InnerJoin(cq1).On(eq1.EmployeeID == cq1.Manager);
eq1.Where(eq1.EmployeeID == 1);
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
collection.Load(eq1);
Assert.AreEqual(35, collection.Count);
EmployeeQuery eq2 = new EmployeeQuery("e2");
CustomerQuery cq2 = new CustomerQuery("c2");
eq2.Select(eq2, cq2.CustomerName);
eq2.InnerJoin(cq2).On(eq2.EmployeeID == cq2.Manager);
eq2.Where(eq2.EmployeeID == 2);
EmployeeCollection collection2 = new EmployeeCollection();
collection2.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection2.es.Connection);
collection2.Load(eq2);
Assert.AreEqual(12, collection2.Count);
collection.Combine(collection2);
Assert.AreEqual(47, collection.Count);
foreach (Employee emp in collection)
{
string custName = CustomerMetadata.ColumnNames.CustomerName;
Assert.IsTrue(emp.GetColumn(custName).ToString().Length > 0);
}
}
public void JoinWithPaging()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.MSAccessProvider":
case "EntitySpaces.SqlServerCeProvider":
case "EntitySpaces.VistaDBProvider":
case "EntitySpaces.VistaDB4Provider":
Assert.Ignore("Not supported");
break;
default:
EmployeeQuery emp = new EmployeeQuery("e");
EmployeeTerritoryQuery empTerr = new EmployeeTerritoryQuery("et");
TerritoryQuery terr = new TerritoryQuery("t");
TerritoryExQuery terrEx = new TerritoryExQuery("tx");
emp.Select(emp, terr.Description.As("Territory"), terrEx.Notes);
emp.InnerJoin(empTerr).On(empTerr.TerrID == emp.EmployeeID);
emp.InnerJoin(terr).On(terr.TerritoryID == empTerr.TerrID);
emp.InnerJoin(terrEx).On(terrEx.TerritoryID == terr.TerritoryID);
emp.Where(terrEx.Notes.IsNotNull());
emp.OrderBy(emp.FirstName.Ascending);
emp.es.PageNumber = 1;
emp.es.PageSize = 20;
Assert.IsTrue(collection.Load(emp));
Assert.AreEqual(2, collection.Count);
break;
}
}
public void WhereExistsFalse()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
// EmployeeID is required and will never be NULL
EmployeeQuery sq = new EmployeeQuery("s");
sq.es.Distinct = true;
sq.Select(sq.EmployeeID);
sq.Where(sq.EmployeeID.IsNull());
// This should produce no results as the
// inner query does not exist.
EmployeeQuery eq = new EmployeeQuery("e");
eq.Select(eq.EmployeeID, eq.Supervisor);
eq.Where(eq.Exists(sq));
Assert.IsFalse(collection.Load(eq));
}
public void NestedZeroToMany()
{
// The main Employee query
EmployeeQuery eq1 = new EmployeeQuery("e");
eq1.Where(eq1.EmployeeID < 4);
eq1.OrderBy(eq1.EmployeeID.Ascending);
// The Order Collection
OrderQuery oq1 = eq1.Prefetch <OrderQuery>(Employee.Prefetch_OrderCollectionByEmployeeID);
EmployeeQuery eq2 = oq1.GetQuery <EmployeeQuery>();
oq1.Where(eq2.EmployeeID < 4 && oq1.PlacedBy < 3);
// Pre-test the Order query
OrderCollection oColl = new OrderCollection();
oColl.es.Connection.Name = "ForeignKeyTest";
oColl.Load(oq1);
Assert.AreEqual(5, oColl.Count, "Order pre-test");
// The OrderItem Collection
OrderItemQuery oiq1 = eq1.Prefetch <OrderItemQuery>(Employee.Prefetch_OrderCollectionByEmployeeID, Order.Prefetch_OrderItemCollectionByOrderID);
EmployeeQuery eq3 = oiq1.GetQuery <EmployeeQuery>();
OrderQuery oq2 = oiq1.GetQuery <OrderQuery>();
oiq1.Where(eq3.EmployeeID < 4 && oq2.PlacedBy < 3 && oiq1.Quantity < 100);
// Pre-test the OrderItem query
OrderItemCollection oiColl = new OrderItemCollection();
oiColl.es.Connection.Name = "ForeignKeyTest";
oiColl.Load(oiq1);
Assert.AreEqual(4, oiColl.Count, "OrderItem pre-test");
// Will Prefetch the Order and OrderItems queries
EmployeeCollection coll = new EmployeeCollection();
coll.es.Connection.Name = "ForeignKeyTest";
//coll.es.IsLazyLoadDisabled = true;
coll.Load(eq1);
foreach (Employee emp in coll)
{
emp.es.IsLazyLoadDisabled = true;
switch (emp.EmployeeID.Value)
{
case 1:
Assert.AreEqual(1, emp.EmployeeID.Value);
Assert.AreEqual(0, emp.OrderCollectionByEmployeeID.Count);
break;
case 2:
Assert.AreEqual(2, emp.EmployeeID.Value);
Assert.AreEqual(2, emp.OrderCollectionByEmployeeID.Count);
foreach (Order o in emp.OrderCollectionByEmployeeID)
{
Assert.Less(0, o.OrderItemCollectionByOrderID.Count);
}
break;
case 3:
Assert.AreEqual(3, emp.EmployeeID.Value);
Assert.AreEqual(3, emp.OrderCollectionByEmployeeID.Count);
foreach (Order o in emp.OrderCollectionByEmployeeID)
{
Assert.AreEqual(0, o.OrderItemCollectionByOrderID.Count);
}
break;
default:
Assert.Fail("Only employees 1, 2, and 3 should be loaded.");
break;
}
}
}
public void NestedZeroToManyEntity()
{
//The main Employee query
EmployeeQuery eq1 = new EmployeeQuery();
eq1.Where(eq1.EmployeeID == 1);
// Prefetch Employee Customers by Manager
CustomerQuery cq1 = eq1.Prefetch <CustomerQuery>(
Employee.Prefetch_CustomerCollectionByManager);
EmployeeQuery eq2 = cq1.GetQuery <EmployeeQuery>();
cq1.Where(eq2.EmployeeID == 1);
// Prefetch Employee Customers Orders (composite FK)
OrderQuery oq1 = eq1.Prefetch <OrderQuery>(
Employee.Prefetch_CustomerCollectionByManager,
Customer.Prefetch_OrderCollectionByCustID);
EmployeeQuery eq3 = oq1.GetQuery <EmployeeQuery>();
oq1.Where(eq3.EmployeeID == 1);
// Prefetch Employee Customers Orders OrderItems
OrderItemQuery oiq1 = eq1.Prefetch <OrderItemQuery>(
Employee.Prefetch_CustomerCollectionByManager,
Customer.Prefetch_OrderCollectionByCustID,
Order.Prefetch_OrderItemCollectionByOrderID);
EmployeeQuery eq4 = oiq1.GetQuery <EmployeeQuery>();
oiq1.Where(eq4.EmployeeID == 1);
// Prefetch Employee Customers by StaffAssigned
CustomerQuery cq2 = eq1.Prefetch <CustomerQuery>(
Employee.Prefetch_CustomerCollectionByStaffAssigned);
EmployeeQuery eq5 = cq2.GetQuery <EmployeeQuery>();
cq2.Where(eq5.EmployeeID == 1);
Employee emp = new Employee();
emp.es.Connection.Name = "ForeignKeyTest";
emp.Load(eq1);
Assert.AreEqual(1, emp.EmployeeID.Value);
Assert.AreEqual(35, emp.CustomerCollectionByManager.Count);
Assert.AreEqual(2, emp.CustomerCollectionByStaffAssigned.Count);
Assert.AreEqual(0, emp.EmployeeCollectionBySupervisor.Count, "These 2 are not Prefetched");
foreach (Customer c in emp.CustomerCollectionByManager)
{
if (c.CustomerID == "01001" && c.CustomerSub == "001")
{
Assert.AreEqual(3, c.OrderCollectionByCustID.Count);
foreach (Order o in c.OrderCollectionByCustID)
{
if (o.OrderID == 1)
{
Assert.AreEqual(3, o.OrderItemCollectionByOrderID.Count);
}
}
}
}
}