コード例 #1
0
        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)
                .LeftJoin(empTerr)
                .On(emp.EmployeeID == empTerr.EmpID)
                .InnerJoin(terr)
                .On(empTerr.TerrID == terr.TerritoryID)
                .LeftJoin(terrEx)
                .On(terr.TerritoryID == terrEx.TerritoryID);

                Assert.IsTrue(collection.Load(emp));
                Assert.AreEqual(8, collection.Count);
                break;
            }
        }
コード例 #2
0
        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);
        }
コード例 #3
0
        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);
        }
コード例 #4
0
        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)
                .RightJoin(cq)
                .On(eq.EmployeeID == cq.StaffAssigned);

                Assert.IsTrue(collection.Load(eq));
                Assert.AreEqual(56, collection.Count);
                break;
            }
        }
コード例 #5
0
        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)
                .LeftJoin(cq)
                .On(eq.EmployeeID == cq.StaffAssigned)
                .Where(cq.CustomerName.Contains(nameTerm));

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

            default:
                Assert.Ignore("Not supported");
                break;
            }
        }
コード例 #6
0
        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)
            .LeftJoin(empTerr)
            .On(emp.EmployeeID == empTerr.EmpID)
            .LeftJoin(terr)
            .On(empTerr.TerrID == terr.TerritoryID)
            .LeftJoin(terrEx)
            .On(terr.TerritoryID == terrEx.TerritoryID)
            .Where(emp.FirstName.Trim().Like("J___"));

            Assert.IsTrue(collection.Load(emp));
            Assert.AreEqual(7, collection.Count);
        }
コード例 #7
0
        private void Form1_Load(object sender, EventArgs e)
        {
            EmployeeCollection coll  = new EmployeeCollection();
            EmployeeQuery      query = coll.Query; // short hand

            query.Select(query.EmployeeID, query.LastName, query.FirstName);

            if (coll.Query.Load())
            {
                this.dataGrid1.DataSource = coll;
            }
        }
コード例 #8
0
        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)
            .LeftJoin(cq)
            .On(eq.EmployeeID == cq.StaffAssigned);

            Assert.IsTrue(collection.Load(eq));
            Assert.AreEqual(11, collection.Count);
        }
コード例 #9
0
        public void LeftSimple()
        {
            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);

            Assert.IsTrue(collection.Load(eq));
            Assert.AreEqual(11, collection.Count);
        }
コード例 #10
0
        public void LeftSelfJoin()
        {
            EmployeeCollection collection = new EmployeeCollection();

            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);

            EmployeeQuery eq = new EmployeeQuery("eq");
            EmployeeQuery supervisorQuery = new EmployeeQuery("sq");

            eq.Select(eq.EmployeeID, eq.LastName,
                      supervisorQuery.LastName.As("Reports To"));
            eq.LeftJoin(supervisorQuery)
            .On(eq.Supervisor == supervisorQuery.EmployeeID);

            Assert.IsTrue(collection.Load(eq));
            Assert.AreEqual(5, collection.Count);
        }
コード例 #11
0
        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);
        }
コード例 #12
0
        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));
            }
        }
コード例 #13
0
        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);
        }
コード例 #14
0
        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);
        }
コード例 #15
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)
                .InnerJoin(empTerr)
                .On(empTerr.TerrID == emp.EmployeeID)
                .InnerJoin(terr)
                .On(terr.TerritoryID == empTerr.TerrID)
                .InnerJoin(terrEx)
                .On(terrEx.TerritoryID == terr.TerritoryID)
                .Where(terrEx.Notes.IsNotNull())
                .OrderBy(emp.FirstName.Ascending);

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

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

                break;
            }
        }
コード例 #16
0
        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);
        }
コード例 #17
0
        public void SimpleSyntaxCheckWithLike()
        {
            EmployeeQuery q = new EmployeeQuery();

            q.es2.Connection.Name = "ForeignKeyTest";

            q.Select
            (
                q.LastName
                .Case()
                .When(q.LastName.Like("%a")).Then("Last Name Contains an A")
                .Else("Last Name Doesnt Contain an A")
                .End().As("SpecialLastName")
            );

            EmployeeCollection coll = new EmployeeCollection();

            coll.es.Connection.Name = "ForeignKeyTest";
            coll.Load(q);
        }
コード例 #18
0
        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)
                    .FullJoin(cq)
                    .On(eq.EmployeeID == cq.StaffAssigned);

                    Assert.IsTrue(collection.Load(eq));
                    Assert.AreEqual(57, collection.Count);
                    break;
                }
            }
        }
コード例 #19
0
        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;
            }
        }
コード例 #20
0
        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);
        }
コード例 #21
0
        public void CombineJoinQueriedCollections()
        {
            EmployeeQuery eq1 = new EmployeeQuery("e1");
            CustomerQuery cq1 = new CustomerQuery("c1");

            eq1.Select(eq1, cq1.CustomerName);
            eq1.InnerJoin(cq1).On(eq1.EmployeeID == cq1.Manager);
            eq1.Where(eq1.EmployeeID == 1);

            EmployeeCollection collection = new EmployeeCollection();

            collection.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection.es.Connection);
            collection.Load(eq1);
            Assert.AreEqual(35, collection.Count);

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

            eq2.Select(eq2, cq2.CustomerName);
            eq2.InnerJoin(cq2).On(eq2.EmployeeID == cq2.Manager);
            eq2.Where(eq2.EmployeeID == 2);

            EmployeeCollection collection2 = new EmployeeCollection();

            collection2.es.Connection.ConnectionString =
                UnitTestBase.GetFktString(collection2.es.Connection);
            collection2.Load(eq2);
            Assert.AreEqual(12, collection2.Count);

            collection.Combine(collection2);
            Assert.AreEqual(47, collection.Count);

            foreach (Employee emp in collection)
            {
                string custName = CustomerMetadata.ColumnNames.CustomerName;
                Assert.IsTrue(emp.GetColumn(custName).ToString().Length > 0);
            }
        }
コード例 #22
0
        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);
        }
コード例 #23
0
        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));
        }
コード例 #24
0
        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 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)
            .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);
        }