private bool LoadByPrimaryKeyDynamic(System.Int32 territoryID)
        {
            TerritoryQuery query = new TerritoryQuery();

            query.Where(query.TerritoryID == territoryID);
            return(this.Load(query));
        }
        protected void InitQuery(TerritoryQuery query)
        {
            query.OnLoadDelegate = this.OnQueryLoaded;

            if (!query.es2.HasConnection)
            {
                query.es2.Connection = ((IEntityCollection)this).Connection;
            }
        }
        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 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 void WhereNotIn()
        {
            TerritoryCollection collection = new TerritoryCollection();
            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            // SubQuery of Territories that Employee 1 is assigned to.
            EmployeeTerritoryQuery etq = new EmployeeTerritoryQuery("et");
            etq.Select(etq.TerrID);
            etq.Where(etq.EmpID == 1);

            // Territories that Employee 1 is not assigned to.
            TerritoryQuery tq = new TerritoryQuery("t");
            tq.Select(tq.Description);
            tq.Where(tq.TerritoryID.NotIn(etq));
            tq.OrderBy(tq.TerritoryID.Ascending);

            Assert.IsTrue(collection.Load(tq));
            Assert.AreEqual(2, collection.Count);
            Assert.AreEqual("West", collection[0].GetColumn(
                TerritoryMetadata.ColumnNames.Description));
        }
 public bool Load(TerritoryQuery query)
 {
     this.query = query;
     InitQuery(this.query);
     return(Query.Load());
 }
        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 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 LeftJoinFourTablesWithWhere()
        {
            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);
            emp.LeftJoin(empTerr).On(emp.EmployeeID == empTerr.EmpID);
            emp.LeftJoin(terr).On(empTerr.TerrID == terr.TerritoryID);
            emp.LeftJoin(terrEx).On(terr.TerritoryID == terrEx.TerritoryID);

            emp.Where(emp.FirstName.Trim().Like("J___"));

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