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