public void FromClause() { OrderCollection collection = new OrderCollection(); collection.es.Connection.Name = "ForeignKeyTest"; switch (collection.es.Connection.ProviderSignature.DataProviderName) { //case "EntitySpaces.SqlServerCeProvider": // Assert.Ignore("Not supported."); // break; default: OrderQuery oq = new OrderQuery("o"); OrderItemQuery oiq = new OrderItemQuery("oi"); // The inner Select contains an aggregate, // which requires a GroupBy for each non-aggregate in the Select. // The outer Select includes the aggregate from the inner Select, // plus columns where no GroupBy was desired. oq.Select(oq.CustID, oq.OrderDate, oiq.UnitPrice); oq.From ( oiq.Select(oiq.OrderID, oiq.UnitPrice.Sum()).GroupBy(oiq.OrderID) ).As("sub"); oq.InnerJoin(oq).On(oq.OrderID == oiq.OrderID); oq.OrderBy(oq.OrderID.Ascending); Assert.IsTrue(collection.Load(oq)); Assert.AreEqual(5, collection.Count); Decimal up = Convert.ToDecimal(collection[0].GetColumn("UnitPrice")); Assert.AreEqual(5.11m, Math.Round(up, 2)); break; } }
public void FromClauseWithAlias() { OrderCollection collection = new OrderCollection(); collection.es.Connection.Name = "ForeignKeyTest"; OrderQuery oq = new OrderQuery("o"); OrderItemQuery oiq = new OrderItemQuery("oi"); // Calculate total price per order switch (collection.es.Connection.ProviderSignature.DataProviderName) { //case "EntitySpaces.SqlServerCeProvider": // Assert.Ignore("Not supported."); // break; case "EntitySpaces.NpgsqlProvider": case "EntitySpaces.Npgsql2Provider": case "EntitySpaces.OracleClientProvider": oq.Select(oq.CustID, oq.OrderDate, "<sub.\"OrderTotal\">"); break; default: oq.Select(oq.CustID, oq.OrderDate, "<sub.OrderTotal>"); break; } oq.From ( oiq.Select(oiq.OrderID, (oiq.UnitPrice * oiq.Quantity).Sum().As("OrderTotal")) .GroupBy(oiq.OrderID) ).As("sub"); oq.InnerJoin(oq).On(oq.OrderID == oiq.OrderID); oq.OrderBy(oq.OrderID.Ascending); Assert.IsTrue(collection.Load(oq)); Assert.AreEqual(5, collection.Count); Assert.AreEqual(13.11m, collection[0].GetColumn("OrderTotal")); }
public void FromClauseUsingInstance() { OrderCollection collection = new OrderCollection(); collection.es.Connection.Name = "ForeignKeyTest"; // Select an arbitrary number of rows from // any starting row. int startRow = 4; int numberOfRows = 7; // OrderItem SubQuery 1 // Get all rows through start + number, ascending OrderItemQuery oisq = new OrderItemQuery("ois"); oisq.es.Top = startRow + numberOfRows - 1; oisq.Select(oisq.OrderID, oisq.ProductID, oisq.Quantity); oisq.OrderBy(oisq.OrderID.Ascending, oisq.ProductID.Ascending); // OrderItem SubQuery 2 // Get just the number of rows, descending OrderItemQuery oisq2 = new OrderItemQuery("ois2"); oisq2.es.Top = numberOfRows; oisq2.From(oisq).As("sub1"); switch (collection.es.Connection.ProviderSignature.DataProviderName) { //case "EntitySpaces.SqlServerCeProvider": // Assert.Ignore("Not supported."); // break; case "EntitySpaces.NpgsqlProvider": case "EntitySpaces.Npgsql2Provider": oisq2.OrderBy("<sub1.\"OrderID\">", esOrderByDirection.Descending); oisq2.OrderBy("<sub1.\"ProductID\">", esOrderByDirection.Descending); break; case "EntitySpaces.OracleClientProvider": Assert.Ignore("Not supported."); break; default: oisq2.OrderBy("<sub1.OrderID>", esOrderByDirection.Descending); oisq2.OrderBy("<sub1.ProductID>", esOrderByDirection.Descending); break; } // Put it back in ascending order OrderQuery oq = new OrderQuery("o"); oq.From(oisq2).As("sub2"); switch (collection.es.Connection.ProviderSignature.DataProviderName) { case "EntitySpaces.NpgsqlProvider": case "EntitySpaces.Npgsql2Provider": oq.OrderBy("<sub2.\"OrderID\">", esOrderByDirection.Ascending); oq.OrderBy("<sub2.\"ProductID\">", esOrderByDirection.Ascending); break; default: oq.OrderBy("<sub2.OrderID>", esOrderByDirection.Ascending); oq.OrderBy("<sub2.ProductID>", esOrderByDirection.Ascending); break; } Assert.IsTrue(collection.Load(oq)); Assert.AreEqual(7, collection.Count); Assert.AreEqual(10, collection[0].GetColumn("Quantity")); }