public void LoadJoined()
{
CustomerQuery cq = new CustomerQuery("c");
EmployeeQuery eq = new EmployeeQuery("e");
EmployeeQuery eq2 = new EmployeeQuery("e2");
OrderQuery oq = new OrderQuery("o");
OrderItemQuery oiq = new OrderItemQuery("oi");
ProductQuery pq = new ProductQuery("p");
cq.Select(
cq.CustomerID,
cq.CustomerSub,
cq.CustomerName,
eq,
eq2.LastName.As("ReportsTo"),
oq.PlacedBy,
oq.OrderDate,
oiq,
pq.ProductName,
pq.Discontinued);
cq.LeftJoin(eq).On(eq.EmployeeID == cq.Manager);
cq.LeftJoin(eq2).On(eq.Supervisor == eq2.EmployeeID);
cq.LeftJoin(oq).On(cq.CustomerID == oq.CustID &&
cq.CustomerSub == oq.CustSub);
cq.LeftJoin(oiq).On(oq.OrderID == oiq.OrderID);
cq.LeftJoin(pq).On(oiq.ProductID == pq.ProductID);
CustomerCollection coll = new CustomerCollection();
coll.es.Connection.Name = "ForeignKeyTest";
Assert.IsTrue(coll.Load(cq));
Assert.AreEqual(69, coll.Count);
}
public void OneModLiteral()
{
OrderItemCollection collection = new OrderItemCollection();
collection.es.Connection.Name = "ForeignKeyTest";
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.MSAccessProvider":
Assert.Ignore("Not supported");
break;
default:
OrderItemQuery oiq = new OrderItemQuery("oiq");
oiq.Select(oiq.OrderID, oiq.ProductID,
(oiq.Quantity % 2).As("SomeInteger"));
oiq.OrderBy(oiq.OrderID, esOrderByDirection.Ascending);
oiq.OrderBy(oiq.ProductID, esOrderByDirection.Ascending);
Assert.IsTrue(collection.Load(oiq));
decimal someInt = Convert.ToInt32(collection[0].GetColumn("SomeInteger"));
Assert.AreEqual(1, someInt);
break;
}
}
public void MixedANDAndORInOn()
{
ProductCollection collection = new ProductCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
int empId = 1;
ProductQuery prd = new ProductQuery("pq");
OrderItemQuery item = new OrderItemQuery("oiq");
OrderQuery ord = new OrderQuery("oq");
CustomerQuery cust = new CustomerQuery("cq");
EmployeeQuery emp = new EmployeeQuery("eq");
prd.Select(prd.ProductID);
prd.InnerJoin(item).On(prd.ProductID == item.ProductID);
prd.InnerJoin(ord).On(item.OrderID == ord.OrderID);
prd.InnerJoin(cust).On(ord.CustID == cust.CustomerID &
(ord.CustSub == cust.CustomerSub |
ord.EmployeeID == cust.StaffAssigned));
prd.InnerJoin(emp).On(cust.Manager == emp.EmployeeID);
prd.Where(emp.EmployeeID == empId);
prd.Where(prd.Discontinued == false);
prd.OrderBy(prd.ProductID.Ascending);
Assert.IsTrue(collection.Load(prd));
Assert.AreEqual(9, collection.Count);
}
public void HavingWithComplexExpression()
{
OrderItemCollection coll = new OrderItemCollection();
coll.es.Connection.Name = "ForeignKeyTest";
OrderItemQuery q = new OrderItemQuery();
q.Select(q.OrderID, (q.Quantity * q.UnitPrice).Sum().As("TotalPrice"));
q.Where(q.Discount.IsNull());
q.GroupBy(q.OrderID);
q.Having((q.Quantity * q.UnitPrice).Sum() > 500);
switch (coll.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SqlServerCeProvider":
case "EntitySpaces.SqlServerCe4Provider":
case "EntitySpaces.VistaDBProvider":
case "EntitySpaces.VistaDB4Provider":
q.OrderBy("<TotalPrice>", esOrderByDirection.Descending);
break;
default:
q.OrderBy((q.Quantity * q.UnitPrice).Sum().Descending);
break;
}
Assert.IsTrue(coll.Load(q), "Load");
Assert.AreEqual(2, coll.Count, "Count");
decimal price = Convert.ToDecimal(coll[0].GetColumn("TotalPrice"));
Assert.AreEqual(1940.0M, price, "GetColumn");
}
public void FromClause()
{
OrderCollection collection = new OrderCollection();
collection.es.Connection.Name = "ForeignKeyTest";
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
//case "EntitySpaces.SqlServerCeProvider":
// Assert.Ignore("Not supported.");
// break;
default:
OrderQuery oq = new OrderQuery("o");
OrderItemQuery oiq = new OrderItemQuery("oi");
// The inner Select contains an aggregate,
// which requires a GroupBy for each non-aggregate in the Select.
// The outer Select includes the aggregate from the inner Select,
// plus columns where no GroupBy was desired.
oq.Select(oq.CustID, oq.OrderDate, oiq.UnitPrice);
oq.From
(
oiq.Select(oiq.OrderID, oiq.UnitPrice.Sum()).GroupBy(oiq.OrderID)
).As("sub");
oq.InnerJoin(oq).On(oq.OrderID == oiq.OrderID);
oq.OrderBy(oq.OrderID.Ascending);
Assert.IsTrue(collection.Load(oq));
Assert.AreEqual(5, collection.Count);
Decimal up = Convert.ToDecimal(collection[0].GetColumn("UnitPrice"));
Assert.AreEqual(5.11m, Math.Round(up, 2));
break;
}
}
public void SimpleJoinOn()
{
OrderCollection collection = new OrderCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.MSAccessProvider":
Assert.Ignore("SubQuery inside an ON clause not Supported");
break;
default:
// Query for the Join
OrderItemQuery oiq = new OrderItemQuery("oi");
// SubQuery of OrderItems with a discount
OrderItemQuery oisq = new OrderItemQuery("ois");
oisq.es.Distinct = true;
oisq.Select(oisq.Discount);
oisq.Where(oisq.Discount > 0);
// Orders with discounted items
OrderQuery oq = new OrderQuery("o");
oq.Select(oq.OrderID, oiq.Discount);
oq.InnerJoin(oiq).On(oq.OrderID == oiq.OrderID &
oiq.Discount.In(oisq));
Assert.IsTrue(collection.Load(oq));
Assert.AreEqual(2, collection.Count);
break;
}
}
public void SelectStatement()
{
OrderCollection collection = new OrderCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
OrderQuery orders = new OrderQuery("o");
OrderItemQuery details = new OrderItemQuery("oi");
// A SubQuery in the Select clause must return a single value.
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SqlServerCeProvider":
case "EntitySpaces.SqlServerCe4Provider":
Assert.Ignore("Not supported.");
break;
default:
orders.Select
(
orders.OrderID,
orders.OrderDate,
details.Select(
details.UnitPrice.Max())
.Where(orders.OrderID == details.OrderID).As("MaxUnitPrice")
);
orders.OrderBy(orders.OrderID.Ascending);
break;
}
Assert.IsTrue(collection.Load(orders));
Assert.AreEqual(8, collection.Count);
Assert.AreEqual(3m, collection[0].GetColumn("MaxUnitPrice"));
}
public void Correlated()
{
OrderItemCollection collection = new OrderItemCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
OrderItemQuery oiq = new OrderItemQuery("oi");
ProductQuery pq = new ProductQuery("p");
// oiq.ProductID in the inner Select is pulled from
// the outer Select, making a correlated SubQuery.
oiq.Select(
oiq.OrderID,
(oiq.Quantity * oiq.UnitPrice).Sum().As("Total")
);
oiq.Where(oiq.ProductID
.In(
pq.Select(pq.ProductID)
.Where(oiq.ProductID == pq.ProductID)
)
);
oiq.GroupBy(oiq.OrderID);
Assert.IsTrue(collection.Load(oiq));
Assert.AreEqual(5, collection.Count);
}
public void SelectAllPlusSubQuery()
{
OrderCollection collection = new OrderCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
OrderQuery orders = new OrderQuery("o");
OrderItemQuery details = new OrderItemQuery("oi");
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SqlServerCeProvider":
case "EntitySpaces.SqlServerCe4Provider":
Assert.Ignore("Not supported.");
break;
default:
orders
.Select(orders, details.Select(details.UnitPrice.Max())
.Where(orders.OrderID == details.OrderID).As("MaxUnitPrice"))
.OrderBy(orders.OrderID.Ascending);
break;
}
Assert.IsTrue(collection.Load(orders));
Assert.AreEqual(8, collection.Count);
Assert.AreEqual(3m, collection[0].GetColumn("MaxUnitPrice"));
string lq = collection.Query.es.LastQuery;
string all = lq.Substring(7, 3);
Assert.AreEqual("o.*", all);
}
public async Task <IActionResult> GetLatest()
{
await Db.Connection.OpenAsync();
var query = new OrderItemQuery(Db);
var result = await query.LatestOrdersAsync();
return(new OkObjectResult(result));
}
public async Task GetSingleByIdAsync_Should_Fail_Gracefully_On_Exception()
{
//Arrange
_sut = new OrderItemQuery(DbContextBuilder.InitEmptyContext(), loggerMock.Object, mapper.CreateMapper());
//Act
var result = await _sut.GetSingleByIdAsync(1);
//Assert
result.IsSuccess.ShouldBeFalse();
loggerMock.Verify(x => x.LogError(It.IsAny <Exception>(), It.IsAny <string>()), Times.Once);
}
public void SingleUnionWithSubSelect()
{
OrderCollection coll = new OrderCollection();
coll.es.Connection.Name = "ForeignKeyTest";
switch (coll.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SqlServerCeProvider":
case "EntitySpaces.SqlServerCe4Provider":
Assert.Ignore("Scalar SubSelects are not supported in SqlCe.");
break;
default:
OrderQuery oq1 = new OrderQuery("o1");
OrderItemQuery oiq1 = new OrderItemQuery("oi1");
oq1.Select
(
oq1.OrderID,
oq1.OrderDate,
oiq1.Select(
oiq1.UnitPrice.Max())
.Where(oq1.OrderID == oiq1.OrderID).As("MaxUnitPrice")
);
oq1.Where(oq1.OrderDate.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31")));
OrderQuery oq2 = new OrderQuery("o2");
OrderItemQuery oiq2 = new OrderItemQuery("oi2");
oq2.Select
(
oq2.OrderID,
oq2.OrderDate,
oiq2.Select(
oiq2.UnitPrice.Max())
.Where(oq2.OrderID == oiq2.OrderID).As("MaxUnitPrice")
);
oq2.Where(oq2.OrderDate.Between(Convert.ToDateTime("2004-01-01"), Convert.ToDateTime("2004-12-31")));
oq1.Union(oq2);
oq1.OrderBy(oq1.OrderID.Ascending);
//string lq = cq1.Parse();
Assert.IsTrue(coll.Load(oq1));
Assert.AreEqual(6, coll.Count);
Assert.AreEqual(3m, coll[0].GetColumn("MaxUnitPrice"));
break;
}
}
public async Task GetAllAsync_Should_Return_Valid_List()
{
//Arrange
dbContextMock = DbContextBuilder.InitContextWithInMemoryDbSupport();
_sut = new OrderItemQuery(dbContextMock, loggerMock.Object, mapper.CreateMapper());
//Act
var result = await _sut.GetAllAsync();
//Assert
result.IsSuccess.ShouldBeTrue();
result.Result.ShouldNotBeNull();
result.Result.Any().ShouldBeTrue();
result.Result.FirstOrDefault().Id.ShouldBe(1);
result.ErrorMessage.ShouldBeNull();
}
public void InnerAlias()
{
OrderCollection collection = new OrderCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.MSAccessProvider":
Assert.Ignore("Not supported");
break;
case "EntitySpaces.VistaDBProvider":
case "EntitySpaces.VistaDB4Provider":
OrderQuery oq = new OrderQuery("o");
OrderItemQuery odq = new OrderItemQuery("od");
oq.Select(oq.OrderID, odq.OrderID);
oq.InnerJoin(odq).On(oq.OrderID == odq.OrderID);
oq.OrderBy(oq.OrderID.Ascending);
Assert.IsTrue(collection.Load(oq), "Load");
string lq = collection.Query.es.LastQuery;
Assert.AreEqual(1, collection[0].OrderID.Value, "Property");
Assert.AreEqual(1, Convert.ToInt32(collection[0].GetColumn("OrderID_1")), "Virtual");
break;
default:
oq = new OrderQuery("o");
odq = new OrderItemQuery("od");
oq.Select(oq.OrderID, odq.OrderID);
oq.InnerJoin(odq).On(oq.OrderID == odq.OrderID);
oq.OrderBy(oq.OrderID.Ascending);
Assert.IsTrue(collection.Load(oq), "Load");
lq = collection.Query.es.LastQuery;
Assert.AreEqual(1, collection[0].OrderID.Value, "Property");
Assert.AreEqual(1, Convert.ToInt32(collection[0].GetColumn("OrderID1")), "Virtual");
break;
}
}
public void HavingWithSimpleExpression()
{
OrderItemCollection coll = new OrderItemCollection();
coll.es.Connection.Name = "ForeignKeyTest";
OrderItemQuery q = new OrderItemQuery();
//q.es2.Connection.Name = "ForeignKeyTest";
q.Select(q.OrderID, q.Quantity.Sum().As("TotalQty"));
q.Where(q.Discount.IsNull());
q.GroupBy(q.OrderID);
switch (coll.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SqlServerCeProvider":
case "EntitySpaces.SqlServerCe4Provider":
case "EntitySpaces.VistaDBProvider":
case "EntitySpaces.VistaDB4Provider":
q.Having(q.Quantity.Sum() > 100);
q.OrderBy("<TotalQty>", esOrderByDirection.Descending);
break;
case "EntitySpaces.SQLiteProvider":
q.Having((q.Quantity * 1).Sum() > 100);
q.OrderBy(q.Quantity.Sum().Descending);
break;
default:
q.Having(q.Quantity.Sum() > 100);
q.OrderBy(q.Quantity.Sum().Descending);
break;
}
Assert.IsTrue(coll.Load(q), "Load");
Assert.AreEqual(3, coll.Count, "Count");
int qty = Convert.ToInt32(coll[0].GetColumn("TotalQty"));
Assert.AreEqual(240, qty, "GetColumn");
}
public void OneWhereSubtract()
{
OrderItemCollection collection = new OrderItemCollection();
collection.es.Connection.Name = "ForeignKeyTest";
OrderItemQuery oiq = new OrderItemQuery("oiq");
oiq.Select(oiq.OrderID, oiq.ProductID,
(oiq.UnitPrice - oiq.Discount).As("SomeDecimal"));
oiq.Where(oiq.UnitPrice - oiq.Discount < .2);
oiq.OrderBy(oiq.OrderID, esOrderByDirection.Ascending);
oiq.OrderBy(oiq.ProductID, esOrderByDirection.Ascending);
Assert.IsTrue(collection.Load(oiq));
decimal someDec = Convert.ToDecimal(collection[0].GetColumn("SomeDecimal"));
Assert.AreEqual(Convert.ToDecimal("0.10"), Math.Round(someDec, 2));
}
public void InnerSelectAllSecondaryQuery()
{
OrderCollection collection = new OrderCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
OrderQuery oq = new OrderQuery("o");
OrderItemQuery oiq = new OrderItemQuery("oi");
oq.Select(oiq);
oq.InnerJoin(oiq).On(oq.OrderID == oiq.OrderID);
Assert.IsTrue(collection.Load(oq));
Assert.AreEqual(15, collection.Count);
string lq = collection.Query.es.LastQuery;
string all = lq.Substring(7, 4);
Assert.AreEqual("oi.*", all);
}
public void FromClauseWithAlias()
{
OrderCollection collection = new OrderCollection();
collection.es.Connection.Name = "ForeignKeyTest";
OrderQuery oq = new OrderQuery("o");
OrderItemQuery oiq = new OrderItemQuery("oi");
// Calculate total price per order
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
//case "EntitySpaces.SqlServerCeProvider":
// Assert.Ignore("Not supported.");
// break;
case "EntitySpaces.NpgsqlProvider":
case "EntitySpaces.Npgsql2Provider":
case "EntitySpaces.OracleClientProvider":
oq.Select(oq.CustID, oq.OrderDate, "<sub.\"OrderTotal\">");
break;
default:
oq.Select(oq.CustID, oq.OrderDate, "<sub.OrderTotal>");
break;
}
oq.From
(
oiq.Select(oiq.OrderID,
(oiq.UnitPrice * oiq.Quantity).Sum().As("OrderTotal"))
.GroupBy(oiq.OrderID)
).As("sub");
oq.InnerJoin(oq).On(oq.OrderID == oiq.OrderID);
oq.OrderBy(oq.OrderID.Ascending);
Assert.IsTrue(collection.Load(oq));
Assert.AreEqual(5, collection.Count);
Assert.AreEqual(13.11m, collection[0].GetColumn("OrderTotal"));
}
public void SimpleSyntaxCheck()
{
OrderItemQuery oq = new OrderItemQuery();
oq.es2.Connection.Name = "ForeignKeyTest";
oq.Select
(
oq.UnitPrice
.Case()
.When(oq.Quantity < 50).Then(oq.UnitPrice)
.When(oq.Quantity >= 50 && oq.Quantity < 70).Then(oq.UnitPrice * .90)
.When(oq.Quantity >= 70 && oq.Quantity < 99).Then(oq.UnitPrice * .80)
.Else(oq.UnitPrice * .70)
.End()
);
OrderItemCollection coll = new OrderItemCollection();
coll.es.Connection.Name = "ForeignKeyTest";
coll.Load(oq);
}
public void JoinWithArithmeticExpressionOrderByCalulatedColumn()
{
CustomerCollection collection = new CustomerCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
string orderAlias = "";
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.MSAccessProvider":
orderAlias = "<'TotalSales'>";
break;
default:
orderAlias = "TotalSales";
break;
}
// Notice I create a calulated columns based on the TotalSales,
// then Order by it descending
CustomerQuery cust = new CustomerQuery("c");
OrderQuery order = new OrderQuery("o");
OrderItemQuery item = new OrderItemQuery("oi");
cust
.Select(cust.CustomerName, (item.Quantity * item.UnitPrice).Sum().As("TotalSales"))
.InnerJoin(order)
.On(order.CustID == cust.CustomerID)
.InnerJoin(item)
.On(item.OrderID == order.OrderID)
.GroupBy(cust.CustomerName)
.OrderBy(orderAlias, esOrderByDirection.Descending);
Assert.IsTrue(collection.Load(cust));
Assert.AreEqual(6, collection.Count);
}
public void FromClauseUsingInstance()
{
OrderCollection collection = new OrderCollection();
collection.es.Connection.Name = "ForeignKeyTest";
// Select an arbitrary number of rows from
// any starting row.
int startRow = 4;
int numberOfRows = 7;
// OrderItem SubQuery 1
// Get all rows through start + number, ascending
OrderItemQuery oisq = new OrderItemQuery("ois");
oisq.es.Top = startRow + numberOfRows - 1;
oisq.Select(oisq.OrderID, oisq.ProductID, oisq.Quantity);
oisq.OrderBy(oisq.OrderID.Ascending,
oisq.ProductID.Ascending);
// OrderItem SubQuery 2
// Get just the number of rows, descending
OrderItemQuery oisq2 = new OrderItemQuery("ois2");
oisq2.es.Top = numberOfRows;
oisq2.From(oisq).As("sub1");
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
//case "EntitySpaces.SqlServerCeProvider":
// Assert.Ignore("Not supported.");
// break;
case "EntitySpaces.NpgsqlProvider":
case "EntitySpaces.Npgsql2Provider":
oisq2.OrderBy("<sub1.\"OrderID\">", esOrderByDirection.Descending);
oisq2.OrderBy("<sub1.\"ProductID\">", esOrderByDirection.Descending);
break;
case "EntitySpaces.OracleClientProvider":
Assert.Ignore("Not supported.");
break;
default:
oisq2.OrderBy("<sub1.OrderID>", esOrderByDirection.Descending);
oisq2.OrderBy("<sub1.ProductID>", esOrderByDirection.Descending);
break;
}
// Put it back in ascending order
OrderQuery oq = new OrderQuery("o");
oq.From(oisq2).As("sub2");
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.NpgsqlProvider":
case "EntitySpaces.Npgsql2Provider":
oq.OrderBy("<sub2.\"OrderID\">", esOrderByDirection.Ascending);
oq.OrderBy("<sub2.\"ProductID\">", esOrderByDirection.Ascending);
break;
default:
oq.OrderBy("<sub2.OrderID>", esOrderByDirection.Ascending);
oq.OrderBy("<sub2.ProductID>", esOrderByDirection.Ascending);
break;
}
Assert.IsTrue(collection.Load(oq));
Assert.AreEqual(7, collection.Count);
Assert.AreEqual(10, collection[0].GetColumn("Quantity"));
}
public void NestedZeroToManyCollection()
{
//The main Employee query
EmployeeCollection coll = new EmployeeCollection();
coll.es.Connection.Name = "ForeignKeyTest";
coll.Query.Where(coll.Query.EmployeeID < 4);
// Prefetch Employees Customers by Manager
CustomerQuery cq1 = coll.Query.Prefetch <CustomerQuery>(
Employee.Prefetch_CustomerCollectionByManager);
EmployeeQuery eq1 = cq1.GetQuery <EmployeeQuery>();
cq1.Where(eq1.EmployeeID < 4);
// Prefetch Employees Customers Orders (composite FK)
OrderQuery oq1 = coll.Query.Prefetch <OrderQuery>(
Employee.Prefetch_CustomerCollectionByManager,
Customer.Prefetch_OrderCollectionByCustID);
EmployeeQuery eq2 = oq1.GetQuery <EmployeeQuery>();
oq1.Where(eq2.EmployeeID < 4);
// Prefetch Employees Customers Orders OrderItems
OrderItemQuery oiq1 = coll.Query.Prefetch <OrderItemQuery>(
Employee.Prefetch_CustomerCollectionByManager,
Customer.Prefetch_OrderCollectionByCustID,
Order.Prefetch_OrderItemCollectionByOrderID);
EmployeeQuery eq3 = oiq1.GetQuery <EmployeeQuery>();
oiq1.Where(eq3.EmployeeID < 4);
// Prefetch Employees Customers by StaffAssigned
CustomerQuery cq2 = coll.Query.Prefetch <CustomerQuery>(
Employee.Prefetch_CustomerCollectionByStaffAssigned);
EmployeeQuery eq4 = cq2.GetQuery <EmployeeQuery>();
cq2.Where(eq4.EmployeeID < 4);
coll.Query.Load();
foreach (Employee emp in coll)
{
switch (emp.EmployeeID.Value)
{
case 1:
Assert.AreEqual(1, emp.EmployeeID.Value);
Assert.AreEqual(35, emp.CustomerCollectionByManager.Count);
Assert.AreEqual(2, emp.CustomerCollectionByStaffAssigned.Count);
Assert.AreEqual(0, emp.EmployeeCollectionBySupervisor.Count, "These 2 are not Prefetched");
foreach (Customer c in emp.CustomerCollectionByManager)
{
if (c.CustomerID == "01001" && c.CustomerSub == "001")
{
Assert.AreEqual(3, c.OrderCollectionByCustID.Count);
foreach (Order o in c.OrderCollectionByCustID)
{
if (o.OrderID == 1)
{
Assert.AreEqual(3, o.OrderItemCollectionByOrderID.Count);
}
}
}
}
break;
case 2:
Assert.AreEqual(2, emp.EmployeeID.Value);
Assert.AreEqual(12, emp.CustomerCollectionByManager.Count);
Assert.AreEqual(5, emp.CustomerCollectionByStaffAssigned.Count);
break;
case 3:
Assert.AreEqual(3, emp.EmployeeID.Value);
Assert.AreEqual(6, emp.CustomerCollectionByManager.Count);
Assert.AreEqual(1, emp.CustomerCollectionByStaffAssigned.Count);
break;
default:
Assert.Fail("Only employees 1, 2, and 3 should be loaded.");
break;
}
}
}
public void NestedZeroToManyEntity2()
{
//The main Employee
Employee emp = new Employee();
emp.es.Connection.Name = "ForeignKeyTest";
emp.Query.Where(emp.Query.EmployeeID == 1);
// Prefetch Employee Customers by Manager
CustomerQuery cq1 = emp.Query.Prefetch <CustomerQuery>(
Employee.Prefetch_CustomerCollectionByManager);
EmployeeQuery eq1 = cq1.GetQuery <EmployeeQuery>();
cq1.Where(eq1.EmployeeID == 1);
// Prefetch Employee Customers Orders (composite FK)
OrderQuery oq1 = emp.Query.Prefetch <OrderQuery>(
Employee.Prefetch_CustomerCollectionByManager,
Customer.Prefetch_OrderCollectionByCustID);
EmployeeQuery eq2 = oq1.GetQuery <EmployeeQuery>();
oq1.Where(eq2.EmployeeID == 1);
// Prefetch Employee Customers Orders OrderItems
OrderItemQuery oiq1 = emp.Query.Prefetch <OrderItemQuery>(
Employee.Prefetch_CustomerCollectionByManager,
Customer.Prefetch_OrderCollectionByCustID,
Order.Prefetch_OrderItemCollectionByOrderID);
EmployeeQuery eq3 = oiq1.GetQuery <EmployeeQuery>();
oiq1.Where(eq3.EmployeeID == 1);
// Prefetch Employee Customers by StaffAssigned
CustomerQuery cq2 = emp.Query.Prefetch <CustomerQuery>(
Employee.Prefetch_CustomerCollectionByStaffAssigned);
EmployeeQuery eq4 = cq2.GetQuery <EmployeeQuery>();
cq2.Where(eq4.EmployeeID == 1);
emp.Query.Load();
Assert.AreEqual(1, emp.EmployeeID.Value);
Assert.AreEqual(35, emp.CustomerCollectionByManager.Count);
Assert.AreEqual(2, emp.CustomerCollectionByStaffAssigned.Count);
Assert.AreEqual(0, emp.EmployeeCollectionBySupervisor.Count, "These 2 are not Prefetched");
foreach (Customer c in emp.CustomerCollectionByManager)
{
if (c.CustomerID == "01001" && c.CustomerSub == "001")
{
Assert.AreEqual(3, c.OrderCollectionByCustID.Count);
foreach (Order o in c.OrderCollectionByCustID)
{
if (o.OrderID == 1)
{
Assert.AreEqual(3, o.OrderItemCollectionByOrderID.Count);
}
}
}
}
}
public void NestedZeroToMany()
{
// The main Employee query
EmployeeQuery eq1 = new EmployeeQuery("e");
eq1.Where(eq1.EmployeeID < 4);
eq1.OrderBy(eq1.EmployeeID.Ascending);
// The Order Collection
OrderQuery oq1 = eq1.Prefetch <OrderQuery>(Employee.Prefetch_OrderCollectionByEmployeeID);
EmployeeQuery eq2 = oq1.GetQuery <EmployeeQuery>();
oq1.Where(eq2.EmployeeID < 4 && oq1.PlacedBy < 3);
// Pre-test the Order query
OrderCollection oColl = new OrderCollection();
oColl.es.Connection.Name = "ForeignKeyTest";
oColl.Load(oq1);
Assert.AreEqual(5, oColl.Count, "Order pre-test");
// The OrderItem Collection
OrderItemQuery oiq1 = eq1.Prefetch <OrderItemQuery>(Employee.Prefetch_OrderCollectionByEmployeeID, Order.Prefetch_OrderItemCollectionByOrderID);
EmployeeQuery eq3 = oiq1.GetQuery <EmployeeQuery>();
OrderQuery oq2 = oiq1.GetQuery <OrderQuery>();
oiq1.Where(eq3.EmployeeID < 4 && oq2.PlacedBy < 3 && oiq1.Quantity < 100);
// Pre-test the OrderItem query
OrderItemCollection oiColl = new OrderItemCollection();
oiColl.es.Connection.Name = "ForeignKeyTest";
oiColl.Load(oiq1);
Assert.AreEqual(4, oiColl.Count, "OrderItem pre-test");
// Will Prefetch the Order and OrderItems queries
EmployeeCollection coll = new EmployeeCollection();
coll.es.Connection.Name = "ForeignKeyTest";
//coll.es.IsLazyLoadDisabled = true;
coll.Load(eq1);
foreach (Employee emp in coll)
{
emp.es.IsLazyLoadDisabled = true;
switch (emp.EmployeeID.Value)
{
case 1:
Assert.AreEqual(1, emp.EmployeeID.Value);
Assert.AreEqual(0, emp.OrderCollectionByEmployeeID.Count);
break;
case 2:
Assert.AreEqual(2, emp.EmployeeID.Value);
Assert.AreEqual(2, emp.OrderCollectionByEmployeeID.Count);
foreach (Order o in emp.OrderCollectionByEmployeeID)
{
Assert.Less(0, o.OrderItemCollectionByOrderID.Count);
}
break;
case 3:
Assert.AreEqual(3, emp.EmployeeID.Value);
Assert.AreEqual(3, emp.OrderCollectionByEmployeeID.Count);
foreach (Order o in emp.OrderCollectionByEmployeeID)
{
Assert.AreEqual(0, o.OrderItemCollectionByOrderID.Count);
}
break;
default:
Assert.Fail("Only employees 1, 2, and 3 should be loaded.");
break;
}
}
}
