public void InnerAlias() { OrderCollection collection = new OrderCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); switch (collection.es.Connection.ProviderSignature.DataProviderName) { case "EntitySpaces.MSAccessProvider": Assert.Ignore("Not supported"); break; case "EntitySpaces.VistaDBProvider": case "EntitySpaces.VistaDB4Provider": OrderQuery oq = new OrderQuery("o"); OrderItemQuery odq = new OrderItemQuery("od"); oq.Select(oq.OrderID, odq.OrderID); oq.InnerJoin(odq).On(oq.OrderID == odq.OrderID); oq.OrderBy(oq.OrderID.Ascending); Assert.IsTrue(collection.Load(oq), "Load"); string lq = collection.Query.es.LastQuery; Assert.AreEqual(1, collection[0].OrderID.Value, "Property"); Assert.AreEqual(1, Convert.ToInt32(collection[0].GetColumn("OrderID_1")), "Virtual"); break; default: oq = new OrderQuery("o"); odq = new OrderItemQuery("od"); oq.Select(oq.OrderID, odq.OrderID); oq.InnerJoin(odq).On(oq.OrderID == odq.OrderID); oq.OrderBy(oq.OrderID.Ascending); Assert.IsTrue(collection.Load(oq), "Load"); lq = collection.Query.es.LastQuery; Assert.AreEqual(1, collection[0].OrderID.Value, "Property"); Assert.AreEqual(1, Convert.ToInt32(collection[0].GetColumn("OrderID1")), "Virtual"); break; } }
public void SimpleJoinOn() { OrderCollection collection = new OrderCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); switch (collection.es.Connection.ProviderSignature.DataProviderName) { case "EntitySpaces.MSAccessProvider": Assert.Ignore("SubQuery inside an ON clause not Supported"); break; default: // Query for the Join OrderItemQuery oiq = new OrderItemQuery("oi"); // SubQuery of OrderItems with a discount OrderItemQuery oisq = new OrderItemQuery("ois"); oisq.es.Distinct = true; oisq.Select(oisq.Discount); oisq.Where(oisq.Discount > 0); // Orders with discounted items OrderQuery oq = new OrderQuery("o"); oq.Select(oq.OrderID, oiq.Discount); oq.InnerJoin(oiq).On(oq.OrderID == oiq.OrderID & oiq.Discount.In(oisq)); Assert.IsTrue(collection.Load(oq)); Assert.AreEqual(2, collection.Count); 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 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 InnerSelectAllSecondaryQuery() { OrderCollection collection = new OrderCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); OrderQuery oq = new OrderQuery("o"); OrderItemQuery oiq = new OrderItemQuery("oi"); oq.Select(oiq); oq.InnerJoin(oiq).On(oq.OrderID == oiq.OrderID); Assert.IsTrue(collection.Load(oq)); Assert.AreEqual(15, collection.Count); string lq = collection.Query.es.LastQuery; string all = lq.Substring(7, 4); Assert.AreEqual("oi.*", all); }