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"));
}
public void SubQueryWithGT_LT()
{
CustomerCollection coll = new CustomerCollection();
coll.es.Connection.ConnectionString =
UnitTestBase.GetFktString(coll.es.Connection);
switch (coll.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SqlServerCeProvider":
case "EntitySpaces.SqlServerCe4Provider":
Assert.Ignore("Not supported.");
break;
default:
DateTime fromDate = new DateTime(2005, 1, 1);
DateTime toDate = new DateTime(2005, 8, 31);
DateTime startDate = new DateTime(2000, 1, 1);
DateTime endDate = new DateTime(2000, 12, 31);
CustomerQuery cq = new CustomerQuery("c");
OrderQuery oq = new OrderQuery("o");
OrderQuery oqSub = new OrderQuery("oSub");
oqSub.Select(oqSub.OrderDate.Max());
oqSub.Where(oqSub.CustID == cq.CustomerID &
oqSub.CustSub == cq.CustomerSub);
// These work in SubQuery
oqSub.Where(oqSub.OrderDate >= fromDate);
oqSub.Where(oqSub.OrderDate <= toDate);
// If you comment the above 2 GT/LT lines
// and un-comment the Between line below
// it gets Null Reference exception on Load()
//oqSub.Where(oqSub.OrderDate.Between(fromDate, toDate));
cq.es.Distinct = true;
cq.Select(cq.CustomerID, cq.CustomerSub, cq.DateAdded, oqSub.As("MaxOrderDate"));
cq.InnerJoin(oq).On(cq.CustomerID == oq.CustID &
cq.CustomerSub == oq.CustSub);
// This works in outer query
cq.Where(cq.DateAdded.Between(startDate, endDate));
Assert.IsTrue(coll.Load(cq));
Assert.AreEqual(1, coll.Count);
break;
}
}
public void JoinWithArithmeticExpressionOrderByCalulatedColumn()
{
CustomerCollection collection = new CustomerCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
string orderAlias = "";
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.MSAccessProvider":
orderAlias = "<'TotalSales'>";
break;
default:
orderAlias = "TotalSales";
break;
}
// Notice I create a calulated columns based on the TotalSales,
// then Order by it descending
CustomerQuery cust = new CustomerQuery("c");
OrderQuery order = new OrderQuery("o");
OrderItemQuery item = new OrderItemQuery("oi");
cust.Select(cust.CustomerName,
(item.Quantity * item.UnitPrice).Sum().As("TotalSales"));
cust.InnerJoin(order).On(order.CustID == cust.CustomerID);
cust.InnerJoin(item).On(item.OrderID == order.OrderID);
cust.GroupBy(cust.CustomerName);
cust.OrderBy(orderAlias, esOrderByDirection.Descending);
Assert.IsTrue(collection.Load(cust));
Assert.AreEqual(6, collection.Count);
}