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