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());
}
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'.");
}
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());
}
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());
}
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());
}
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());
}
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());
}
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());
}
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());
}
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());
}
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());
}
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());
}
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());
}
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());
}
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());
}
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%'.");
}