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