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