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 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 InnerSimple()
		{
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.Name = "ForeignKeyTest";
            //collection.es.Connection.ConnectionString =
            //    UnitTestBase.GetFktString(collection.es.Connection);

            EmployeeQuery eq = new EmployeeQuery("eq");
            CustomerQuery cq = new CustomerQuery("cq");

            eq.Select(eq.EmployeeID, eq.LastName, cq.CustomerName);
            eq.InnerJoin(cq).On(eq.EmployeeID == cq.StaffAssigned);

            Assert.IsTrue(collection.Load(eq));
            Assert.AreEqual(10, collection.Count);
        }
        public void SingleUnion()
        {
            CustomerCollection coll = new CustomerCollection();
            coll.es.Connection.Name = "ForeignKeyTest";

            CustomerQuery cq1 = new CustomerQuery("c1");
            cq1.SelectAllExcept(cq1.Notes);
            cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31")));

            CustomerQuery cq2 = new CustomerQuery("c2");
            cq2.SelectAllExcept(cq2.Notes);
            cq2.Where(cq2.DateAdded.Between(Convert.ToDateTime("2006-01-01"), Convert.ToDateTime("2006-12-31")));

            // Combine 2005 and 2006 in one result set
            cq1.Union(cq2);

            //string lq = cq1.Parse();

            Assert.IsTrue(coll.Load(cq1));
            Assert.AreEqual(49, coll.Count);
        }
        public void LeftWithExplicitParen()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            EmployeeQuery eq = new EmployeeQuery("eq");
            CustomerQuery cq = new CustomerQuery("cq");

            eq.Select(eq.EmployeeID, eq.LastName, cq.CustomerName);
            eq.LeftJoin(cq).On(eq.EmployeeID == cq.StaffAssigned);

            eq.Where(eq.Age == 20);

            eq.Where(new esComparison(esParenthesis.Open));

            eq.es.DefaultConjunction = esConjunction.Or;

            for (int i = 0; i < 4; i++)
            {
                eq.Where(
                    eq.Supervisor == i &
                    eq.EmployeeID == i + 1);
            }

            eq.Where(new esComparison(esParenthesis.Close));

            Assert.IsTrue(collection.Load(eq));
            Assert.AreEqual(5, 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 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 SingleIntersectWithNoOverlap()
        {
            CustomerCollection coll = new CustomerCollection();
            coll.es.Connection.Name = "ForeignKeyTest";

            CustomerQuery cq1 = new CustomerQuery("c1");
            cq1.SelectAllExcept(cq1.Notes);
            cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31")));

            CustomerQuery cq2 = new CustomerQuery("c2");
            cq2.SelectAllExcept(cq2.Notes);
            cq2.Where(cq2.DateAdded.Between(Convert.ToDateTime("2006-01-01"), Convert.ToDateTime("2006-12-31")));

            // There is no intersection for 2005 and 2006
            cq1.Intersect(cq2);

            Assert.IsFalse(coll.Load(cq1));
            Assert.AreEqual(0, coll.Count);
        }
        public void SingleExceptWithNoOverlap()
        {
            CustomerCollection coll = new CustomerCollection();
            coll.es.Connection.Name = "ForeignKeyTest";

            CustomerQuery cq1 = new CustomerQuery("c1");
            cq1.SelectAllExcept(cq1.Notes);
            cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2006-12-31")));

            CustomerQuery cq2 = new CustomerQuery("c2");
            cq2.SelectAllExcept(cq2.Notes);
            cq2.Where(cq2.DateAdded == Convert.ToDateTime("1900-12-31"));

            // All 2005 and 2006
            cq1.Except(cq2);

            Assert.IsTrue(coll.Load(cq1));
            Assert.AreEqual(49, coll.Count);
        }
        public void LeftRawSelect()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            EmployeeQuery eq = new EmployeeQuery("eq");
            CustomerQuery cq = new CustomerQuery("cq");

            string special = "";
            switch (collection.es.Connection.ProviderSignature.DataProviderName)
            {
                case "EntitySpaces.MSAccessProvider":
                    special = "<cq.[CustomerID] + '-' + cq.[CustomerSub] AS FullCustomerId>";
                    break;

                case "EntitySpaces.MySqlClientProvider":
                    special = "<CONCAT(cq.`CustomerID`, '-', cq.`CustomerSub`) AS 'FullCustomerId'>";
                    break;

                case "EntitySpaces.NpgsqlProvider":
                case "EntitySpaces.Npgsql2Provider":
                case "EntitySpaces.OracleClientProvider":
                    special = "<cq.\"CustomerID\" || '-' || cq.\"CustomerSub\" AS \"FullCustomerId\">";
                    break;

                default:
                    if (collection.es.Connection.Name == "SqlCe")
                    {
                        special = "<cq.[CustomerID] + '-' + cq.[CustomerSub] AS \"FullCustomerId\">";
                    }
                    else
                    {
                        special = "<cq.[CustomerID] + '-' + cq.[CustomerSub] As FullCustomerId>";
                    }
                    break;
            }

            eq.Select(eq.EmployeeID, eq.LastName, special, cq.CustomerName);

            eq.LeftJoin(cq).On(eq.EmployeeID == cq.StaffAssigned);

            Assert.IsTrue(collection.Load(eq));
            Assert.AreEqual(11, collection.Count);
        }
        public void LeftWithContains()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            switch (collection.es.Connection.ProviderSignature.DataProviderName)
            {
                case "EntitySpaces.SqlClientProvider":
                    string nameTerm =
                        "acme NEAR company";

                    EmployeeQuery eq = new EmployeeQuery("eq");
                    CustomerQuery cq = new CustomerQuery("cq");

                    eq.Select(eq.EmployeeID, eq.LastName, cq.CustomerName);
                    eq.LeftJoin(cq).On(eq.EmployeeID == cq.StaffAssigned);
                    eq.Where(cq.CustomerName.Contains(nameTerm));

                    Assert.IsTrue(collection.Load(eq));
                    Assert.AreEqual(2, collection.Count);
                    break;

                default:
                    Assert.Ignore("Not supported");
                    break;
            }
        }
        public void LeftWithDateComparisonInOn()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            EmployeeQuery eq = new EmployeeQuery("eq");
            CustomerQuery cq = new CustomerQuery("cq");

            eq.Select(eq.LastName);
            eq.LeftJoin(cq).On(eq.EmployeeID == cq.StaffAssigned &
                cq.DateAdded < Convert.ToDateTime("2005-12-31"));
            eq.Where(cq.DateAdded < Convert.ToDateTime("2000-12-31"));
            eq.OrderBy(eq.LastName.Ascending);

            Assert.IsTrue(collection.Load(eq));
            Assert.AreEqual(7, collection.Count);
        }
        public void LeftWithOperatorsInOn()
        {
            int record = 0;
            CustomerCollection collection = new CustomerCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            switch (collection.es.Connection.ProviderSignature.DataProviderName)
            {
                //case "EntitySpaces.NpgsqlProvider":
                //case "EntitySpaces.Npgsql2Provider":
                case "EntitySpaces.OracleClientProvider":
                    record = 2;
                    CustomerQuery cq = new CustomerQuery("cq");
                    EmployeeQuery eq = new EmployeeQuery("eq");

                    cq.Select(cq.CustomerName, eq.LastName);
                    cq.LeftJoin(eq).On(cq.StaffAssigned == eq.EmployeeID &
                        eq.Supervisor == 1);
                    cq.OrderBy(cq.CustomerName.Ascending);

                    Assert.IsTrue(collection.Load(cq));
                    Assert.AreEqual(56, collection.Count);
                    Assert.AreEqual("Doe", collection[record].GetColumn("LastName"));
                    break;
                default:
                    record = 1;
                    cq = new CustomerQuery("cq");
                    eq = new EmployeeQuery("eq");

                    cq.Select(cq.CustomerName, eq.LastName);
                    cq.LeftJoin(eq).On(cq.StaffAssigned == eq.EmployeeID &
                        eq.Supervisor == 1);
                    cq.OrderBy(cq.CustomerName.Ascending);

                    Assert.IsTrue(collection.Load(cq));
                    Assert.AreEqual(56, collection.Count);
                    Assert.AreEqual("Doe", collection[record].GetColumn("LastName"));
                    break;
            }
        }
        public void LeftWithWhere()
        {
            CustomerCollection collection = new CustomerCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            CustomerQuery cust = new CustomerQuery("cq");
            EmployeeQuery emp = new EmployeeQuery("eq");

            cust.Select(cust.CustomerID, emp.LastName,
                cust.Manager, cust.StaffAssigned);
            cust.LeftJoin(emp).On(cust.Manager ==
                emp.EmployeeID);
            cust.Where(
                cust.Manager == cust.StaffAssigned);

            Assert.IsTrue(collection.Load(cust));
            Assert.AreEqual(4, collection.Count);
        }
        public void LeftIsNotNull()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            EmployeeQuery eq = new EmployeeQuery("eq");
            CustomerQuery cq = new CustomerQuery("cq");

            eq.Select(eq.EmployeeID, eq.LastName, cq.CustomerName);
            eq.LeftJoin(cq).On(eq.EmployeeID == cq.Manager &
                cq.StaffAssigned.IsNotNull());

            Assert.IsTrue(collection.Load(eq));
            Assert.AreEqual(13, collection.Count);
        }
        public void SingleUnionWithJoin()
        {
            CustomerCollection coll = new CustomerCollection();
            coll.es.Connection.Name = "ForeignKeyTest";

            CustomerQuery cq1 = new CustomerQuery("c1");
            EmployeeQuery eq1 = new EmployeeQuery("e1");

            cq1.Select(cq1.CustomerID, cq1.CustomerSub, cq1.CustomerName, eq1.LastName);
            cq1.InnerJoin(eq1).On(cq1.Manager == eq1.EmployeeID);
            cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31")));

            CustomerQuery cq2 = new CustomerQuery("c2");
            EmployeeQuery eq2 = new EmployeeQuery("e2");

            cq2.Select(cq2.CustomerID, cq2.CustomerSub, cq2.CustomerName, eq2.LastName);
            cq2.InnerJoin(eq2).On(cq2.Manager == eq2.EmployeeID);
            cq2.Where(cq2.DateAdded.Between(Convert.ToDateTime("2006-01-01"), Convert.ToDateTime("2006-12-31")));

            cq1.Union(cq2);
            cq1.OrderBy(cq1.CustomerID.Ascending, cq1.CustomerSub.Ascending);

            //string lq = cq1.Parse();

            Assert.IsTrue(coll.Load(cq1));
            Assert.AreEqual(49, coll.Count);
            Assert.AreEqual("Smith", coll[0].GetColumn("LastName"));
        }
        public void SingleUnionAllWithOverlap()
        {
            CustomerCollection coll = new CustomerCollection();
            coll.es.Connection.Name = "ForeignKeyTest";

            CustomerQuery cq1 = new CustomerQuery("c1");
            cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31")));

            CustomerQuery cq2 = new CustomerQuery("c2");
            cq2.Where(cq2.DateAdded.Between(Convert.ToDateTime("2005-12-31"), Convert.ToDateTime("2006-12-31")));

            // Combine 2005 and 2006 in one result set with the 2 duplicate rows retained
            cq1.UnionAll(cq2);

            Assert.IsTrue(coll.Load(cq1));
            Assert.AreEqual(51, coll.Count);
        }
        public void RightSimple()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            switch (collection.es.Connection.ProviderSignature.DataProviderName)
            {
                case "EntitySpaces.SQLiteProvider":
                    Assert.Ignore("RIGHT JOIN not supported.");
                    break;

                default:
                    EmployeeQuery eq = new EmployeeQuery("eq");
                    CustomerQuery cq = new CustomerQuery("cq");

                    eq.Select(eq.EmployeeID, eq.LastName, cq.CustomerName);
                    eq.RightJoin(cq).On(eq.EmployeeID == cq.StaffAssigned);

                    Assert.IsTrue(collection.Load(eq));
                    Assert.AreEqual(56, collection.Count);
                    break;
            }
        }
        public void SingleIntersect()
        {
            CustomerCollection coll = new CustomerCollection();
            coll.es.Connection.Name = "ForeignKeyTest";

            CustomerQuery cq1 = new CustomerQuery("c1");
            cq1.SelectAllExcept(cq1.Notes);
            cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31")));

            CustomerQuery cq2 = new CustomerQuery("c2");
            cq2.SelectAllExcept(cq2.Notes);
            cq2.Where(cq2.DateAdded.Between(Convert.ToDateTime("2005-12-31"), Convert.ToDateTime("2006-12-31")));

            // Only the 2 rows for 2005-12-31
            cq1.Intersect(cq2);

            Assert.IsTrue(coll.Load(cq1));
            Assert.AreEqual(2, coll.Count);
        }
        public void RightWithWhereIn()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            switch (collection.es.Connection.ProviderSignature.DataProviderName)
            {
                case "EntitySpaces.SQLiteProvider":
                    Assert.Ignore("RIGHT JOIN not supported.");
                    break;

                default:
                    List<string> custList = new List<string>();
                    custList.Add("01001");
                    custList.Add("40000");
                    custList.Add("XXXXX");

                    EmployeeQuery eq = new EmployeeQuery("eq");
                    CustomerQuery cq = new CustomerQuery("cq");

                    eq.Select(eq.EmployeeID, eq.LastName, cq.CustomerID, cq.CustomerName);
                    eq.RightJoin(cq).On(eq.EmployeeID == cq.Manager);
                    eq.Where(cq.CustomerID.In(custList));

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

                    Assert.IsTrue(coll.Load(eq));
                    Assert.AreEqual(14, coll.Count);
                    break;
            }
        }
        public void RightWithFromSubQuery()
        {
            CustomerCollection coll = new CustomerCollection();
            coll.es.Connection.Name = "ForeignKeyTest";

            switch (coll.es.Connection.ProviderSignature.DataProviderName)
            {
                case "EntitySpaces.SQLiteProvider":
                    Assert.Ignore("RIGHT JOIN not supported.");
                    break;

                default:
                    CustomerQuery cq1 = new CustomerQuery("c1");
                    cq1.es.Top = 5;
                    cq1.Select(cq1.CustomerID, cq1.CustomerSub);
                    cq1.Where(cq1.Active == true);
                    cq1.OrderBy(cq1.CustomerID.Ascending);

                    CustomerQuery cq2 = new CustomerQuery("c2");

                    CustomerQuery cq3 = new CustomerQuery("c3");
                    cq3.Select(cq2);
                    cq3.From(cq1).As("cSub");
                    cq3.RightJoin(cq2).On(cq1.CustomerID == cq2.CustomerID && 
                        cq1.CustomerSub == cq2.CustomerSub);
                    cq3.Where(cq2.Active == true && cq1.CustomerID.IsNull());
                    cq3.OrderBy(cq2.CustomerID.Ascending);

                    Assert.IsTrue(coll.Load(cq3));
                    Assert.AreEqual(36, coll.Count);
                    break;
            }
        }
        public void FullSimple()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            if (collection.es.Connection.Name == "SqlCe")
            {
                Assert.Ignore("FULL JOIN not supported.");
            }
            else
            {
                switch (collection.es.Connection.ProviderSignature.DataProviderName)
                {
                    case "EntitySpaces.MSAccessProvider":
                    case "EntitySpaces.MySqlClientProvider":
                    case "EntitySpaces.SQLiteProvider":
                    case "EntitySpaces.SqlServerCeProvider":
                    case "EntitySpaces.SqlServerCe4Provider":
                    case "EntitySpaces.VistaDBProvider":
                    case "EntitySpaces.VistaDB4Provider":
                        Assert.Ignore("FULL JOIN not supported.");
                        break;

                    default:
                        EmployeeQuery eq = new EmployeeQuery("eq");
                        CustomerQuery cq = new CustomerQuery("cq");

                        eq.Select(eq.EmployeeID, eq.LastName, cq.CustomerName);
                        eq.FullJoin(cq).On(eq.EmployeeID == cq.StaffAssigned);

                        Assert.IsTrue(collection.Load(eq));
                        Assert.AreEqual(57, 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 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 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 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;
            }
        }
Exemple #28
0
 public bool Load(CustomerQuery query)
 {
     this.query = query;
     InitQuery(this.query);
     return(Query.Load());
 }