public void TestAggregateConditionAll()
{
const int freight = 0;
var count = (from customer in context.Customers
where customer.Orders.All(order => order.Freight >= freight)
select customer).Count();
QueryDesigner queryDesigner = new QueryDesigner(context, typeof(Customers));
ConditionList conditions = new ConditionList(new Condition("Freight", freight, ConditionOperator.GreaterThanOrEqualTo));
PredicateAggregationCondition predicateAggregationCondition = new AllCondition("Orders", conditions);
queryDesigner.Where(predicateAggregationCondition);
List<Customers> customers = queryDesigner.Cast<Customers>().ToList();
Assert.AreEqual(count, customers.Count);
count = (from customer in context.Customers
where !customer.Orders.All(order => order.Freight >= freight)
select customer).Count();
queryDesigner = new QueryDesigner(context, typeof(Customers));
conditions = new ConditionList(new Condition("Freight", freight, ConditionOperator.GreaterThanOrEqualTo));
predicateAggregationCondition = new AllCondition("Orders", conditions, false);
queryDesigner.Where(predicateAggregationCondition);
customers = queryDesigner.Cast<Customers>().ToList();
Assert.AreEqual(count, customers.Count);
}
public void TestUnion()
{
const int resultRowCount = 5;
// create QueryDesigner with ElementType == Products
var queryDesinger = new QueryDesigner(context, typeof(Products));
//create root node which elementType has the same type in queryDesigner
var root = new JoinNode(typeof(Products));
// create child node Categories with propertyName "Products".
// Because Categories linked with Products by next property:
// public EntitySet<Products> Products
var categoryNode = new JoinNode(typeof(Categories), "Category", "Products");
// add categoryNode to root node
root.AddChildren(categoryNode);
// create filter by Products.ProductName like "%l%"
var productNameCondition = new Condition("ProductName", "l", ConditionOperator.Like);
root.AddConditions(productNameCondition);
// create filter by Categories.Description like "Sweet%"
var categoryNameCondition = new Condition("Description", "Sweet", ConditionOperator.StartsWith, typeof(Categories));
categoryNode.AddConditions(categoryNameCondition);
// create ordering, because we call Skip and Take method.
Ordering order = new Ordering("ProductName");
// make join Products table with Categories filtered by conditions
// and ordered by already created ordering
queryDesinger.Join(root, new OrderingList(order));
QueryDesigner cloneQueryDesigner = (QueryDesigner)queryDesinger.Clone();
queryDesinger.Skip(0).Take(3);
cloneQueryDesigner.Skip(2).Take(4);
queryDesinger.Union(cloneQueryDesigner);
var list = new List<Products>(queryDesinger.Cast<Products>());
Assert.AreEqual(resultRowCount, list.Count);
}
public void TestJoinWithTwoChildrenAndComplicatedFilterAndOrderings()
{
const string productName = "Louisiana";
const string categoryName = "Condiments";
const int resultRowCount = 9;
//create root node
var root = new JoinNode(typeof(Products));
// add first child node Categories with propertyName "Products".
// Because Categories linked with Products by next property:
// public EntitySet<Products> Products
var categoryNode = new JoinNode(typeof(Categories), "Category", "Products");
root.AddChildren(categoryNode);
// add second child node Order_Details. PropertyName not defined
// because Order_Details linked with Products by next property:
// public Products Products - name of property is equal name of type
var orderDetailNode = new JoinNode(typeof(Order_Details), "Order_Detail", "Products");
root.AddChildren(orderDetailNode);
var queryDesinger = new QueryDesigner(context, root);
// create conditions for filtering by ProductName Like "Louisiana%" Or CategoryName == "Condiments"
var productCondition = new Condition("ProductName", productName, ConditionOperator.StartsWith,
typeof(Products));
var categoryCondition = new Condition("CategoryName", categoryName, ConditionOperator.EqualTo,
typeof(Categories));
var orCondition = new OrCondition(productCondition, categoryCondition);
// create condition for filtering by [Orders Details].Discount > 0.15
var discountCondition = new Condition("Discount", 0.15F, ConditionOperator.GreaterThan,
typeof(Order_Details));
var conditionals = new ConditionList(orCondition, discountCondition);
// assign conditions
// queryDesinger.Where(conditionals);
// make Distinct
queryDesinger.Distinct();
// make orderings by ProductName and CategoryName
var productNameOrder = new Ordering("ProductName", SortDirection.Ascending, typeof(Products));
// var categoryNameOrder = new Ordering("CategoryName", SortDirection.Descending, typeof(Categories));
queryDesinger.OrderBy(new OrderingList(productNameOrder/*, categoryNameOrder*/));
IQueryable<Products> distictedProducts = queryDesinger.Cast<Products>();
var list = new List<Products>(distictedProducts);
Assert.AreEqual(resultRowCount, list.Count);
string query =
@" SELECT DISTINCT Products.ProductID, Products.ProductName, Products.SupplierID, Products.CategoryID, Products.QuantityPerUnit, Products.UnitPrice, Products.UnitsInStock, Products.UnitsOnOrder, Products.ReorderLevel, Products.Discontinued
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID
INNER JOIN [Orders Details] ON Products.ProductID = [Orders Details].ProductID
WHERE [Orders Details].Discount > 0.15
AND ((Products.ProductName LIKE N'Louisiana%') OR (Categories.CategoryName = N'Condiments'))";
CheckDataWithExecuteReaderResult(query, resultRowCount, list);
}
public void TestJoinWithOneChildSkipAndTake()
{
const int resultRowCount = 10;
//create root node
var root = new JoinNode(typeof(Products));
// add child node Categories with propertyName "Products".
// Because Categories linked with Products by next property:
// public EntitySet<Products> Products
var categoryNode = new JoinNode(typeof(Categories), "Category", "Products");
root.AddChildren(categoryNode);
var queryDesinger = new QueryDesigner(context, typeof(Products));
OrderingList parameteres = new OrderingList(new Ordering("ProductName", typeof(Products)), new Ordering("CategoryName", SortDirection.Descending, typeof(Categories)));
queryDesinger.Join(root, parameteres);
queryDesinger.Skip(10).Take(10);
var list = new List<Products>(queryDesinger.Cast<Products>());
Assert.AreEqual(resultRowCount, list.Count);
}
public void TestJoinWithOneChildAndFilteredByProductNameAndCategoryNameV2()
{
const string productName = "Louisiana";
const string categoryName = "Condiments";
const int resultRowCount = 2;
//create root node
var root = new JoinNode(typeof(Products));
// add condition for filtering by ProductName Like "Louisiana%"
root.AddConditions(new Condition("ProductName", productName, ConditionOperator.StartsWith));
// add child node Categories with propertyName "Products".
// Because Categories linked with Products by next property:
// public EntitySet<Products> Products
var categoryNode = new JoinNode(typeof(Categories), "Category", "Products");
root.AddChildren(categoryNode);
// add condition for filtering by CategoryName == "Condiments"
categoryNode.AddConditions(new Condition("CategoryName", categoryName));
var queryDesinger = new QueryDesigner(context, root);
var list = new List<Products>(queryDesinger.Cast<Products>());
Assert.AreEqual(resultRowCount, list.Count);
string query =
@"SELECT ProductID FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID
WHERE Products.ProductName like N'Louisiana%' AND Categories.CategoryName = N'Condiments'";
CheckDataWithExecuteReaderResult(query, resultRowCount, list);
}
public void TestJoinWithOneChild()
{
const int resultRowCount = 77;
//create root node
var root = new JoinNode(typeof(Products));
// add child node Categories with propertyName "Products".
// Because Categories linked with Products by next property:
// public EntitySet<Products> Products
root.AddChildren(new JoinNode(typeof(Categories), "Category", "Products"));
var queryDesinger = new QueryDesigner(context, root);
var list = new List<Products>(queryDesinger.Cast<Products>());
// check numbers of entity
Assert.AreEqual(resultRowCount, list.Count);
string query =
@"SELECT ProductID FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID";
CheckDataWithExecuteReaderResult(query, resultRowCount, list);
}
public void TestAggregateConditionCount()
{
const int freight = 10;
const int firstCountValue = 0;
var count = (from customer in context.Customers
where customer.Orders.Count(order => order.Freight >= freight) > firstCountValue
select customer).Count();
QueryDesigner queryDesigner = new QueryDesigner(context, typeof(Customers));
ConditionList conditions = new ConditionList(new Condition("Freight", freight, ConditionOperator.GreaterThanOrEqualTo));
PredicateAggregationCondition predicateAggregationCondition = new CountCondition("Orders", conditions, firstCountValue, ConditionOperator.GreaterThan);
queryDesigner.Where(predicateAggregationCondition);
List<Customers> customers = queryDesigner.Cast<Customers>().ToList();
Assert.AreEqual(count, customers.Count);
const int secondCountValue = 5;
count = (from customer in context.Customers
where customer.Orders.Count(order => order.Freight >= freight) <= secondCountValue
select customer).Count();
queryDesigner = new QueryDesigner(context, typeof(Customers));
conditions = new ConditionList(new Condition("Freight", freight, ConditionOperator.GreaterThanOrEqualTo));
predicateAggregationCondition = new CountCondition("Orders", conditions, secondCountValue, ConditionOperator.LessThanOrEqualTo);
queryDesigner.Where(predicateAggregationCondition);
customers = queryDesigner.Cast<Customers>().ToList();
Assert.AreEqual(count, customers.Count);
}
public void TestJoinWithOneChildByNullableAssociationFileds()
{
const int resultRowCount = 77;
//create root node
var root = new JoinNode(typeof(Category));
// add child node Category with propertyName "Products".
// Because Category linked with Product by next property:
// public EntitySet<Product> Products
root.AddChildren(new JoinNode(typeof(Product), new[] { "CategoryID" }, new[] { "CategoryID" }));
var queryDesinger = new QueryDesigner(context, root);
var list = new List<Category>(queryDesinger.Cast<Category>());
// check numbers of entity
Assert.AreEqual(resultRowCount, list.Count);
string query =
@"SELECT CategoryID FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID";
// CheckDataWithExecuteReaderResult(query, resultRowCount, list);
}
public void TestJoinWithOneChildAndFilteredByCategoryNameV2()
{
const string categoryName = "Condiments";
const int resultRowCount = 12;
//create root node
var root = new JoinNode(typeof (Product));
// add child node Category with propertyName "Products".
// Because Category linked with Product by next property:
// public EntitySet<Product> Products
var categoryNode = new JoinNode(typeof(Category), "Category", "Products");
root.AddChildren(categoryNode);
var queryDesinger = new QueryDesigner(context, root);
// add condition for filtering by CategoryName == "Condiments"
queryDesinger = queryDesinger.Where(new Condition("CategoryName", categoryName, ConditionOperator.EqualTo,
typeof (Category)));
var list = new List<Product>(queryDesinger.Cast<Product>());
Assert.AreEqual(resultRowCount, list.Count);
string query =
@"SELECT ProductID FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID
WHERE Categories.CategoryName = N'Condiments'";
CheckDataWithExecuteReaderResult(query, resultRowCount, list);
}
public void TestExcept()
{
const int resultRowCount = 2;
// create QueryDesigner with ElementType == Product
var queryDesinger = new QueryDesigner(context, typeof(Product));
//create root node which elementType has the same type in queryDesigner
var root = new JoinNode(typeof(Product));
// create child node Category with propertyName "Products".
// Because Category linked with Product by next property:
// public EntitySet<Product> Products
var categoryNode = new JoinNode(typeof(Category), "Category", "Products");
// add categoryNode to root node
root.AddChildren(categoryNode);
// create filter by Product.ProductName like "%l%"
var productNameCondition = new Condition("ProductName", "l", ConditionOperator.Like);
// create filter by Category.Description like "Sweet%"
var categoryNameCondition = new Condition("Description", "Sweet", ConditionOperator.StartsWith, typeof(Category));
// create condition list with already created conditions
var conditionList = new ConditionList(productNameCondition, categoryNameCondition);
// make join Product table with Category filtered by conditions
// and ordered by already created ordering
queryDesinger = queryDesinger.Join(root, conditionList);
QueryDesigner cloneQueryDesigner = (QueryDesigner)queryDesinger.Clone();
queryDesinger = queryDesinger.Skip(0).Take(3);
cloneQueryDesigner = cloneQueryDesigner.Skip(2).Take(4);
queryDesinger = queryDesinger.Except(cloneQueryDesigner);
var list = new List<Product>(queryDesinger.Cast<Product>());
Assert.AreEqual(resultRowCount, list.Count);
}
