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