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 WhereNotIn()
{
TerritoryCollection collection = new TerritoryCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
// SubQuery of Territories that Employee 1 is assigned to.
EmployeeTerritoryQuery etq = new EmployeeTerritoryQuery("et");
etq.Select(etq.TerrID);
etq.Where(etq.EmpID == 1);
// Territories that Employee 1 is not assigned to.
TerritoryQuery tq = new TerritoryQuery("t");
tq.Select(tq.Description);
tq.Where(tq.TerritoryID.NotIn(etq));
tq.OrderBy(tq.TerritoryID.Ascending);
Assert.IsTrue(collection.Load(tq));
Assert.AreEqual(2, collection.Count);
Assert.AreEqual("West", collection[0].GetColumn(
TerritoryMetadata.ColumnNames.Description));
}