public void LoadJoined() { CustomerQuery cq = new CustomerQuery("c"); EmployeeQuery eq = new EmployeeQuery("e"); EmployeeQuery eq2 = new EmployeeQuery("e2"); OrderQuery oq = new OrderQuery("o"); OrderItemQuery oiq = new OrderItemQuery("oi"); ProductQuery pq = new ProductQuery("p"); cq.Select( cq.CustomerID, cq.CustomerSub, cq.CustomerName, eq, eq2.LastName.As("ReportsTo"), oq.PlacedBy, oq.OrderDate, oiq, pq.ProductName, pq.Discontinued); cq.LeftJoin(eq).On(eq.EmployeeID == cq.Manager); cq.LeftJoin(eq2).On(eq.Supervisor == eq2.EmployeeID); cq.LeftJoin(oq).On(cq.CustomerID == oq.CustID && cq.CustomerSub == oq.CustSub); cq.LeftJoin(oiq).On(oq.OrderID == oiq.OrderID); cq.LeftJoin(pq).On(oiq.ProductID == pq.ProductID); CustomerCollection coll = new CustomerCollection(); coll.es.Connection.Name = "ForeignKeyTest"; Assert.IsTrue(coll.Load(cq)); Assert.AreEqual(69, coll.Count); }
private bool LoadByPrimaryKeyDynamic(System.Int32 employeeID) { EmployeeQuery query = new EmployeeQuery(); query.Where(query.EmployeeID == employeeID); return(this.Load(query)); }
public bool GetActiveProductIds(int employeeId) { ProductQuery prd = new ProductQuery("pq"); //prd.es.Connection.Name = "ForeignKeyTest"; OrderItemQuery item = new OrderItemQuery("oiq"); //item.es.Connection.Name = "ForeignKeyTest"; OrderQuery ord = new OrderQuery("oq"); //ord.es.Connection.Name = "ForeignKeyTest"; CustomerQuery cust = new CustomerQuery("cq"); //cust.es.Connection.Name = "ForeignKeyTest"; EmployeeQuery emp = new EmployeeQuery("eq"); //emp.es.Connection.Name = "ForeignKeyTest"; prd.Select(prd.ProductID); prd.InnerJoin(item).On(prd.ProductID == item.ProductID); prd.InnerJoin(ord).On(item.OrderID == ord.OrderID); prd.InnerJoin(cust).On(ord.CustID == cust.CustomerID & ord.CustSub == cust.CustomerSub); prd.InnerJoin(emp).On(cust.Manager == emp.EmployeeID); prd.Where(emp.EmployeeID == employeeId); prd.Where(prd.Discontinued == false); prd.es.Distinct = true; return this.Load(prd); }
public bool GetActiveProductIds(int employeeId) { ProductQuery prd = new ProductQuery("pq"); //prd.es.Connection.Name = "ForeignKeyTest"; OrderItemQuery item = new OrderItemQuery("oiq"); //item.es.Connection.Name = "ForeignKeyTest"; OrderQuery ord = new OrderQuery("oq"); //ord.es.Connection.Name = "ForeignKeyTest"; CustomerQuery cust = new CustomerQuery("cq"); //cust.es.Connection.Name = "ForeignKeyTest"; EmployeeQuery emp = new EmployeeQuery("eq"); //emp.es.Connection.Name = "ForeignKeyTest"; prd.Select(prd.ProductID); prd.InnerJoin(item).On(prd.ProductID == item.ProductID); prd.InnerJoin(ord).On(item.OrderID == ord.OrderID); prd.InnerJoin(cust).On(ord.CustID == cust.CustomerID & ord.CustSub == cust.CustomerSub); prd.InnerJoin(emp).On(cust.Manager == emp.EmployeeID); prd.Where(emp.EmployeeID == employeeId); prd.Where(prd.Discontinued == false); prd.es.Distinct = true; return(this.Load(prd)); }
protected void InitQuery(EmployeeQuery query) { query.OnLoadDelegate = this.OnQueryLoaded; if (!query.es2.HasConnection) { query.es2.Connection = ((IEntityCollection)this).Connection; } }
public void SimpleIntersect() { EmployeeCollection collection = new EmployeeCollection(); collection.es.Connection.Name = "ForeignKeyTest"; EmployeeQuery eq1 = new EmployeeQuery("eq1"); EmployeeQuery eq2 = new EmployeeQuery("eq2"); // Only includes rows with both "n" and"a" eq1.Where(eq1.FirstName.Like("%n%")); eq1.Intersect(eq2); eq2.Where(eq2.FirstName.Like("%a%")); Assert.IsTrue(collection.Load(eq1)); Assert.AreEqual(2, collection.Count); }
public void SimpleExcept() { EmployeeCollection collection = new EmployeeCollection(); collection.es.Connection.Name = "ForeignKeyTest"; EmployeeQuery eq1 = new EmployeeQuery("eq1"); EmployeeQuery eq2 = new EmployeeQuery("eq2"); // Includes all "J"s except "Jim" eq1.Where(eq1.FirstName.Like("%J%")); eq1.Except(eq2); eq2.Where(eq2.FirstName == "Jim"); Assert.IsTrue(collection.Load(eq1)); Assert.AreEqual(3, collection.Count); }
public void ConcatTwoStringsAndTwoLiterals() { 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")); Assert.IsTrue(collection.Load(eq)); string theName = collection[0].GetColumn("FullName") as string; Assert.AreEqual(12, theName.Length); }
public void InnerSimple() { EmployeeCollection collection = new EmployeeCollection(); collection.es.Connection.Name = "ForeignKeyTest"; //collection.es.Connection.ConnectionString = // UnitTestBase.GetFktString(collection.es.Connection); EmployeeQuery eq = new EmployeeQuery("eq"); CustomerQuery cq = new CustomerQuery("cq"); eq.Select(eq.EmployeeID, eq.LastName, cq.CustomerName); eq.InnerJoin(cq).On(eq.EmployeeID == cq.StaffAssigned); Assert.IsTrue(collection.Load(eq)); Assert.AreEqual(10, collection.Count); }
public void SimpleUnion() { EmployeeCollection collection = new EmployeeCollection(); collection.es.Connection.Name = "ForeignKeyTest"; EmployeeQuery eq1 = new EmployeeQuery("eq1"); EmployeeQuery eq2 = new EmployeeQuery("eq2"); // This leaves out the record with Age 30 eq1.Where(eq1.Age < 30); eq1.Union(eq2); eq2.Where(eq2.Age > 30); Assert.IsTrue(collection.Load(eq1)); Assert.AreEqual(4, collection.Count); }
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 TestSerializeQuery() { EmployeeQuery query = new EmployeeQuery("e"); query.Where(query.EmployeeID.In(1, 2, 3, new List<object>() { 1, 2, 3 })); string qq = EmployeeQuery.SerializeHelper.ToXml(query); List<Type> types = new List<Type>(); types.Add(typeof(EmployeeQuery)); EmployeeQuery employeeQuery = EmployeeQuery.SerializeHelper.FromXml( qq, typeof(EmployeeQuery), types) as EmployeeQuery; EmployeeCollection c = new EmployeeCollection(); c.es.Connection.Name = "ForeignKeyTest"; c.Load(employeeQuery); Assert.IsTrue(c.Count == 3); }
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) .InnerJoin(empTerr).On(emp.EmployeeID == empTerr.EmpID) .InnerJoin(terr).On(terr.TerritoryID == empTerr.TerrID) .InnerJoin(terrEx).On(terrEx.TerritoryID == terr.TerritoryID) .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 WhereExists() { EmployeeCollection collection = new EmployeeCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); // SubQuery of Employees with a null Supervisor column. EmployeeQuery sq = new EmployeeQuery("s"); sq.es.Distinct = true; sq.Select(sq.EmployeeID); sq.Where(sq.Supervisor.IsNull()); // If even one employee has a null supervisor, // i.e., the above query has a result set, // then run a list of all employees. EmployeeQuery eq = new EmployeeQuery("e"); eq.Select(eq.EmployeeID, eq.Supervisor); eq.Where(eq.Exists(sq)); Assert.IsTrue(collection.Load(eq)); Assert.AreEqual(5, collection.Count); }
public void SerializeDeserializeJoin() { if (aggTest.es.Connection.Name == "SqlCe") { Assert.Ignore("Not tested for SqlCe."); } else { // Test serializing a DynamicQuery // Must use binary serialization // Xml serialization doesn't serialize all of your private properties // 1) Create our Query on the client EmployeeQuery emp = new EmployeeQuery("eq"); //emp.es.Connection.Name = "ForeignKeyTest"; EmployeeTerritoryQuery et = new EmployeeTerritoryQuery("etq"); //et.es.Connection.Name = "ForeignKeyTest"; emp.Select(emp.FirstName, emp.LastName, et.TerrID); emp.InnerJoin(et).On(emp.EmployeeID == et.EmpID); emp.Where(emp.LastName.Like("S%")); // 2) Serialize it in binary BinaryFormatter bf = new BinaryFormatter(); MemoryStream ms = new MemoryStream(); bf.Serialize(ms, emp); byte[] query = ms.ToArray(); // 3) Send it over the wire // 4) Deserialize it on the Server bf = new BinaryFormatter(); ms = new MemoryStream(query); EmployeeQuery newQuery = bf.Deserialize(ms) as EmployeeQuery; // Now load it EmployeeCollection collection = new EmployeeCollection(); collection.es.Connection.Name = "ForeignKeyTest"; collection.Load(newQuery); Assert.AreEqual(5, collection.Count); Assert.AreEqual("S", collection[0].LastName.Substring(0, 1)); } }
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 LeftWithExplicitParen() { 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.EmployeeID, eq.LastName, cq.CustomerName); eq.LeftJoin(cq).On(eq.EmployeeID == cq.StaffAssigned); eq.Where(eq.Age == 20); eq.Where(new esComparison(esParenthesis.Open)); eq.es.DefaultConjunction = esConjunction.Or; for (int i = 0; i < 4; i++) { eq.Where( eq.Supervisor == i & eq.EmployeeID == i + 1); } eq.Where(new esComparison(esParenthesis.Close)); Assert.IsTrue(collection.Load(eq)); Assert.AreEqual(5, collection.Count); }
public void AnyNestedBySubQuery() { OrderCollection collection = new OrderCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); switch (collection.es.Connection.ProviderSignature.DataProviderName) { case "EntitySpaces.SQLiteProvider": Assert.Ignore("Not supported by SQLite."); break; default: // Employees whose LastName begins with 'S'. EmployeeQuery eq = new EmployeeQuery("e"); eq.Select(eq.EmployeeID); eq.Where(eq.LastName.Like("S%")); // DateAdded for Customers whose Managers are in the // EmployeeQuery above. CustomerQuery cq = new CustomerQuery("c"); cq.es.Any = true; cq.Select(cq.DateAdded); cq.Where(cq.Manager.In(eq)); // OrderID and CustID where the OrderDate is // less than any one of the dates in the CustomerQuery above. OrderQuery oq = new OrderQuery("o"); oq.Select( oq.OrderID, oq.CustID ); oq.Where(oq.OrderDate < cq); Assert.IsTrue(collection.Load(oq)); Assert.AreEqual(8, collection.Count); break; } }
public void Nested() { OrderCollection collection = new OrderCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); OrderQuery oq = new OrderQuery("o"); CustomerQuery cq = new CustomerQuery("c"); EmployeeQuery eq = new EmployeeQuery("e"); // OrderID and CustID for customers who ordered on the same date // a customer was added, and have a manager whose // last name starts with 'S'. oq.Select( oq.OrderID, oq.CustID ); oq.Where(oq.OrderDate .In( cq.Select(cq.DateAdded) .Where(cq.Manager.In( eq.Select(eq.EmployeeID) .Where(eq.LastName.Like("S%")) ) ) ) ); Assert.IsTrue(collection.Load(oq)); Assert.AreEqual(2, collection.Count); }
public void WhereInANDedTogetherOperator() { EmployeeCollection collection = new EmployeeCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); EmployeeQuery eq1 = new EmployeeQuery("e1"); EmployeeQuery eq2 = new EmployeeQuery("e2"); EmployeeQuery eq3 = new EmployeeQuery("e3"); eq2.Select(eq2.EmployeeID); eq3.Select(eq3.EmployeeID); eq1.Where(eq1.EmployeeID.In(eq2) & eq1.EmployeeID.In(eq3)); Assert.IsTrue(collection.Load(eq1)); Assert.AreEqual(5, collection.Count); string lq = collection.Query.es.LastQuery; string[] one = lq.Split('1'); Assert.AreEqual(4, one.GetLength(0)); string[] two = lq.Split('2'); Assert.AreEqual(3, two.GetLength(0)); string[] three = lq.Split('3'); Assert.AreEqual(3, three.GetLength(0)); }
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 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 TwoAddStringsThenConcatenated() { EmployeeCollection collection = new EmployeeCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); EmployeeQuery eq = new EmployeeQuery("eq"); eq.Select ( eq.EmployeeID, ( (eq.LastName.ToLower() + eq.FirstName.ToLower()).Trim() + " : " + (eq.LastName.ToUpper() + eq.FirstName.ToUpper()).Trim() ).Trim().As("SomeColumn") ); Assert.IsTrue(collection.Load(eq)); string theName = collection[0].GetColumn("SomeColumn") as string; Assert.AreEqual("smithjohn : SMITHJOHN", theName); }
public void WhereExistsFalse() { EmployeeCollection collection = new EmployeeCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); // EmployeeID is required and will never be NULL EmployeeQuery sq = new EmployeeQuery("s"); sq.es.Distinct = true; sq.Select(sq.EmployeeID); sq.Where(sq.EmployeeID.IsNull()); // This should produce no results as the // inner query does not exist. EmployeeQuery eq = new EmployeeQuery("e"); eq.Select(eq.EmployeeID, eq.Supervisor); eq.Where(eq.Exists(sq)); Assert.IsFalse(collection.Load(eq)); }
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 WhereExistsANDed() { EmployeeCollection collection = new EmployeeCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); EmployeeQuery eq1 = new EmployeeQuery("e1"); EmployeeQuery eq2 = new EmployeeQuery("e2"); eq1.Where(eq1.EmployeeID > 2, eq1.Exists(eq2)); Assert.IsTrue(collection.Load(eq1)); Assert.AreEqual(3, collection.Count); string lq = collection.Query.es.LastQuery; string[] one = lq.Split('1'); Assert.AreEqual(4, one.GetLength(0)); string[] two = lq.Split('2'); Assert.AreEqual(2, two.GetLength(0)); }
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 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 bool Load(EmployeeQuery query) { this.query = query; InitQuery(this.query); return(Query.Load()); }
public void NestedBySubQuery() { OrderCollection collection = new OrderCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); // This is the same as the traditional nested SubQuery // in the 'Nested' test, but is easier to construct // and understand. // The key is to start with the innermost SubQuery, // and work your way out to the outermost Query. // Employees whose LastName begins with 'S'. EmployeeQuery eq = new EmployeeQuery("e"); eq.Select(eq.EmployeeID); eq.Where(eq.LastName.Like("S%")); // DateAdded for Customers whose Managers are in the // EmployeeQuery above. CustomerQuery cq = new CustomerQuery("c"); cq.Select(cq.DateAdded); cq.Where(cq.Manager.In(eq)); // OrderID and CustID where the OrderDate is in the // CustomerQuery above. OrderQuery oq = new OrderQuery("o"); oq.Select( oq.OrderID, oq.CustID ); oq.Where(oq.OrderDate.In(cq)); Assert.IsTrue(collection.Load(oq)); Assert.AreEqual(2, collection.Count); }
public void MixedANDAndORInOn() { ProductCollection collection = new ProductCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); int empId = 1; ProductQuery prd = new ProductQuery("pq"); OrderItemQuery item = new OrderItemQuery("oiq"); OrderQuery ord = new OrderQuery("oq"); CustomerQuery cust = new CustomerQuery("cq"); EmployeeQuery emp = new EmployeeQuery("eq"); prd.Select(prd.ProductID); prd.InnerJoin(item).On(prd.ProductID == item.ProductID); prd.InnerJoin(ord).On(item.OrderID == ord.OrderID); prd.InnerJoin(cust).On(ord.CustID == cust.CustomerID & (ord.CustSub == cust.CustomerSub | ord.EmployeeID == cust.StaffAssigned)); prd.InnerJoin(emp).On(cust.Manager == emp.EmployeeID); prd.Where(emp.EmployeeID == empId); prd.Where(prd.Discontinued == false); prd.OrderBy(prd.ProductID.Ascending); Assert.IsTrue(collection.Load(prd)); Assert.AreEqual(9, collection.Count); }
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 SingleUnionWithJoin() { CustomerCollection coll = new CustomerCollection(); coll.es.Connection.Name = "ForeignKeyTest"; CustomerQuery cq1 = new CustomerQuery("c1"); EmployeeQuery eq1 = new EmployeeQuery("e1"); cq1.Select(cq1.CustomerID, cq1.CustomerSub, cq1.CustomerName, eq1.LastName); cq1.InnerJoin(eq1).On(cq1.Manager == eq1.EmployeeID); cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31"))); CustomerQuery cq2 = new CustomerQuery("c2"); EmployeeQuery eq2 = new EmployeeQuery("e2"); cq2.Select(cq2.CustomerID, cq2.CustomerSub, cq2.CustomerName, eq2.LastName); cq2.InnerJoin(eq2).On(cq2.Manager == eq2.EmployeeID); cq2.Where(cq2.DateAdded.Between(Convert.ToDateTime("2006-01-01"), Convert.ToDateTime("2006-12-31"))); cq1.Union(cq2); cq1.OrderBy(cq1.CustomerID.Ascending, cq1.CustomerSub.Ascending); //string lq = cq1.Parse(); Assert.IsTrue(coll.Load(cq1)); Assert.AreEqual(49, coll.Count); Assert.AreEqual("Smith", coll[0].GetColumn("LastName")); }