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); }