public void SaveComposite()
{
using (esTransactionScope scope = new esTransactionScope())
{
OrderItemCollection coll = new OrderItemCollection();
coll.es.Connection.Name = "ForeignKeyTest";
OrderItem entity = coll.AddNew();
entity.OrderID = 9;
entity.ProductID = 1;
entity.UnitPrice = 1000;
entity.Quantity = 1000;
entity = coll.AddNew();
entity.OrderID = 9;
entity.ProductID = 2;
entity.UnitPrice = 1000;
entity.Quantity = 1000;
entity = coll.AddNew();
entity.OrderID = 9;
entity.ProductID = 3;
entity.UnitPrice = 1000;
entity.Quantity = 1000;
coll.Save();
coll = new OrderItemCollection();
coll.es.Connection.Name = "ForeignKeyTest";
coll.Query.Where(coll.Query.OrderID == 9);
Assert.IsTrue(coll.Query.Load());
Assert.AreEqual(3, coll.Count);
// Clean up
coll.MarkAllAsDeleted();
coll.Save();
coll = new OrderItemCollection();
coll.es.Connection.Name = "ForeignKeyTest";
coll.Query.Where(coll.Query.OrderID == 9);
Assert.IsFalse(coll.Query.Load());
coll = new OrderItemCollection();
coll.es.Connection.Name = "ForeignKeyTest";
Assert.IsTrue(coll.LoadAll());
Assert.AreEqual(15, coll.Count);
}
}
public static void RefreshForeignKeyTest(string connectionName)
{
OrderItemCollection oiColl = new OrderItemCollection();
oiColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
oiColl.es.Connection.Name = connectionName;
}
oiColl.Query.Where(oiColl.Query.OrderID > 11 |
oiColl.Query.ProductID > 9);
oiColl.Query.Load();
oiColl.MarkAllAsDeleted();
oiColl.Save();
OrderCollection oColl = new OrderCollection();
oColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
oColl.es.Connection.Name = connectionName;
}
oColl.Query.Where(oColl.Query.OrderID > 11);
oColl.Query.Load();
oColl.MarkAllAsDeleted();
oColl.Save();
EmployeeTerritoryCollection etColl = new EmployeeTerritoryCollection();
etColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
etColl.es.Connection.Name = connectionName;
}
etColl.Query.Where(etColl.Query.EmpID > 4 |
etColl.Query.TerrID > 4);
etColl.Query.Load();
etColl.MarkAllAsDeleted();
etColl.Save();
CustomerCollection cColl = new CustomerCollection();
cColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
cColl.es.Connection.Name = connectionName;
}
cColl.Query.Where(cColl.Query.CustomerID > "99999" &
cColl.Query.CustomerSub > "001");
cColl.Query.Load();
cColl.MarkAllAsDeleted();
cColl.Save();
TerritoryExCollection tExColl = new TerritoryExCollection();
tExColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
tExColl.es.Connection.Name = connectionName;
}
tExColl.Query.Where(tExColl.Query.TerritoryID > 1);
tExColl.Query.Load();
tExColl.MarkAllAsDeleted();
tExColl.Save();
TerritoryCollection tColl = new TerritoryCollection();
tColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
tColl.es.Connection.Name = connectionName;
}
tColl.Query.Where(tColl.Query.TerritoryID > 5);
tColl.Query.Load();
tColl.MarkAllAsDeleted();
tColl.Save();
ReferredEmployeeCollection reColl = new ReferredEmployeeCollection();
reColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
reColl.es.Connection.Name = connectionName;
}
reColl.Query.Where(reColl.Query.EmployeeID > 4 |
reColl.Query.ReferredID > 5);
reColl.Query.Load();
reColl.MarkAllAsDeleted();
reColl.Save();
ProductCollection pColl = new ProductCollection();
pColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
pColl.es.Connection.Name = connectionName;
}
pColl.Query.Where(pColl.Query.ProductID > 10);
pColl.Query.Load();
pColl.MarkAllAsDeleted();
pColl.Save();
GroupCollection gColl = new GroupCollection();
gColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
gColl.es.Connection.Name = connectionName;
}
gColl.Query.Where(gColl.Query.Id > "15001");
gColl.Query.Load();
gColl.MarkAllAsDeleted();
gColl.Save();
EmployeeCollection eColl = new EmployeeCollection();
eColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
eColl.es.Connection.Name = connectionName;
}
eColl.Query.Where(eColl.Query.EmployeeID > 5);
eColl.Query.Load();
eColl.MarkAllAsDeleted();
eColl.Save();
CustomerGroupCollection cgColl = new CustomerGroupCollection();
cgColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
cgColl.es.Connection.Name = connectionName;
}
cgColl.Query.Where(cgColl.Query.GroupID > "99999" |
cgColl.Query.GroupID < "00001");
cgColl.Query.Load();
cgColl.MarkAllAsDeleted();
cgColl.Save();
}
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 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 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 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 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 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;
}
}
}
public void TestCollectionDeleteAll()
{
int prdId = -1;
Product prd = new Product();
prd.es.Connection.Name = "ForeignKeyTest";
try
{
using (esTransactionScope scope = new esTransactionScope())
{
prd.ProductName = "UnitTest";
prd.UnitPrice = 1;
prd.Discontinued = false;
for (int i = 0; i < 3; i++)
{
OrderItem oi = prd.OrderItemCollectionByProductID.AddNew();
oi.OrderID = i + 1;
oi.UnitPrice = prd.UnitPrice;
oi.Quantity = Convert.ToInt16(i);
oi.Discount = 0;
}
prd.Save();
prdId = prd.ProductID.Value;
// Test
ProductCollection collection = new ProductCollection();
collection.es.Connection.Name = "ForeignKeyTest";
Assert.IsTrue(collection.LoadAll());
Product entity = collection.FindByPrimaryKey(prdId);
Assert.AreEqual(3, entity.OrderItemCollectionByProductID.Count);
entity.OrderItemCollectionByProductID.MarkAllAsDeleted();
entity.MarkAsDeleted();
collection.Save();
prd = new Product();
prd.es.Connection.Name = "ForeignKeyTest";
Assert.IsFalse(prd.LoadByPrimaryKey(prdId));
OrderItemCollection oic = new OrderItemCollection();
oic.es.Connection.Name = "ForeignKeyTest";
oic.Query.Where(oic.Query.ProductID == prdId);
Assert.IsFalse(oic.Query.Load());
}
}
finally
{
prd = new Product();
prd.es.Connection.Name = "ForeignKeyTest";
if (prd.LoadByPrimaryKey(prdId))
{
prd.OrderItemCollectionByProductID.MarkAllAsDeleted();
prd.MarkAsDeleted();
prd.Save();
}
}
}
