コード例 #1
0
        public void TestEnrollmentQuery()
        {
            var entityTypeResolver   = new StaticEntityTypeResolver();
            var entityTypeRepository = new DSEntityTypeRepository(new MemoryCache(), entityTypeResolver);

            var search = new DSSearch(DSEnrollment.EntityName);

            search.AddSelect(DSEnrollment.Attrs.EnrollmentID);
            search.AddSelect(DSEnrollment.Attrs.Student.LastName);
            search.AddSelect(DSEnrollment.Attrs.Student.ID);
            search.AddSelect(DSEnrollment.Attrs.Course.Title);
            search.AddSelect(DSEnrollment.Attrs.Course.Department.Name);
            search.AddSelect(DSEnrollment.Attrs.Grade);

            search.Where.DSContains(DSEnrollment.Attrs.Course.Title, "e");

            search.AddOrder(DSEnrollment.Attrs.Student.LastName);
            search.Page     = 1;
            search.PageSize = 25;

            var queryBuilder = DSSearchTestHelpers.CreateSearchBuilder(entityTypeRepository, search, true);

            var expectedQuery = "SELECT e.EnrollmentID, p.LastName [Student.LastName], p.ID [Student.ID], c.Title [Course.Title], d.Name [Course.Department.Name], e.Grade" +
                                " FROM dbo.Enrollment e WITH(NOLOCK)" +
                                " JOIN dbo.Person p WITH(NOLOCK) ON e.StudentID = p.ID" +
                                " JOIN dbo.Course c WITH(NOLOCK) ON e.CourseID = c.CourseID" +
                                " JOIN dbo.Department d WITH(NOLOCK) ON c.DepartmentID = d.DepartmentID" +
                                " WHERE c.Title LIKE '%'+@p0+'%'";

            Assert.AreEqual(DSSearchTestHelpers.AddDefaultQueryOrder(expectedQuery, "p.LastName, e.EnrollmentID"), queryBuilder.ToString());
        }
コード例 #2
0
        public void TestParameterizedNumericInFilter()
        {
            var simpleSearch = SearchSampleData.SimpleTestObjectASearch;

            simpleSearch.Where.DSIn("Foo", new List <object> {
                1234, 2345, 3456, 4567, 5678
            });

            DSSearchParser searchParser;
            var            queryBuilder = DSSearchTestHelpers.CreateSearchBuilder(_entityTypeRepository, simpleSearch, out searchParser, true);

            var expectedQuery = "SELECT t.Foo" +
                                " FROM dbo.TestObjectATable t WITH(NOLOCK)" +
                                " WHERE t.Foo IN (@p0, @p1, @p2, @p3, @p4)";

            Assert.AreEqual(DSSearchTestHelpers.AddDefaultQueryOrder(expectedQuery, "t.TestObjectAKey"), queryBuilder.ToString());

            var sqlFilterBuilder = queryBuilder.WhereBuilder as DSSQLFilterBuilder;

            Assert.AreEqual(sqlFilterBuilder.Parameters.Count, 5, "Should be one parameter.");
            Assert.IsTrue(sqlFilterBuilder.Parameters.ContainsKey("@p0"), "Should have an '@p0' parameter.");
            Assert.AreEqual(sqlFilterBuilder.Parameters["@p0"], 1234, "Parameter value should be '1234'.");
            Assert.IsTrue(sqlFilterBuilder.Parameters.ContainsKey("@p4"), "Should have an '@p4' parameter.");
            Assert.AreEqual(sqlFilterBuilder.Parameters["@p4"], 5678, "Parameter value should be '5678'.");
        }
コード例 #3
0
        public void TestReferenceKeyQuery()
        {
            var simpleSearch = SearchSampleData.SimpleTestObjectBSearch;

            simpleSearch.AddSelect("ObjectA");

            var queryBuilder = DSSearchTestHelpers.CreateSearchBuilder(_entityTypeRepository, simpleSearch);

            var expectedQuery = "SELECT t.Foo, t.ObjectAKey [ObjectA]" +
                                " FROM dbo.TestObjectBTable t WITH(NOLOCK)";

            Assert.AreEqual(DSSearchTestHelpers.AddDefaultQueryOrder(expectedQuery, "t.TestObjectBKey"), queryBuilder.ToString());
        }
コード例 #4
0
        public void TestNoSortQuery()
        {
            var simpleSearch = SearchSampleData.SimpleTestObjectASearch;

            simpleSearch.AddSelect("Bar");

            var queryBuilder = DSSearchTestHelpers.CreateSearchBuilder(_entityTypeRepository, simpleSearch);

            var expectedQuery = "SELECT t.Foo, t.BarCol [Bar] FROM dbo.TestObjectATable t WITH(NOLOCK)";

            // By default should always be adding the Primary Key as a Sort
            Assert.AreEqual(DSSearchTestHelpers.AddDefaultQueryOrder(expectedQuery, "t.TestObjectAKey"), queryBuilder.ToString());
        }
コード例 #5
0
        public void TestMultipleSortsQuery()
        {
            var simpleSearch = SearchSampleData.SimpleTestObjectASearch;

            simpleSearch.AddSelect("Bar");
            simpleSearch.AddOrder("Foo").AddOrderDesc("Bar");

            var queryBuilder = DSSearchTestHelpers.CreateSearchBuilder(_entityTypeRepository, simpleSearch);

            var expectedQuery = "SELECT t.Foo, t.BarCol [Bar] FROM dbo.TestObjectATable t WITH(NOLOCK)";

            Assert.AreEqual(DSSearchTestHelpers.AddDefaultQueryOrder(expectedQuery, "t.Foo, t.BarCol DESC, t.TestObjectAKey"), queryBuilder.ToString());
        }
コード例 #6
0
        public void TestSortOnReferenceColumnQuery()
        {
            var simpleSearch = SearchSampleData.SimpleTestObjectBSearch;

            simpleSearch.AddSelect("Bar");
            simpleSearch.AddOrderDesc("ObjectA.Foo").AddOrderDesc("Bar");

            var queryBuilder = DSSearchTestHelpers.CreateSearchBuilder(_entityTypeRepository, simpleSearch);

            var expectedQuery = "SELECT t.Foo, t.BarCol [Bar] FROM dbo.TestObjectBTable t WITH(NOLOCK)" +
                                " JOIN dbo.TestObjectATable t2 WITH(NOLOCK) ON t.ObjectAKey = t2.TestObjectAKey";

            Assert.AreEqual(DSSearchTestHelpers.AddDefaultQueryOrder(expectedQuery, "t2.Foo DESC, t.BarCol DESC, t.TestObjectBKey"), queryBuilder.ToString());
        }
コード例 #7
0
        public void TestStringEqualsFilter()
        {
            var simpleSearch = SearchSampleData.SimpleTestObjectASearch;

            simpleSearch.Where.DSEquals("Bar", "a");

            var queryBuilder = DSSearchTestHelpers.CreateSearchBuilder(_entityTypeRepository, simpleSearch);

            var expectedQuery = "SELECT t.Foo" +
                                " FROM dbo.TestObjectATable t WITH(NOLOCK)" +
                                " WHERE t.BarCol = 'a'";

            Assert.AreEqual(DSSearchTestHelpers.AddDefaultQueryOrder(expectedQuery, "t.TestObjectAKey"), queryBuilder.ToString());
        }
コード例 #8
0
        public void TestDateLessThanOrEqualFilter()
        {
            var simpleSearch = SearchSampleData.SimpleTestObjectASearch;

            simpleSearch.Where.DSLessThanOrEqual("FooBar", DateTime.Parse("12/20/2016 4:45 PM"));

            var queryBuilder = DSSearchTestHelpers.CreateSearchBuilder(_entityTypeRepository, simpleSearch);

            var expectedQuery = "SELECT t.Foo" +
                                " FROM dbo.TestObjectATable t WITH(NOLOCK)" +
                                " WHERE t.FooBar <= '12/20/2016 4:45:00 PM'";

            Assert.AreEqual(DSSearchTestHelpers.AddDefaultQueryOrder(expectedQuery, "t.TestObjectAKey"), queryBuilder.ToString());
        }
コード例 #9
0
        public void TestNumericLessThanOrEqualFilter()
        {
            var simpleSearch = SearchSampleData.SimpleTestObjectASearch;

            simpleSearch.Where.DSLessThanOrEqual("Foo", 1234);

            var queryBuilder = DSSearchTestHelpers.CreateSearchBuilder(_entityTypeRepository, simpleSearch);

            var expectedQuery = "SELECT t.Foo" +
                                " FROM dbo.TestObjectATable t WITH(NOLOCK)" +
                                " WHERE t.Foo <= 1234";

            Assert.AreEqual(DSSearchTestHelpers.AddDefaultQueryOrder(expectedQuery, "t.TestObjectAKey"), queryBuilder.ToString());
        }
コード例 #10
0
        public void TestSimpleQuery()
        {
            var simpleSearch = SearchSampleData.SimpleTestObjectASearch;

            simpleSearch.AddSelect("Bar");

            var queryBuilder = DSSearchTestHelpers.CreateSearchBuilder(_entityTypeRepository, simpleSearch);

            var expectedQuery = "SELECT t.Foo, t.BarCol [Bar] FROM dbo.TestObjectATable t WITH(NOLOCK)";

            Assert.AreEqual(DSSearchTestHelpers.AddDefaultQueryOrder(expectedQuery, "t.TestObjectAKey"), queryBuilder.ToString());

            expectedQuery = "SELECT COUNT(*) FROM dbo.TestObjectATable t WITH(NOLOCK)";
            Assert.AreEqual(expectedQuery, ((DSSQLSearchBuilder)queryBuilder).ToStringForCount());
        }
コード例 #11
0
        public void TestVirtualOnReferenceQuery()
        {
            var simpleSearch = SearchSampleData.SimpleTestObjectCSearch;

            simpleSearch.AddSelect("ObjectB.ObjectAFoo");

            var queryBuilder = DSSearchTestHelpers.CreateSearchBuilder(_entityTypeRepository, simpleSearch);

            var expectedQuery = "SELECT t.A, t3.Foo [ObjectB.ObjectAFoo]" +
                                " FROM dbo.TestObjectCTable t WITH(NOLOCK)" +
                                " JOIN dbo.TestObjectBTable t2 WITH(NOLOCK) ON t.ObjectBKey = t2.TestObjectBKey" +
                                " JOIN dbo.TestObjectATable t3 WITH(NOLOCK) ON t2.ObjectAKey = t3.TestObjectAKey";

            Assert.AreEqual(DSSearchTestHelpers.AddDefaultQueryOrder(expectedQuery, "t.TestObjectCKey"), queryBuilder.ToString());
        }
コード例 #12
0
        public void TestDateInFilter()
        {
            var simpleSearch = SearchSampleData.SimpleTestObjectASearch;

            simpleSearch.Where.DSIn("FooBar", new List <object> {
                DateTime.Parse("12/20/2016 4:45 PM"), new DateTime(2016, 12, 15), new DateTime(2016, 12, 1)
            });

            var queryBuilder = DSSearchTestHelpers.CreateSearchBuilder(_entityTypeRepository, simpleSearch);

            var expectedQuery = "SELECT t.Foo" +
                                " FROM dbo.TestObjectATable t WITH(NOLOCK)" +
                                " WHERE t.FooBar IN ('12/20/2016 4:45:00 PM', '12/15/2016 12:00:00 AM', '12/1/2016 12:00:00 AM')";

            Assert.AreEqual(DSSearchTestHelpers.AddDefaultQueryOrder(expectedQuery, "t.TestObjectAKey"), queryBuilder.ToString());
        }
コード例 #13
0
        public void TestNumericInFilter()
        {
            var simpleSearch = SearchSampleData.SimpleTestObjectASearch;

            simpleSearch.Where.DSIn("Foo", new List <object> {
                1234, 2345, 3456, 4567, 5678
            });

            var queryBuilder = DSSearchTestHelpers.CreateSearchBuilder(_entityTypeRepository, simpleSearch);

            var expectedQuery = "SELECT t.Foo" +
                                " FROM dbo.TestObjectATable t WITH(NOLOCK)" +
                                " WHERE t.Foo IN (1234, 2345, 3456, 4567, 5678)";

            Assert.AreEqual(DSSearchTestHelpers.AddDefaultQueryOrder(expectedQuery, "t.TestObjectAKey"), queryBuilder.ToString());
        }
コード例 #14
0
        public void TestStringInFilter()
        {
            var simpleSearch = SearchSampleData.SimpleTestObjectASearch;

            simpleSearch.Where.DSIn("Bar", new List <object> {
                "a", "b", "c", "d", "e"
            });

            var queryBuilder = DSSearchTestHelpers.CreateSearchBuilder(_entityTypeRepository, simpleSearch);

            var expectedQuery = "SELECT t.Foo" +
                                " FROM dbo.TestObjectATable t WITH(NOLOCK)" +
                                " WHERE t.BarCol IN ('a', 'b', 'c', 'd', 'e')";

            Assert.AreEqual(DSSearchTestHelpers.AddDefaultQueryOrder(expectedQuery, "t.TestObjectAKey"), queryBuilder.ToString());
        }
コード例 #15
0
        public void TestFringeNumericIsBlankFilter()
        {
            var simpleSearch = SearchSampleData.SimpleTestObjectASearch;

            simpleSearch.Where = new DSPredicate
            {
                Column   = "Foo",
                Operator = DSSearchOperatorEnum.IsBlank,
                Value    = 1234
            };

            var queryBuilder = DSSearchTestHelpers.CreateSearchBuilder(_entityTypeRepository, simpleSearch);

            var expectedQuery = "SELECT t.Foo" +
                                " FROM dbo.TestObjectATable t WITH(NOLOCK)" +
                                " WHERE t.Foo IS NULL";

            Assert.AreEqual(DSSearchTestHelpers.AddDefaultQueryOrder(expectedQuery, "t.TestObjectAKey"), queryBuilder.ToString());
        }
コード例 #16
0
        public void TestParameterizedContainsFilter()
        {
            var simpleSearch = SearchSampleData.SimpleTestObjectASearch;

            simpleSearch.Where.DSContains("Bar", "a");

            DSSearchParser searchParser;
            var            queryBuilder = DSSearchTestHelpers.CreateSearchBuilder(_entityTypeRepository, simpleSearch, out searchParser, true);

            var expectedQuery = "SELECT t.Foo" +
                                " FROM dbo.TestObjectATable t WITH(NOLOCK)" +
                                " WHERE t.BarCol LIKE '%'+@p0+'%'";

            Assert.AreEqual(DSSearchTestHelpers.AddDefaultQueryOrder(expectedQuery, "t.TestObjectAKey"), queryBuilder.ToString());

            var sqlFilterBuilder = queryBuilder.WhereBuilder as DSSQLFilterBuilder;

            Assert.AreEqual(sqlFilterBuilder.Parameters.Count, 1, "Should be one parameter.");
            Assert.IsTrue(sqlFilterBuilder.Parameters.ContainsKey("@p0"), "Should have an '@p0' parameter.");
            Assert.AreEqual(sqlFilterBuilder.Parameters["@p0"], "a", "Parameter value should be '%a%'.");
        }