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 Expression Make(Expression rootExpression, JoinNode rootNode, params IParameterMarker[] parameters)
{
Checker.CheckArgumentNull(rootExpression, "rootExpression");
Checker.CheckArgumentNull(rootNode, "rootNode");
ConditionList conditions;
OrderingList orderings;
ParametersParser.Parse(out conditions, out orderings, parameters);
Expression newExpression = rootExpression;
int index = 0;
foreach (JoinNode childNode in rootNode.ChildNodes)
{
if (childNode.JoinWithParentBy == JoinType.InnerJoin)
{
index++;
if (childNode.ChildNodes.Count > 0)
{
var queryDesinger = new QueryDesigner(Factory.CurrentContext, childNode.EntityType);
queryDesinger = queryDesinger.Where(conditions).OrderBy(orderings);
Expression outerExpression = queryDesinger.Expression;
Expression childrenExpression = Make(outerExpression, childNode, conditions, orderings);
newExpression = MakeInnerJoin(newExpression, childrenExpression, childNode);
}
else
{
var queryDesinger = new QueryDesigner(Factory.CurrentContext, childNode.EntityType);
queryDesinger = queryDesinger.Where(conditions).OrderBy(orderings);
newExpression = MakeInnerJoin(newExpression, queryDesinger.Expression, childNode);
}
}
else
{
break;
}
}
if (index < rootNode.ChildNodes.Count)
{
newExpression = MakeLeftJoin(newExpression, rootNode, index);
}
return newExpression;
}
public void TestJoinWithOneChildAndFilteredByProductNameAndCategoryNameV1()
{
const string productName = "Louisiana";
const string categoryName = "Condiments";
const int resultRowCount = 2;
//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, root);
// add condition for filtering by ProductName Like "Louisiana%"
var Products = new Condition("ProductName", productName, ConditionOperator.StartsWith);
// add condition for filtering by CategoryName == "Condiments"
var categoryCondition = new Condition("CategoryName", categoryName, ConditionOperator.EqualTo,
typeof(Categories));
queryDesinger.Where(new ConditionList(Products, categoryCondition));
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 TestComplicatedJoinWithFilters()
{
const int regionId = 4;
const string territoryDescription = "Orlando";
const int resultRowCount = 23;
//create root node
var root = new JoinNode(typeof(Products));
// 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");
var categoryNode = new JoinNode(typeof(Categories), "Category", "Products", JoinType.LeftOuterJoin);
var supplierNode = new JoinNode(typeof(Suppliers), "Supplier", "Products");
root.AddChildren(orderDetailNode, categoryNode, supplierNode);
var orderNode = new JoinNode(typeof(Orders), "Order", "Order_Details");
orderDetailNode.AddChildren(orderNode);
var employeeNode = new JoinNode(typeof(Employees), "Employee", "Orders");
orderNode.AddChildren(employeeNode);
var territoryNode = new JoinNode(typeof(Territories), "Territory", "Employees");
employeeNode.AddChildren(territoryNode);
var regionNode = new JoinNode(typeof(Region), "Region", "Territories");
territoryNode.AddChildren(regionNode);
var queryDesinger = new QueryDesigner(context, root);
// create conditions for filtering by RegionID = 4 and TerritoryDescription like "Orlando%" and (CategoryID == 4 or CategoryID == 5 or CategoryID == 6)
var regionCondition = new Condition("RegionID", regionId, ConditionOperator.EqualTo, typeof(Region));
var territoryCondition = new Condition("TerritoryDescription", territoryDescription,
ConditionOperator.StartsWith, typeof(Territories));
OrCondition categoryIDsCondition = OrCondition.Create("CategoryID", new object[] { 4, 5, 6 }, ConditionOperator.EqualTo, typeof(Categories));
var conditionals = new ConditionList(regionCondition, territoryCondition, categoryIDsCondition);
// assign conditions
queryDesinger.Where(conditionals);
// make Distinct
IQueryable<Products> distictedProducts = queryDesinger.Distinct().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 Orders
INNER JOIN [Orders Details] ON Orders.OrderID = [Orders Details].OrderID
INNER JOIN Products ON [Orders Details].ProductID = Products.ProductID
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
INNER JOIN EmployeeTerritories ON Employees.EmployeeID = EmployeeTerritories.EmployeeID
INNER JOIN Territories ON EmployeeTerritories.TerritoryID = Territories.TerritoryID
INNER JOIN Region ON Territories.RegionID = Region.RegionID
WHERE (Region.RegionID = 4) AND (Territories.TerritoryDescription like 'Orlando%') AND
(Products.CategoryID IN (4, 5, 6)) ";
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 TestJoinWithTwoChildrenAndComplicatedFilterAndOrderings()
{
const string productName = "Louisiana";
const string categoryName = "Condiments";
const int resultRowCount = 9;
//create root node
var root = new JoinNode(typeof (Product));
// add first child node Category with propertyName "Products".
// Because Category linked with Product by next property:
// public EntitySet<Product> Products
var categoryNode = new JoinNode(typeof (Category));
root.AddChildren(categoryNode);
// add second child node Order_Detail. PropertyName not defined
// because Order_Detail linked with Product by next property:
// public Product Product - name of property is equal name of type
var orderDetailNode = new JoinNode(typeof (Order_Detail));
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 (Product));
var categoryCondition = new Condition("CategoryName", categoryName, ConditionOperator.EqualTo,
typeof (Category));
var orCondition = new OrCondition(productCondition, categoryCondition);
// create condition for filtering by [Order Details].Discount > 0.15
var discountCondition = new Condition("Discount", 0.15F, ConditionOperator.GreaterThan,
typeof (Order_Detail));
var conditionals = new ConditionList(orCondition, discountCondition);
// assign conditions
queryDesinger = queryDesinger.Where(conditionals);
// make Distinct
queryDesinger = queryDesinger.Distinct();
// make orderings by ProductName and CategoryName
var productNameOrder = new Ordering("ProductName", SortDirection.Ascending, typeof (Product));
var categoryNameOrder = new Ordering("CategoryName", SortDirection.Descending, typeof (Category));
queryDesinger = queryDesinger.OrderBy(new OrderingList(productNameOrder, categoryNameOrder));
IQueryable<Product> distictedProducts = queryDesinger.Cast<Product>();
var list = new List<Product>(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 [Order Details] ON Products.ProductID = [Order Details].ProductID
WHERE [Order Details].Discount > 0.15
AND ((Products.ProductName LIKE N'Louisiana%') OR (Categories.CategoryName = N'Condiments'))";
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 TestEnumInCondition()
{
var count = (from order in context.Orders
where order.OrderDate.Value.DayOfWeek == DayOfWeek.Monday
select order).Count();
QueryDesigner queryDesigner = new QueryDesigner(context, typeof(Order));
ConditionList conditions = new ConditionList(new Condition("OrderDate.Value.DayOfWeek", DayOfWeek.Monday));
queryDesigner = queryDesigner.Where(conditions);
var resultCount = queryDesigner.Count();
Assert.AreEqual(count, resultCount);
}
public void TestDataInNullableCondition()
{
var queryable = from order in context.Orders
where order.OrderDate < DateTime.Now
select order;
var count = queryable.Count();
var queryDesigner = new QueryDesigner(context, typeof(Order));
var conditions = new ConditionList(new Condition("OrderDate", DateTime.Now, ConditionOperator.LessThan));
queryDesigner = queryDesigner.Where(conditions);
var resultCount = queryDesigner.Count();
Assert.AreEqual(count, resultCount);
}
public void TestNullInNullableCondition()
{
var queryable = from order in context.Orders
where order.OrderDate == null
select order;
var count = queryable.Count();
var queryDesigner = new QueryDesigner(context, typeof(Order));
var conditions = new ConditionList(new Condition("OrderDate", null));
queryDesigner = queryDesigner.Where(conditions);
var resultCount = queryDesigner.Count();
Assert.AreEqual(count, resultCount);
}
