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); }
/// <summary> /// Get Territory /// </summary> public void GetTerritory() { // Create the manager with the api key Route4MeManager route4Me = new Route4MeManager(c_ApiKey); string territoryId = "596A2A44FE9FB19EEB9C3C072BF2D0BE"; TerritoryQuery territoryQuery1 = new TerritoryQuery { TerritoryId = territoryId, addresses = 1 }; // Run the query string errorString = ""; TerritoryZone territory = route4Me.GetTerritory(territoryQuery1, out errorString); Console.WriteLine(""); if (territory != null) { Console.WriteLine("GetTerritory executed successfully"); Console.WriteLine("Territory ID: {0}", territory.TerritoryId); } else { Console.WriteLine("GetTerritory error: {0}", errorString); } }
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)); }
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 JoinFourTablesInnerLeft() { EmployeeCollection collection = new EmployeeCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); switch (collection.es.Connection.ProviderSignature.DataProviderName) { case "EntitySpaces.MSAccessProvider": 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.FirstName, emp.LastName, terr.Description.As("Territory"), terrEx.Notes); emp.LeftJoin(empTerr).On(emp.EmployeeID == empTerr.EmpID); emp.InnerJoin(terr).On(empTerr.TerrID == terr.TerritoryID); emp.LeftJoin(terrEx).On(terr.TerritoryID == terrEx.TerritoryID); Assert.IsTrue(collection.Load(emp)); Assert.AreEqual(8, collection.Count); break; } }
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) .LeftJoin(empTerr) .On(emp.EmployeeID == empTerr.EmpID) .LeftJoin(terr) .On(empTerr.TerrID == terr.TerritoryID) .LeftJoin(terrEx) .On(terr.TerritoryID == terrEx.TerritoryID) .Where(emp.FirstName.Trim().Like("J___")); Assert.IsTrue(collection.Load(emp)); Assert.AreEqual(7, collection.Count); }
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) .InnerJoin(empTerr) .On(empTerr.TerrID == emp.EmployeeID) .InnerJoin(terr) .On(terr.TerritoryID == empTerr.TerrID) .InnerJoin(terrEx) .On(terrEx.TerritoryID == terr.TerritoryID) .Where(terrEx.Notes.IsNotNull()) .OrderBy(emp.FirstName.Ascending); emp.es.PageNumber = 1; emp.es.PageSize = 20; Assert.IsTrue(collection.Load(emp)); Assert.AreEqual(2, collection.Count); break; } }
/// <summary> /// Get Territory /// </summary> public void GetTerritory() { // Create the manager with the api key var route4Me = new Route4MeManager(ActualApiKey); CreateTerritoryZone(); string territoryId = TerritoryZonesToRemove[TerritoryZonesToRemove.Count - 1]; var territoryQuery = new TerritoryQuery { TerritoryId = territoryId, Addresses = 1 }; // Run the query TerritoryZone territory = route4Me.GetTerritory(territoryQuery, out string errorString); PrintExampleTerritory(territory, errorString); RemoveTestTerritoryZones(); }