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; } }