public void Correlated() { OrderItemCollection collection = new OrderItemCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); OrderItemQuery oiq = new OrderItemQuery("oi"); ProductQuery pq = new ProductQuery("p"); // oiq.ProductID in the inner Select is pulled from // the outer Select, making a correlated SubQuery. oiq.Select( oiq.OrderID, (oiq.Quantity * oiq.UnitPrice).Sum().As("Total") ); oiq.Where(oiq.ProductID .In( pq.Select(pq.ProductID) .Where(oiq.ProductID == pq.ProductID) ) ); oiq.GroupBy(oiq.OrderID); Assert.IsTrue(collection.Load(oiq)); Assert.AreEqual(5, collection.Count); }
public void HavingWithComplexExpression() { OrderItemCollection coll = new OrderItemCollection(); coll.es.Connection.Name = "ForeignKeyTest"; OrderItemQuery q = new OrderItemQuery(); q.Select(q.OrderID, (q.Quantity * q.UnitPrice).Sum().As("TotalPrice")); q.Where(q.Discount.IsNull()); q.GroupBy(q.OrderID); q.Having((q.Quantity * q.UnitPrice).Sum() > 500); switch (coll.es.Connection.ProviderSignature.DataProviderName) { case "EntitySpaces.SqlServerCeProvider": case "EntitySpaces.SqlServerCe4Provider": case "EntitySpaces.VistaDBProvider": case "EntitySpaces.VistaDB4Provider": q.OrderBy("<TotalPrice>", esOrderByDirection.Descending); break; default: q.OrderBy((q.Quantity * q.UnitPrice).Sum().Descending); break; } Assert.IsTrue(coll.Load(q), "Load"); Assert.AreEqual(2, coll.Count, "Count"); decimal price = Convert.ToDecimal(coll[0].GetColumn("TotalPrice")); Assert.AreEqual(1940.0M, price, "GetColumn"); }
public void HavingWithSimpleExpression() { OrderItemCollection coll = new OrderItemCollection(); coll.es.Connection.Name = "ForeignKeyTest"; OrderItemQuery q = new OrderItemQuery(); //q.es2.Connection.Name = "ForeignKeyTest"; q.Select(q.OrderID, q.Quantity.Sum().As("TotalQty")); q.Where(q.Discount.IsNull()); q.GroupBy(q.OrderID); switch (coll.es.Connection.ProviderSignature.DataProviderName) { case "EntitySpaces.SqlServerCeProvider": case "EntitySpaces.SqlServerCe4Provider": case "EntitySpaces.VistaDBProvider": case "EntitySpaces.VistaDB4Provider": q.Having(q.Quantity.Sum() > 100); q.OrderBy("<TotalQty>", esOrderByDirection.Descending); break; case "EntitySpaces.SQLiteProvider": q.Having((q.Quantity * 1).Sum() > 100); q.OrderBy(q.Quantity.Sum().Descending); break; default: q.Having(q.Quantity.Sum() > 100); q.OrderBy(q.Quantity.Sum().Descending); break; } Assert.IsTrue(coll.Load(q), "Load"); Assert.AreEqual(3, coll.Count, "Count"); int qty = Convert.ToInt32(coll[0].GetColumn("TotalQty")); Assert.AreEqual(240, qty, "GetColumn"); }
public void OneAddAggregate() { OrderItemCollection collection = new OrderItemCollection(); collection.es.Connection.Name = "ForeignKeyTest"; OrderItemQuery oiq = new OrderItemQuery("oiq"); oiq.Select(oiq.ProductID, (oiq.UnitPrice + oiq.Discount).Sum().As("SomeDecimal")); oiq.GroupBy(oiq.ProductID); oiq.OrderBy(oiq.ProductID, esOrderByDirection.Ascending); Assert.IsTrue(collection.Load(oiq)); decimal someDec = Convert.ToDecimal(collection[0].GetColumn("SomeDecimal")); Assert.AreEqual(Convert.ToDecimal("0.12"), Math.Round(someDec, 2)); }