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 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 RightWithFromSubQuery()
{
CustomerCollection coll = new CustomerCollection();
coll.es.Connection.Name = "ForeignKeyTest";
switch (coll.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SQLiteProvider":
Assert.Ignore("RIGHT JOIN not supported.");
break;
default:
CustomerQuery cq1 = new CustomerQuery("c1");
cq1.es.Top = 5;
cq1.Select(cq1.CustomerID, cq1.CustomerSub);
cq1.Where(cq1.Active == true);
cq1.OrderBy(cq1.CustomerID.Ascending);
CustomerQuery cq2 = new CustomerQuery("c2");
CustomerQuery cq3 = new CustomerQuery("c3");
cq3.Select(cq2);
cq3.From(cq1).As("cSub");
cq3.RightJoin(cq2).On(cq1.CustomerID == cq2.CustomerID &&
cq1.CustomerSub == cq2.CustomerSub);
cq3.Where(cq2.Active == true && cq1.CustomerID.IsNull());
cq3.OrderBy(cq2.CustomerID.Ascending);
Assert.IsTrue(coll.Load(cq3));
Assert.AreEqual(36, 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);
}
public void LeftWithOperatorsInOn()
{
int record = 0;
CustomerCollection collection = new CustomerCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
//case "EntitySpaces.NpgsqlProvider":
//case "EntitySpaces.Npgsql2Provider":
case "EntitySpaces.OracleClientProvider":
record = 2;
CustomerQuery cq = new CustomerQuery("cq");
EmployeeQuery eq = new EmployeeQuery("eq");
cq.Select(cq.CustomerName, eq.LastName);
cq.LeftJoin(eq).On(cq.StaffAssigned == eq.EmployeeID &
eq.Supervisor == 1);
cq.OrderBy(cq.CustomerName.Ascending);
Assert.IsTrue(collection.Load(cq));
Assert.AreEqual(56, collection.Count);
Assert.AreEqual("Doe", collection[record].GetColumn("LastName"));
break;
default:
record = 1;
cq = new CustomerQuery("cq");
eq = new EmployeeQuery("eq");
cq.Select(cq.CustomerName, eq.LastName);
cq.LeftJoin(eq).On(cq.StaffAssigned == eq.EmployeeID &
eq.Supervisor == 1);
cq.OrderBy(cq.CustomerName.Ascending);
Assert.IsTrue(collection.Load(cq));
Assert.AreEqual(56, collection.Count);
Assert.AreEqual("Doe", collection[record].GetColumn("LastName"));
break;
}
}