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 SingleZeroToMany()
		{
            // The main Employee query
            EmployeeQuery eq1 = new EmployeeQuery("e");
            eq1.Where(eq1.EmployeeID < 3);
            eq1.OrderBy(eq1.EmployeeID.Ascending);

            // The Order Collection
            OrderQuery oq1 = eq1.Prefetch<OrderQuery>(Employee.Prefetch_OrderCollectionByEmployeeID);
            EmployeeQuery eq2 = oq1.GetQuery<EmployeeQuery>();
            oq1.Where(eq2.EmployeeID < 3);

            // Pre-test the Order query
            OrderCollection oColl = new OrderCollection();
            oColl.es.Connection.Name = "ForeignKeyTest";
            oColl.Load(oq1);
            Assert.AreEqual(3, oColl.Count, "Order pre-test");

            // This will Prefetch the Order query
            EmployeeCollection coll = new EmployeeCollection();
            coll.es.Connection.Name = "ForeignKeyTest";
            //coll.es.IsLazyLoadDisabled = true;
            coll.Load(eq1);

            foreach (Employee emp in coll)
            {
                emp.es.IsLazyLoadDisabled = true;

                switch (emp.EmployeeID.Value)
                {
                    case 1:
                        Assert.AreEqual(1, emp.EmployeeID.Value);
                        Assert.AreEqual(1, emp.OrderCollectionByEmployeeID.Count);
                        break;

                    case 2:
                        Assert.AreEqual(2, emp.EmployeeID.Value);
                        Assert.AreEqual(2, emp.OrderCollectionByEmployeeID.Count);
                        break;

                    default:
                        Assert.Fail("Only employees 1 and 2 should be loaded.");
                        break;
                }
            }
		}
        public void InnerSelectAllPrimaryQuery()
        {
            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(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, 3);
            Assert.AreEqual("o.*", all);
        }
        public void SelectAllPlusSubQuery()
        {
            OrderCollection collection = new OrderCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            OrderQuery orders = new OrderQuery("o");
            OrderItemQuery details = new OrderItemQuery("oi");

            switch (collection.es.Connection.ProviderSignature.DataProviderName)
            {
                case "EntitySpaces.SqlServerCeProvider":
                case "EntitySpaces.SqlServerCe4Provider":
                    Assert.Ignore("Not supported.");
                    break;
                default:
                    orders.Select
                    (
                        orders,
                        details.Select(
                            details.UnitPrice.Max())
                            .Where(orders.OrderID == details.OrderID).As("MaxUnitPrice")
                    );
                    orders.OrderBy(orders.OrderID.Ascending);

                    break;
            }

            Assert.IsTrue(collection.Load(orders));
            Assert.AreEqual(8, collection.Count);
            Assert.AreEqual(3m, collection[0].GetColumn("MaxUnitPrice"));

            string all = collection.Query.es.LastQuery.Substring(7, 3);
            Assert.AreEqual("o.*", all);
        }
        public void AllSubQuery()
        {
            OrderCollection collection = new OrderCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            switch (collection.es.Connection.ProviderSignature.DataProviderName)
            {
                case "EntitySpaces.SQLiteProvider":
                    Assert.Ignore("Not supported by SQLite.");
                    break;

                default:
                    // DateAdded for Customers whose Manager  = 3
                    CustomerQuery cq = new CustomerQuery("c");
                    cq.es.All = true;
                    cq.Select(cq.DateAdded);
                    cq.Where(cq.Manager == 3);

                    // OrderID and CustID where the OrderDate is 
                    // less than all of the dates in the CustomerQuery above.
                    OrderQuery oq = new OrderQuery("o");
                    oq.Select(
                        oq.OrderID,
                        oq.CustID
                    );
                    oq.Where(oq.OrderDate < cq);

                    Assert.IsTrue(collection.Load(oq));
                    Assert.AreEqual(8, collection.Count);
                    break;
            }
        }
        public void AnyNestedBySubQuery()
        {
            OrderCollection collection = new OrderCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            switch (collection.es.Connection.ProviderSignature.DataProviderName)
            {
                case "EntitySpaces.SQLiteProvider":
                    Assert.Ignore("Not supported by SQLite.");
                    break;

                default:
                    // Employees whose LastName begins with 'S'.
                    EmployeeQuery eq = new EmployeeQuery("e");
                    eq.Select(eq.EmployeeID);
                    eq.Where(eq.LastName.Like("S%"));

                    // DateAdded for Customers whose Managers are in the
                    // EmployeeQuery above.
                    CustomerQuery cq = new CustomerQuery("c");
                    cq.es.Any = true;
                    cq.Select(cq.DateAdded);
                    cq.Where(cq.Manager.In(eq));

                    // OrderID and CustID where the OrderDate is 
                    // less than any one of the dates in the CustomerQuery above.
                    OrderQuery oq = new OrderQuery("o");
                    oq.Select(
                        oq.OrderID,
                        oq.CustID
                    );
                    oq.Where(oq.OrderDate < cq);

                    Assert.IsTrue(collection.Load(oq));
                    Assert.AreEqual(8, collection.Count);
                    break;
            }
        }
        public void NestedBySubQuery()
        {
            OrderCollection collection = new OrderCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            // This is the same as the traditional nested SubQuery 
            // in the 'Nested' test, but is easier to construct
            // and understand.
            // The key is to start with the innermost SubQuery,
            // and work your way out to the outermost Query.

            // Employees whose LastName begins with 'S'.
            EmployeeQuery eq = new EmployeeQuery("e");
            eq.Select(eq.EmployeeID);
            eq.Where(eq.LastName.Like("S%"));

            // DateAdded for Customers whose Managers are in the
            // EmployeeQuery above.
            CustomerQuery cq = new CustomerQuery("c");
            cq.Select(cq.DateAdded);
            cq.Where(cq.Manager.In(eq));

            // OrderID and CustID where the OrderDate is in the
            // CustomerQuery above.
            OrderQuery oq = new OrderQuery("o");
            oq.Select(
                oq.OrderID,
                oq.CustID
            );
            oq.Where(oq.OrderDate.In(cq));

            Assert.IsTrue(collection.Load(oq));
            Assert.AreEqual(2, collection.Count);
        }
        public void Nested()
        {
            OrderCollection collection = new OrderCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            OrderQuery oq = new OrderQuery("o");
            CustomerQuery cq = new CustomerQuery("c");
            EmployeeQuery eq = new EmployeeQuery("e");

            // OrderID and CustID for customers who ordered on the same date
            // a customer was added, and have a manager whose 
            // last name starts with 'S'.
            oq.Select(
                oq.OrderID,
                oq.CustID
            );
            oq.Where(oq.OrderDate
                .In(
                    cq.Select(cq.DateAdded)
                    .Where(cq.Manager.In(
                        eq.Select(eq.EmployeeID)
                        .Where(eq.LastName.Like("S%"))
                        )
                    )
                )
            );

            Assert.IsTrue(collection.Load(oq));
            Assert.AreEqual(2, collection.Count);
        }
        public void SelectStatement()
        {
            OrderCollection collection = new OrderCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            OrderQuery orders = new OrderQuery("o");
            OrderItemQuery details = new OrderItemQuery("oi");

            // A SubQuery in the Select clause must return a single value.
            switch (collection.es.Connection.ProviderSignature.DataProviderName)
            {
                case "EntitySpaces.SqlServerCeProvider":
                case "EntitySpaces.SqlServerCe4Provider":
                    Assert.Ignore("Not supported.");
                    break;
                default:
                    orders.Select
                    (
                        orders.OrderID,
                        orders.OrderDate,
                        details.Select(
                            details.UnitPrice.Max())
                            .Where(orders.OrderID == details.OrderID).As("MaxUnitPrice")
                    );
                    orders.OrderBy(orders.OrderID.Ascending);
                    break;
            }

            Assert.IsTrue(collection.Load(orders));
            Assert.AreEqual(8, collection.Count);
            Assert.AreEqual(3m, collection[0].GetColumn("MaxUnitPrice"));
        }
        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 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"));
        }
        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 SingleUnionWithSubSelect()
        {
            OrderCollection coll = new OrderCollection();
            coll.es.Connection.Name = "ForeignKeyTest";

            switch (coll.es.Connection.ProviderSignature.DataProviderName)
            {
                case "EntitySpaces.SqlServerCeProvider":
                case "EntitySpaces.SqlServerCe4Provider":
                    Assert.Ignore("Scalar SubSelects are not supported in SqlCe.");
                    break;

                default:
                    OrderQuery oq1 = new OrderQuery("o1");
                    OrderItemQuery oiq1 = new OrderItemQuery("oi1");

                    oq1.Select
                    (
                        oq1.OrderID,
                        oq1.OrderDate,
                        oiq1.Select(
                            oiq1.UnitPrice.Max())
                            .Where(oq1.OrderID == oiq1.OrderID).As("MaxUnitPrice")
                    );
                    oq1.Where(oq1.OrderDate.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31")));

                    OrderQuery oq2 = new OrderQuery("o2");
                    OrderItemQuery oiq2 = new OrderItemQuery("oi2");

                    oq2.Select
                    (
                        oq2.OrderID,
                        oq2.OrderDate,
                        oiq2.Select(
                            oiq2.UnitPrice.Max())
                            .Where(oq2.OrderID == oiq2.OrderID).As("MaxUnitPrice")
                    );
                    oq2.Where(oq2.OrderDate.Between(Convert.ToDateTime("2004-01-01"), Convert.ToDateTime("2004-12-31")));

                    oq1.Union(oq2);
                    oq1.OrderBy(oq1.OrderID.Ascending);

                    //string lq = cq1.Parse();

                    Assert.IsTrue(coll.Load(oq1));
                    Assert.AreEqual(6, coll.Count);
                    Assert.AreEqual(3m, coll[0].GetColumn("MaxUnitPrice"));
                    break;
            }
        }
        public void NestedZeroToMany()
        {
            // The main Employee query
            EmployeeQuery eq1 = new EmployeeQuery("e");
            eq1.Where(eq1.EmployeeID < 4);
            eq1.OrderBy(eq1.EmployeeID.Ascending);

            // The Order Collection
            OrderQuery oq1 = eq1.Prefetch<OrderQuery>(Employee.Prefetch_OrderCollectionByEmployeeID);
            EmployeeQuery eq2 = oq1.GetQuery<EmployeeQuery>();
            oq1.Where(eq2.EmployeeID < 4 && oq1.PlacedBy < 3);

            // Pre-test the Order query
            OrderCollection oColl = new OrderCollection();
            oColl.es.Connection.Name = "ForeignKeyTest";
            oColl.Load(oq1);
            Assert.AreEqual(5, oColl.Count, "Order pre-test");
            
            // The OrderItem Collection
            OrderItemQuery oiq1 = eq1.Prefetch<OrderItemQuery>(Employee.Prefetch_OrderCollectionByEmployeeID, Order.Prefetch_OrderItemCollectionByOrderID);
            EmployeeQuery eq3 = oiq1.GetQuery<EmployeeQuery>();
            OrderQuery oq2 = oiq1.GetQuery<OrderQuery>();
            oiq1.Where(eq3.EmployeeID < 4 && oq2.PlacedBy < 3 && oiq1.Quantity < 100);

            // Pre-test the OrderItem query
            OrderItemCollection oiColl = new OrderItemCollection();
            oiColl.es.Connection.Name = "ForeignKeyTest";
            oiColl.Load(oiq1);
            Assert.AreEqual(4, oiColl.Count, "OrderItem pre-test");

            // Will Prefetch the Order and OrderItems queries
            EmployeeCollection coll = new EmployeeCollection();
            coll.es.Connection.Name = "ForeignKeyTest";
            //coll.es.IsLazyLoadDisabled = true;
            coll.Load(eq1);

            foreach (Employee emp in coll)
            {
                emp.es.IsLazyLoadDisabled = true;

                switch (emp.EmployeeID.Value)
                {
                    case 1:
                        Assert.AreEqual(1, emp.EmployeeID.Value);
                        Assert.AreEqual(0, emp.OrderCollectionByEmployeeID.Count);
                        break;

                    case 2:
                        Assert.AreEqual(2, emp.EmployeeID.Value);
                        Assert.AreEqual(2, emp.OrderCollectionByEmployeeID.Count);

                        foreach (Order o in emp.OrderCollectionByEmployeeID)
                        {
                            Assert.Less(0, o.OrderItemCollectionByOrderID.Count);
                        }
                        break;

                    case 3:
                        Assert.AreEqual(3, emp.EmployeeID.Value);
                        Assert.AreEqual(3, emp.OrderCollectionByEmployeeID.Count);

                        foreach (Order o in emp.OrderCollectionByEmployeeID)
                        {
                            Assert.AreEqual(0, o.OrderItemCollectionByOrderID.Count);
                        }
                        break;

                    default:
                        Assert.Fail("Only employees 1, 2, and 3 should be loaded.");
                        break;
                }
            }
        }