public void SingleUnionWithOrderBy() { CustomerCollection coll = new CustomerCollection(); coll.es.Connection.Name = "ForeignKeyTest"; CustomerQuery cq1 = new CustomerQuery("c1"); cq1.Select(cq1.CustomerID, cq1.CustomerSub, cq1.CustomerName); cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31"))); CustomerQuery cq2 = new CustomerQuery("c2"); cq2.Select(cq2.CustomerID, cq2.CustomerSub, cq2.CustomerName); cq2.Where(cq2.DateAdded.Between(Convert.ToDateTime("2006-01-01"), Convert.ToDateTime("2006-12-31"))); // Combine 2005 and 2006 in one result set cq1.Union(cq2); cq1.OrderBy(cq1.CustomerName.Ascending); //string lq = cq1.Parse(); Assert.IsTrue(coll.Load(cq1)); Assert.AreEqual(49, coll.Count); }
public void MultipleUnion() { CustomerCollection coll = new CustomerCollection(); coll.es.Connection.Name = "ForeignKeyTest"; CustomerQuery cq1 = new CustomerQuery("c1"); cq1.SelectAllExcept(cq1.Notes); cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31"))); CustomerQuery cq2 = new CustomerQuery("c2"); cq2.SelectAllExcept(cq2.Notes); cq2.Where(cq2.DateAdded.Between(Convert.ToDateTime("2006-01-01"), Convert.ToDateTime("2006-12-31"))); CustomerQuery cq3 = new CustomerQuery("c3"); cq3.SelectAllExcept(cq3.Notes); cq3.Where(cq3.DateAdded.Between(Convert.ToDateTime("2000-01-01"), Convert.ToDateTime("2000-12-31"))); // Combine 2000, 2005, and 2006 cq1.Union(cq2); cq1.Union(cq3); //string lq = cq1.Parse(); Assert.IsTrue(coll.Load(cq1)); Assert.AreEqual(51, coll.Count); }
public void SingleUnionWithOverlap() { CustomerCollection coll = new CustomerCollection(); coll.es.Connection.Name = "ForeignKeyTest"; CustomerQuery cq1 = new CustomerQuery("c1"); cq1.SelectAllExcept(cq1.Notes); cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31"))); CustomerQuery cq2 = new CustomerQuery("c2"); cq2.SelectAllExcept(cq2.Notes); cq2.Where(cq2.DateAdded.Between(Convert.ToDateTime("2005-12-31"), Convert.ToDateTime("2006-12-31"))); // Combine 2005 and 2006 in one result set with the 2 duplicate rows eliminated cq1.Union(cq2); Assert.IsTrue(coll.Load(cq1)); Assert.AreEqual(49, coll.Count); }
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")); }