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 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 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 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 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 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 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);
// }
}
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);
}
}
}
private Expression MakeInnerJoin(Expression outerSourceExpression, Expression innerSourceExpression,
JoinNode childNode, Type resultSelectorType)
{
Type outerType = ExpressionHelper.GetGenericType(outerSourceExpression);
Type innerType = ExpressionHelper.GetGenericType(innerSourceExpression);
ParameterExpression outerParam = ExpressionHelper.CreateOrGetParameterExpression(outerType, outerType.Name, Factory.Store);
ParameterExpression innerParam = ExpressionHelper.CreateOrGetParameterExpression(innerType, innerType.Name, Factory.Store);
LambdaExpression outerKeySelector;
LambdaExpression innerKeySelector;
if (childNode.IsTypeNameEqualAssociatedPropertyName)
{
GetSelectorKeys(outerType, innerType, out outerKeySelector,
out innerKeySelector);
}
else
{
GetSelectorKeys(outerType, innerType, childNode.ParentPropertyName, childNode.AssociatedPropertyName, out outerKeySelector,
out innerKeySelector);
}
LambdaExpression resultSelector =
Expression.Lambda(resultSelectorType == outerType ? outerParam : innerParam, outerParam, innerParam);
if (childNode.Conditions.Count > 0)
{
innerSourceExpression = Factory.CreateConditionExpressionMaker().Make(childNode.Conditions,
innerSourceExpression);
}
Expression joinExpression = CallJoinMethod(outerSourceExpression, innerSourceExpression, outerKeySelector,
innerKeySelector, resultSelector);
return joinExpression;
}
internal Expression MakeLeftJoin(Expression outerSourceExpression, JoinNode rootNode, int startIndex)
{
Type outerType = ExpressionHelper.GetGenericType(outerSourceExpression);
ParameterExpression outerParam = ExpressionHelper.CreateOrGetParameterExpression(outerType, outerType.Name, Factory.Store);
var dynamicProperties = new Dictionary<DynamicProperty, Expression>(rootNode.ChildNodes.Count - startIndex + 1);
var outerProperty = new DynamicProperty(MethodName.MainProperty, outerType);
dynamicProperties.Add(outerProperty, outerParam);
for (int index = startIndex; index < rootNode.ChildNodes.Count; index++)
{
var childNode = (JoinNode) rootNode.ChildNodes[index];
Expression innerMemberEx = Expression.PropertyOrField(outerParam, childNode.ParentPropertyName);
if (childNode.Conditions.Count > 0)
{
innerMemberEx = Factory.CreateConditionExpressionMaker().Make(childNode.Conditions, innerMemberEx);
}
if (childNode.ChildNodes.Count > 0)
{
innerMemberEx = MakeLeftJoin(innerMemberEx, childNode, 0);
}
var innerProperty = new DynamicProperty(childNode.ParentPropertyName, innerMemberEx.Type);
dynamicProperties.Add(innerProperty, innerMemberEx);
}
Type dynamicClassType = DynamicExpression.CreateClass(dynamicProperties.Keys);
int keyIndex = 0;
var bindings = new MemberBinding[dynamicProperties.Keys.Count];
foreach (var key in dynamicProperties.Keys)
{
PropertyInfo dynamicPropertyInfo = dynamicClassType.GetProperty(key.Name);
bindings[keyIndex] = Expression.Bind(dynamicPropertyInfo, dynamicProperties[key]);
keyIndex++;
}
Expression newExpression = Expression.MemberInit(Expression.New(dynamicClassType), bindings);
LambdaExpression resultSelector = Expression.Lambda(newExpression, outerParam);
Expression selectNewQueryableExpression = null;
if (outerSourceExpression.Type.GetInterface(ExpressionHelper.IQueryableType.Name) != null)
{
selectNewQueryableExpression = ExpressionHelper.CallQueryableMethod(MethodName.Select, new[] {outerType, resultSelector.Body.Type}, outerSourceExpression, Expression.Quote(resultSelector));
}
else if (outerSourceExpression.Type.GetInterface(ExpressionHelper.IEnumerableType.Name) != null)
{
selectNewQueryableExpression = ExpressionHelper.CallEnumerableMethod(MethodName.Select, new[] {outerType, resultSelector.Body.Type}, outerSourceExpression, resultSelector);
}
if (selectNewQueryableExpression == null)
{
throw new SpoltyException(String.Format("Can not create left join between parent {0} and children.", rootNode.EntityType.Name));
}
return selectNewQueryableExpression;
}
internal Expression MakeInnerJoin(Expression outerExpression, Expression innerExpression, JoinNode childNode)
{
Checker.CheckArgumentNull(outerExpression, "outerExpression");
Checker.CheckArgumentNull(innerExpression, "innerExpression");
Checker.CheckArgumentNull(childNode, "childNode");
Type resultType = ExpressionHelper.GetGenericType(outerExpression);
if (childNode.ParentFieldsNames.Count > 0 && childNode.CurrentFieldsNames.Count > 0)
{
var leftFields = new string[childNode.ParentFieldsNames.Count];
var rightFields = new string[childNode.CurrentFieldsNames.Count];
childNode.ParentFieldsNames.CopyTo(leftFields, 0);
childNode.CurrentFieldsNames.CopyTo(rightFields, 0);
outerExpression = MakeInnerJoin(outerExpression, innerExpression,
leftFields, rightFields,
childNode.Conditions);
}
else
{
outerExpression = MakeInnerJoin(outerExpression, innerExpression,
childNode, resultType);
}
return outerExpression;
}
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 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 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 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 JoinNodeList(JoinNode parentNode)
: base(parentNode)
{
}
