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);
        }
        private bool LoadByPrimaryKeyDynamic(System.Int32 employeeID)
        {
            EmployeeQuery query = new EmployeeQuery();

            query.Where(query.EmployeeID == employeeID);
            return(this.Load(query));
        }
        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 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));
        }
        protected void InitQuery(EmployeeQuery query)
        {
            query.OnLoadDelegate = this.OnQueryLoaded;

            if (!query.es2.HasConnection)
            {
                query.es2.Connection = ((IEntityCollection)this).Connection;
            }
        }
        public void SimpleIntersect()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.Name = "ForeignKeyTest";

            EmployeeQuery eq1 = new EmployeeQuery("eq1");
            EmployeeQuery eq2 = new EmployeeQuery("eq2");

            // Only includes rows with both "n" and"a"
            eq1.Where(eq1.FirstName.Like("%n%"));
            eq1.Intersect(eq2);
            eq2.Where(eq2.FirstName.Like("%a%"));

            Assert.IsTrue(collection.Load(eq1));
            Assert.AreEqual(2, collection.Count);
        }
        public void SimpleExcept()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.Name = "ForeignKeyTest";

            EmployeeQuery eq1 = new EmployeeQuery("eq1");
            EmployeeQuery eq2 = new EmployeeQuery("eq2");

            // Includes all "J"s except "Jim"
            eq1.Where(eq1.FirstName.Like("%J%"));
            eq1.Except(eq2);
            eq2.Where(eq2.FirstName == "Jim");

            Assert.IsTrue(collection.Load(eq1));
            Assert.AreEqual(3, collection.Count);
        }
        public void ConcatTwoStringsAndTwoLiterals()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            EmployeeQuery eq = new EmployeeQuery("eq");

            eq.Select(eq.EmployeeID,
                (eq.LastName + " (" + eq.FirstName + ")").As("FullName"));

            Assert.IsTrue(collection.Load(eq));

            string theName = collection[0].GetColumn("FullName") as string;
            Assert.AreEqual(12, theName.Length);
        }
        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 SimpleUnion()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.Name = "ForeignKeyTest";

            EmployeeQuery eq1 = new EmployeeQuery("eq1");
            EmployeeQuery eq2 = new EmployeeQuery("eq2");

            // This leaves out the record with Age 30
            eq1.Where(eq1.Age < 30);
            eq1.Union(eq2);
            eq2.Where(eq2.Age > 30);

            Assert.IsTrue(collection.Load(eq1));
            Assert.AreEqual(4, collection.Count);
        }
		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 TestSerializeQuery()
        {
            EmployeeQuery query = new EmployeeQuery("e");
            query.Where(query.EmployeeID.In(1, 2, 3, new List<object>() { 1, 2, 3 }));

            string qq = EmployeeQuery.SerializeHelper.ToXml(query);

            List<Type> types = new List<Type>();
            types.Add(typeof(EmployeeQuery));

            EmployeeQuery employeeQuery = EmployeeQuery.SerializeHelper.FromXml(
                qq, typeof(EmployeeQuery), types) as EmployeeQuery;

            EmployeeCollection c = new EmployeeCollection();
            c.es.Connection.Name = "ForeignKeyTest";
            c.Load(employeeQuery);

            Assert.IsTrue(c.Count == 3);
        }
        public void InnerJoinFourTables()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            EmployeeQuery emp = new EmployeeQuery("e");
            EmployeeTerritoryQuery empTerr = new EmployeeTerritoryQuery("et");
            TerritoryQuery terr = new TerritoryQuery("t");
            TerritoryExQuery terrEx = new TerritoryExQuery("tx");

            emp.Select(emp.FirstName, emp.LastName, terr.Description.As("Territory"), terrEx.Notes)
                .InnerJoin(empTerr).On(emp.EmployeeID == empTerr.EmpID)
                .InnerJoin(terr).On(terr.TerritoryID == empTerr.TerrID)
                .InnerJoin(terrEx).On(terrEx.TerritoryID == terr.TerritoryID)
                .Where(terrEx.Notes.IsNotNull());

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

            string theName = collection[1].GetColumn("Territory") as string;
            Assert.AreEqual("North", theName);
        }
        public void WhereExists()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            // SubQuery of Employees with a null Supervisor column.
            EmployeeQuery sq = new EmployeeQuery("s");
            sq.es.Distinct = true;
            sq.Select(sq.EmployeeID);
            sq.Where(sq.Supervisor.IsNull());

            // If even one employee has a null supervisor,
            // i.e., the above query has a result set,
            // then run a list of all employees.
            EmployeeQuery eq = new EmployeeQuery("e");
            eq.Select(eq.EmployeeID, eq.Supervisor);
            eq.Where(eq.Exists(sq));

            Assert.IsTrue(collection.Load(eq));
            Assert.AreEqual(5, collection.Count);
        }
        public void SerializeDeserializeJoin()
        {
            if (aggTest.es.Connection.Name == "SqlCe")
            {
                Assert.Ignore("Not tested for SqlCe.");
            }
            else
            {
                // Test serializing a DynamicQuery
                // Must use binary serialization
                // Xml serialization doesn't serialize all of your private properties
                // 1) Create our Query on the client
                EmployeeQuery emp = new EmployeeQuery("eq");
                //emp.es.Connection.Name = "ForeignKeyTest";
                EmployeeTerritoryQuery et = new EmployeeTerritoryQuery("etq");
                //et.es.Connection.Name = "ForeignKeyTest";
                emp.Select(emp.FirstName, emp.LastName, et.TerrID);
                emp.InnerJoin(et).On(emp.EmployeeID == et.EmpID);
                emp.Where(emp.LastName.Like("S%"));

                // 2) Serialize it in binary
                BinaryFormatter bf = new BinaryFormatter();
                MemoryStream ms = new MemoryStream();
                bf.Serialize(ms, emp);
                byte[] query = ms.ToArray();

                // 3) Send it over the wire

                // 4) Deserialize it on the Server
                bf = new BinaryFormatter();
                ms = new MemoryStream(query);
                EmployeeQuery newQuery = bf.Deserialize(ms) as EmployeeQuery;

                // Now load it 
                EmployeeCollection collection = new EmployeeCollection();
                collection.es.Connection.Name = "ForeignKeyTest";

                collection.Load(newQuery);

                Assert.AreEqual(5, collection.Count);
                Assert.AreEqual("S", collection[0].LastName.Substring(0, 1));
            }
        }
        public void JoinFourTablesInnerLeft()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

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

                default:
                    EmployeeQuery emp = new EmployeeQuery("e");
                    EmployeeTerritoryQuery empTerr = new EmployeeTerritoryQuery("et");
                    TerritoryQuery terr = new TerritoryQuery("t");
                    TerritoryExQuery terrEx = new TerritoryExQuery("tx");

                    emp.Select(emp.FirstName, emp.LastName, terr.Description.As("Territory"), terrEx.Notes);
                    emp.LeftJoin(empTerr).On(emp.EmployeeID == empTerr.EmpID);
                    emp.InnerJoin(terr).On(empTerr.TerrID == terr.TerritoryID);
                    emp.LeftJoin(terrEx).On(terr.TerritoryID == terrEx.TerritoryID);

                    Assert.IsTrue(collection.Load(emp));
                    Assert.AreEqual(8, collection.Count);
                    break;
            }
        }
        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 WhereInANDedTogetherOperator()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            EmployeeQuery eq1 = new EmployeeQuery("e1");
            EmployeeQuery eq2 = new EmployeeQuery("e2");
            EmployeeQuery eq3 = new EmployeeQuery("e3");

            eq2.Select(eq2.EmployeeID);
            eq3.Select(eq3.EmployeeID);

            eq1.Where(eq1.EmployeeID.In(eq2) & eq1.EmployeeID.In(eq3));

            Assert.IsTrue(collection.Load(eq1));
            Assert.AreEqual(5, collection.Count);

            string lq = collection.Query.es.LastQuery;
            string[] one = lq.Split('1');
            Assert.AreEqual(4, one.GetLength(0));
            string[] two = lq.Split('2');
            Assert.AreEqual(3, two.GetLength(0));
            string[] three = lq.Split('3');
            Assert.AreEqual(3, three.GetLength(0));
        }
        public void CrossDbJoin()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            switch (collection.es.Connection.ProviderSignature.DataProviderName)
            {
                case "EntitySpaces.SqlClientProvider":
                    // AggregateDb
                    AggregateTestQuery aq = new AggregateTestQuery("a");
                    // ForeignKeyTest
                    EmployeeQuery eq = new EmployeeQuery("e");

                    eq.Select(eq.LastName, eq.FirstName, aq.Age);
                    eq.LeftJoin(aq).On(
                        eq.LastName == aq.LastName &
                        eq.FirstName == aq.FirstName);
                    eq.OrderBy(eq.LastName.Ascending,
                        eq.FirstName.Ascending);

                    Assert.IsTrue(collection.Load(eq));
                    Assert.AreEqual(22, collection[2].GetColumn("Age"));
                    break;

                default:
                    Assert.Ignore("SQL Server only");
                    break;
            }
        }
        public void OneAddIntegers()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.Name = "ForeignKeyTest";


            EmployeeQuery eq = new EmployeeQuery("eq");

            eq.Select(eq.EmployeeID, eq.Age,
                (eq.Age + eq.Supervisor).As("SomeInt"));
            eq.OrderBy(eq.EmployeeID, esOrderByDirection.Ascending);

            Assert.IsTrue(collection.Load(eq));

            object si = collection[0].GetColumn("SomeInt");
            Assert.AreEqual(null, si);

            int someInt = Convert.ToInt32(collection[1].GetColumn("SomeInt"));
            Assert.AreEqual(21, someInt);
        }
        public void TwoAddStringsWithOrderBy()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            EmployeeQuery eq = new EmployeeQuery("eq");

            eq.Select(eq.EmployeeID,
                (eq.LastName + ", " + eq.FirstName).As("FullName"));
            eq.OrderBy(eq.EmployeeID, esOrderByDirection.Ascending);

            Assert.IsTrue(collection.Load(eq));

            string theName = collection[0].GetColumn("FullName") as string;
            Assert.AreEqual(11, theName.Length);
        }
        public void TwoAddStringsThenConcatenated()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            EmployeeQuery eq = new EmployeeQuery("eq");

            eq.Select
            (
                eq.EmployeeID,
                (
                    (eq.LastName.ToLower() + eq.FirstName.ToLower()).Trim() +
                    " : " +
                    (eq.LastName.ToUpper() + eq.FirstName.ToUpper()).Trim()
                ).Trim().As("SomeColumn")
            );

            Assert.IsTrue(collection.Load(eq));

            string theName = collection[0].GetColumn("SomeColumn") as string;
            Assert.AreEqual("smithjohn : SMITHJOHN", theName);
        }
        public void WhereExistsFalse()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            // EmployeeID is required and will never be NULL
            EmployeeQuery sq = new EmployeeQuery("s");
            sq.es.Distinct = true;
            sq.Select(sq.EmployeeID);
            sq.Where(sq.EmployeeID.IsNull());

            // This should produce no results as the
            // inner query does not exist.
            EmployeeQuery eq = new EmployeeQuery("e");
            eq.Select(eq.EmployeeID, eq.Supervisor);
            eq.Where(eq.Exists(sq));

            Assert.IsFalse(collection.Load(eq));
        }
        public void TwoAddStringsWithToUpper()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            EmployeeQuery eq = new EmployeeQuery("eq");

            eq.Select(eq.EmployeeID,
                (eq.LastName.ToUpper() + ", " + eq.FirstName).As("FullName"));
            eq.Where(eq.LastName == "Doe");
            eq.OrderBy(eq.FirstName.Ascending);

            Assert.IsTrue(collection.Load(eq));

            string theName = collection[0].GetColumn("FullName") as string;
            Assert.AreEqual("DOE, Jane", theName);
        }
        public void WhereExistsANDed()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            EmployeeQuery eq1 = new EmployeeQuery("e1");
            EmployeeQuery eq2 = new EmployeeQuery("e2");

            eq1.Where(eq1.EmployeeID > 2, eq1.Exists(eq2)); 

            Assert.IsTrue(collection.Load(eq1));
            Assert.AreEqual(3, collection.Count);

            string lq = collection.Query.es.LastQuery;
            string[] one = lq.Split('1');
            Assert.AreEqual(4, one.GetLength(0));
            string[] two = lq.Split('2');
            Assert.AreEqual(2, two.GetLength(0));
        }
        public void JoinWithPaging()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            switch (collection.es.Connection.ProviderSignature.DataProviderName)
            {
                case "EntitySpaces.MSAccessProvider":
                case "EntitySpaces.SqlServerCeProvider":
                case "EntitySpaces.VistaDBProvider":
                case "EntitySpaces.VistaDB4Provider":
                    Assert.Ignore("Not supported");
                    break;
                default:
                    EmployeeQuery emp = new EmployeeQuery("e");
                    EmployeeTerritoryQuery empTerr = new EmployeeTerritoryQuery("et");
                    TerritoryQuery terr = new TerritoryQuery("t");
                    TerritoryExQuery terrEx = new TerritoryExQuery("tx");

                    emp.Select(emp, terr.Description.As("Territory"), terrEx.Notes);
                    emp.InnerJoin(empTerr).On(empTerr.TerrID == emp.EmployeeID);
                    emp.InnerJoin(terr).On(terr.TerritoryID == empTerr.TerrID);
                    emp.InnerJoin(terrEx).On(terrEx.TerritoryID == terr.TerritoryID);
                    emp.Where(terrEx.Notes.IsNotNull());
                    emp.OrderBy(emp.FirstName.Ascending);

                    emp.es.PageNumber = 1;
                    emp.es.PageSize = 20;

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

                    break;
            }
        }
        public void WhereWithJoin()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            // SubQuery of Territories
            TerritoryQuery tq = new TerritoryQuery("t");
            tq.Select(tq.TerritoryID);
            tq.Where(tq.Description == "North" |
                tq.Description == "West");

            // EmployeeTerritory Query for Join
            EmployeeTerritoryQuery etq = new EmployeeTerritoryQuery("et");

            // Employees matching those territories
            EmployeeQuery eq = new EmployeeQuery("e");
            eq.es.Distinct = true;
            eq.Select(eq.EmployeeID, etq.TerrID);
            eq.LeftJoin(etq).On(
                eq.EmployeeID == etq.EmpID);
            eq.Where(etq.TerrID.In(tq));
            eq.OrderBy(eq.EmployeeID.Ascending);

            Assert.IsTrue(collection.Load(eq));
            Assert.AreEqual(3, collection.Count);
        }
 public bool Load(EmployeeQuery query)
 {
     this.query = query;
     InitQuery(this.query);
     return(Query.Load());
 }
        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 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 MultiExpression()
        {
            EmployeeCollection collection = new EmployeeCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            EmployeeQuery eq = new EmployeeQuery("eq");

            eq.Select(eq.EmployeeID, eq.Age,
                ((eq.Age + eq.Supervisor) / 3).As("SomeInt"));
            eq.OrderBy(eq.EmployeeID, esOrderByDirection.Ascending);

            Assert.IsTrue(collection.Load(eq));

            object si = collection[0].GetColumn("SomeInt");
            Assert.AreEqual(null, si);

            int someInt = Convert.ToInt32(collection[1].GetColumn("SomeInt"));
            Assert.AreEqual(7, someInt);
        }
        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"));
        }