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 CrossDbJoin()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SqlClientProvider":
// AggregateDb
AggregateTestQuery aq = new AggregateTestQuery("a");
// ForeignKeyTest
EmployeeQuery eq = new EmployeeQuery("e");
eq.Select(eq.LastName, eq.FirstName, aq.Age);
eq.LeftJoin(aq).On(
eq.LastName == aq.LastName &
eq.FirstName == aq.FirstName);
eq.OrderBy(eq.LastName.Ascending,
eq.FirstName.Ascending);
Assert.IsTrue(collection.Load(eq));
Assert.AreEqual(22, collection[2].GetColumn("Age"));
break;
default:
Assert.Ignore("SQL Server only");
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 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 TwoAddStringsWithOrderBy()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery eq = new EmployeeQuery("eq");
eq.Select(eq.EmployeeID,
(eq.LastName + ", " + eq.FirstName).As("FullName"));
eq.OrderBy(eq.EmployeeID, esOrderByDirection.Ascending);
Assert.IsTrue(collection.Load(eq));
string theName = collection[0].GetColumn("FullName") as string;
Assert.AreEqual(11, theName.Length);
}
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 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 MultiExpression()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery eq = new EmployeeQuery("eq");
eq.Select(eq.EmployeeID, eq.Age,
((eq.Age + eq.Supervisor) / 3).As("SomeInt"));
eq.OrderBy(eq.EmployeeID, esOrderByDirection.Ascending);
Assert.IsTrue(collection.Load(eq));
object si = collection[0].GetColumn("SomeInt");
Assert.AreEqual(null, si);
int someInt = Convert.ToInt32(collection[1].GetColumn("SomeInt"));
Assert.AreEqual(7, someInt);
}
public void OneAddIntegers()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.Name = "ForeignKeyTest";
EmployeeQuery eq = new EmployeeQuery("eq");
eq.Select(eq.EmployeeID, eq.Age,
(eq.Age + eq.Supervisor).As("SomeInt"));
eq.OrderBy(eq.EmployeeID, esOrderByDirection.Ascending);
Assert.IsTrue(collection.Load(eq));
object si = collection[0].GetColumn("SomeInt");
Assert.AreEqual(null, si);
int someInt = Convert.ToInt32(collection[1].GetColumn("SomeInt"));
Assert.AreEqual(21, someInt);
}
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;
}
}
}
