public void TestLeftOuterJoin() { // var res = (from product in context.Products // orderby product.ProductID // select new { product, Order_Details = product.Order_Details.Where(p => p.Quantity > 120), Order_Detail = product.Suppliers }); // var resutl = res.ToList(); //// var res = (from categories in context.Categories //// orderby categories.CategoryID //// select new { categories, Products = categories.Products.Where(p => p.ProductName.Contains("l")).Select(p=>p.Order_Details) }); //// var resutl = res.ToList(); // // foreach (var list1 in resutl) // { // var value = list1.product.Order_Details.FirstOrDefault(); // if (value != null) // { // Console.WriteLine(value.Quantity); // } // } const int quantity = 120; var root = new JoinNode(typeof(Products)); var orderDetailNode = new JoinNode(typeof(Order_Details), "Order_Details", "Product", JoinType.LeftOuterJoin); root.AddChildren(orderDetailNode); // add condition for filtering by CategoryName == "Condiments" orderDetailNode.AddConditions(new Condition("Quantity", quantity, ConditionOperator.GreaterThan)); var supplierNode = new JoinNode(typeof(Suppliers), "Suppliers", "Products", JoinType.LeftOuterJoin); root.AddChildren(supplierNode); var orderNode = new JoinNode(typeof(Orders), "Orders", "Order_Details", JoinType.LeftOuterJoin); orderDetailNode.AddChildren(orderNode); var queryDesinger = new QueryDesigner(context, root); var query = queryDesinger; foreach (Products value in query) { var orderDetails = value.Order_Details.FirstOrDefault(); if (orderDetails != null) { Console.WriteLine(orderDetails.Quantity + " " + orderDetails.Orders.ShipName); } } }
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 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 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 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 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 TestCount() { 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 count = queryDesinger.Count(); // check numbers of entity Assert.AreEqual(resultRowCount, count); }
public void TestFirstOrDefault() { // 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); // make join Products table with Categories filtered by conditions // and ordered by already created ordering queryDesinger.Join(root); object first = queryDesinger.FirstOrDefault(); Assert.IsInstanceOfType(typeof(Products), first); }
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 TestComplicatedLeftOuterJoin() { const int quantity = 120; var root = new JoinNode(typeof(Customers)); var ordersNode = new JoinNode(typeof(Orders), "Orders", "Customers", JoinType.LeftOuterJoin); root.AddChildren(ordersNode); root.AddConditions(new Condition("Address", "Kirchgasse 6")); var orderDetailNode = new JoinNode(typeof(Order_Details), "Order_Details", "Orders", JoinType.LeftOuterJoin); orderDetailNode.AddConditions(new Condition("Quantity", quantity, ConditionOperator.GreaterThan)); ordersNode.AddChildren(orderDetailNode); var productNode = new JoinNode(typeof(Products), "Products", "Order_Details", JoinType.LeftOuterJoin); orderDetailNode.AddChildren(productNode); var employeesNode = new JoinNode(typeof(Employees), "Employees", "Orders", JoinType.LeftOuterJoin); ordersNode.AddChildren(employeesNode); var shippersNode = new JoinNode(typeof(Shippers), "Shippers", "Orders", JoinType.LeftOuterJoin); ordersNode.AddChildren(shippersNode); var queryDesinger = new QueryDesigner(context, root); var query = queryDesinger; foreach (Customers value in query) { var orders = value.Orders.Where(o => o.Order_Details.Count > 0).FirstOrDefault(); if (orders != null) { Console.WriteLine("ShipName: " + orders.ShipName); foreach (var orderDetail in orders.Order_Details) { Console.WriteLine("ProductName: " + orderDetail.Products.ProductName); } } } }
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); }
public void TestAny() { const bool result = true; //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 root.AddChildren(new JoinNode(typeof(Category), "Category", "Products")); var queryDesinger = new QueryDesigner(context, root); var any = queryDesinger.Any(); // check numbers of entity Assert.AreEqual(result, any); }
public void TestLeftOuterJoin() { var query = from orderDetail in context.Order_Details join product in context.Products on orderDetail.Product equals product into gr from grProduct in gr.DefaultIfEmpty() where grProduct.ProductName == "some" select new {orderDetail, pr = grProduct}; query.ToList(); var ordeDetailrNode = new JoinNode(typeof (Order_Detail)); var productNode = new JoinNode(typeof(Product), "Product", "Order_Details", JoinType.LeftOuterJoin); productNode.AddConditions(new Condition("ProductName", "some")); ordeDetailrNode.AddChildren(productNode); var queryDesigner = new QueryDesigner(context, ordeDetailrNode).Cast<Order_Detail>().ToList(); // var dlo = new DataLoadOptions(); // dlo.AssociateWith<Category>(c => c.Products.Where(p => p.ProductName.Contains("l"))); // context.LoadOptions = dlo; // var res1 = (from category in context.Categories // select category); // foreach (var re in res1) // { // Console.WriteLine(re.CategoryID + " " + re.Products.Count); // } // var res = (from product in context.Products // orderby product.ProductID // select new { product, product.Supplier, // Order_Details = product.Order_Details.Where(p => p.Quantity > 120) // .Select(od => new { Order_Detail = od, Order = od.Order }) // }); // // var queryable = res.ToList(); // foreach (var list in queryable) // { // list.product.Order_Details.SetSource(list.Order_Details.Select(od=>od.Order_Detail)); //// foreach (var detail in list.Order_Details) //// { //// detail.Product = list.product; //// } // } // foreach (var list in queryable) // { // var value = list.product.Order_Details.FirstOrDefault(); // if (value != null) // { // Console.WriteLine(value.Quantity + " " + value.Order.ShipCity); // } // } // return; // const int quantity = 120; // // var root = new JoinNode(typeof(Product)); // var orderDetailNode = new JoinNode(typeof(Order_Detail), "Order_Details", "Product", JoinType.LeftOuterJoin); // root.AddChildren(orderDetailNode); // // // add condition for filtering by CategoryName == "Condiments" // orderDetailNode.AddConditions(new Condition("Quantity", quantity, ConditionOperator.GreaterThan)); // // var orderNode = new JoinNode(typeof (Order), "Order", "Order_Details", JoinType.LeftOuterJoin); // orderDetailNode.AddChildren(orderNode); // // var supplierNode = new JoinNode(typeof(Supplier), "Supplier", "Products", JoinType.LeftOuterJoin); // // root.AddChildren(supplierNode); // // var queryDesinger = new QueryDesigner(context, root); // // var result = queryDesinger.Cast<Product>().ToList(); // foreach (Product list1 in result) // { //// Console.WriteLine(list1.product.Supplier.CompanyName); //// if (list1.product.Order_Details.HasLoadedOrAssignedValues) // { // var value = list1.Order_Details.FirstOrDefault(); // if (value != null) // { // Console.WriteLine(value.Quantity + " " + value.Order.ShipCity); // } // } // } // var res = from categories in context.Categories // join product in context.Products on categories equals product.Category into ppp // from p in ppp.DefaultIfEmpty() // where p.ProductName.Contains("l") // select new { categories/*, Products = ppp.DefaultIfEmpty().Where(p => p.ProductName.Contains("l"))*/ };// new {categories, Products = categories.Products.Where(p => p.ProductName.Contains("l"))});} // var resutl = res.ToList(); // // foreach (var list1 in resutl) // { // Console.WriteLine(list1.categories.Products.Count); // } }