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