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 bool GetActiveProductIds(int employeeId)
        {
            ProductQuery prd = new ProductQuery("pq");
            //prd.es.Connection.Name = "ForeignKeyTest";
            OrderItemQuery item = new OrderItemQuery("oiq");
            //item.es.Connection.Name = "ForeignKeyTest";
            OrderQuery ord = new OrderQuery("oq");
            //ord.es.Connection.Name = "ForeignKeyTest";
            CustomerQuery cust = new CustomerQuery("cq");
            //cust.es.Connection.Name = "ForeignKeyTest";
            EmployeeQuery emp = new EmployeeQuery("eq");
            //emp.es.Connection.Name = "ForeignKeyTest";

            prd.Select(prd.ProductID);
            prd.InnerJoin(item).On(prd.ProductID == item.ProductID);
            prd.InnerJoin(ord).On(item.OrderID == ord.OrderID);
            prd.InnerJoin(cust).On(ord.CustID == cust.CustomerID &
                ord.CustSub == cust.CustomerSub);
            prd.InnerJoin(emp).On(cust.Manager == emp.EmployeeID);
            prd.Where(emp.EmployeeID == employeeId);
            prd.Where(prd.Discontinued == false);
            prd.es.Distinct = true;

            return this.Load(prd);
        }
        public void LoadJoined()
        {
            CustomerQuery cq = new CustomerQuery("c");
            EmployeeQuery eq = new EmployeeQuery("e");
            EmployeeQuery eq2 = new EmployeeQuery("e2");
            OrderQuery oq = new OrderQuery("o");
            OrderItemQuery oiq = new OrderItemQuery("oi");
            ProductQuery pq = new ProductQuery("p");

            cq.Select(
                cq.CustomerID,
                cq.CustomerSub,
                cq.CustomerName,
                eq,
                eq2.LastName.As("ReportsTo"),
                oq.PlacedBy,
                oq.OrderDate,
                oiq,
                pq.ProductName,
                pq.Discontinued);
            cq.LeftJoin(eq).On(eq.EmployeeID == cq.Manager);
            cq.LeftJoin(eq2).On(eq.Supervisor == eq2.EmployeeID);
            cq.LeftJoin(oq).On(cq.CustomerID == oq.CustID
                && cq.CustomerSub == oq.CustSub);
            cq.LeftJoin(oiq).On(oq.OrderID == oiq.OrderID);
            cq.LeftJoin(pq).On(oiq.ProductID == pq.ProductID);

            CustomerCollection coll = new CustomerCollection();
            coll.es.Connection.Name = "ForeignKeyTest";

            Assert.IsTrue(coll.Load(cq));
            Assert.AreEqual(69, coll.Count);
        }
        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 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 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 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 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 bool Load(OrderQuery query)
 {
     this.query = query;
     InitQuery(this.query);
     return(Query.Load());
 }
        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 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);
        }
        public void MixedANDAndORInOn()
        {
            ProductCollection collection = new ProductCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            int empId = 1;

            ProductQuery prd = new ProductQuery("pq");
            OrderItemQuery item = new OrderItemQuery("oiq");
            OrderQuery ord = new OrderQuery("oq");
            CustomerQuery cust = new CustomerQuery("cq");
            EmployeeQuery emp = new EmployeeQuery("eq");

            prd.Select(prd.ProductID);
            prd.InnerJoin(item).On(prd.ProductID == item.ProductID);
            prd.InnerJoin(ord).On(item.OrderID == ord.OrderID);
            prd.InnerJoin(cust).On(ord.CustID == cust.CustomerID &
                (ord.CustSub == cust.CustomerSub |
                ord.EmployeeID == cust.StaffAssigned));
            prd.InnerJoin(emp).On(cust.Manager == emp.EmployeeID);
            prd.Where(emp.EmployeeID == empId);
            prd.Where(prd.Discontinued == false);
            prd.OrderBy(prd.ProductID.Ascending);

            Assert.IsTrue(collection.Load(prd));
            Assert.AreEqual(9, collection.Count);
        }
        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 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 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 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 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;
            }
        }