private bool LoadByPrimaryKeyDynamic(System.Int32 orderID)
{
OrderQuery query = new OrderQuery();
query.Where(query.OrderID == orderID);
return(this.Load(query));
}
public void AllSubQuery()
{
OrderCollection collection = new OrderCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SQLiteProvider":
Assert.Ignore("Not supported by SQLite.");
break;
default:
// DateAdded for Customers whose Manager = 3
CustomerQuery cq = new CustomerQuery("c");
cq.es.All = true;
cq.Select(cq.DateAdded);
cq.Where(cq.Manager == 3);
// OrderID and CustID where the OrderDate is
// less than all of the dates in the CustomerQuery above.
OrderQuery oq = new OrderQuery("o");
oq.Select(
oq.OrderID,
oq.CustID
);
oq.Where(oq.OrderDate < cq);
Assert.IsTrue(collection.Load(oq));
Assert.AreEqual(8, collection.Count);
break;
}
}
public void AnyNestedBySubQuery()
{
OrderCollection collection = new OrderCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SQLiteProvider":
Assert.Ignore("Not supported by SQLite.");
break;
default:
// Employees whose LastName begins with 'S'.
EmployeeQuery eq = new EmployeeQuery("e");
eq.Select(eq.EmployeeID);
eq.Where(eq.LastName.Like("S%"));
// DateAdded for Customers whose Managers are in the
// EmployeeQuery above.
CustomerQuery cq = new CustomerQuery("c");
cq.es.Any = true;
cq.Select(cq.DateAdded);
cq.Where(cq.Manager.In(eq));
// OrderID and CustID where the OrderDate is
// less than any one of the dates in the CustomerQuery above.
OrderQuery oq = new OrderQuery("o");
oq.Select(
oq.OrderID,
oq.CustID
);
oq.Where(oq.OrderDate < cq);
Assert.IsTrue(collection.Load(oq));
Assert.AreEqual(8, collection.Count);
break;
}
}
public void NestedBySubQuery()
{
OrderCollection collection = new OrderCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
// This is the same as the traditional nested SubQuery
// in the 'Nested' test, but is easier to construct
// and understand.
// The key is to start with the innermost SubQuery,
// and work your way out to the outermost Query.
// Employees whose LastName begins with 'S'.
EmployeeQuery eq = new EmployeeQuery("e");
eq.Select(eq.EmployeeID);
eq.Where(eq.LastName.Like("S%"));
// DateAdded for Customers whose Managers are in the
// EmployeeQuery above.
CustomerQuery cq = new CustomerQuery("c");
cq.Select(cq.DateAdded);
cq.Where(cq.Manager.In(eq));
// OrderID and CustID where the OrderDate is in the
// CustomerQuery above.
OrderQuery oq = new OrderQuery("o");
oq.Select(
oq.OrderID,
oq.CustID
);
oq.Where(oq.OrderDate.In(cq));
Assert.IsTrue(collection.Load(oq));
Assert.AreEqual(2, collection.Count);
}
public void Nested()
{
OrderCollection collection = new OrderCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
OrderQuery oq = new OrderQuery("o");
CustomerQuery cq = new CustomerQuery("c");
EmployeeQuery eq = new EmployeeQuery("e");
// OrderID and CustID for customers who ordered on the same date
// a customer was added, and have a manager whose
// last name starts with 'S'.
oq.Select(
oq.OrderID,
oq.CustID
);
oq.Where(oq.OrderDate
.In(
cq.Select(cq.DateAdded)
.Where(cq.Manager.In(
eq.Select(eq.EmployeeID)
.Where(eq.LastName.Like("S%"))
)
)
)
);
Assert.IsTrue(collection.Load(oq));
Assert.AreEqual(2, collection.Count);
}
public void SubQueryWithGT_LT()
{
CustomerCollection coll = new CustomerCollection();
coll.es.Connection.ConnectionString =
UnitTestBase.GetFktString(coll.es.Connection);
switch (coll.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SqlServerCeProvider":
case "EntitySpaces.SqlServerCe4Provider":
Assert.Ignore("Not supported.");
break;
default:
DateTime fromDate = new DateTime(2005, 1, 1);
DateTime toDate = new DateTime(2005, 8, 31);
DateTime startDate = new DateTime(2000, 1, 1);
DateTime endDate = new DateTime(2000, 12, 31);
CustomerQuery cq = new CustomerQuery("c");
OrderQuery oq = new OrderQuery("o");
OrderQuery oqSub = new OrderQuery("oSub");
oqSub.Select(oqSub.OrderDate.Max());
oqSub.Where(oqSub.CustID == cq.CustomerID &
oqSub.CustSub == cq.CustomerSub);
// These work in SubQuery
oqSub.Where(oqSub.OrderDate >= fromDate);
oqSub.Where(oqSub.OrderDate <= toDate);
// If you comment the above 2 GT/LT lines
// and un-comment the Between line below
// it gets Null Reference exception on Load()
//oqSub.Where(oqSub.OrderDate.Between(fromDate, toDate));
cq.es.Distinct = true;
cq.Select(cq.CustomerID, cq.CustomerSub, cq.DateAdded, oqSub.As("MaxOrderDate"));
cq.InnerJoin(oq).On(cq.CustomerID == oq.CustID &
cq.CustomerSub == oq.CustSub);
// This works in outer query
cq.Where(cq.DateAdded.Between(startDate, endDate));
Assert.IsTrue(coll.Load(cq));
Assert.AreEqual(1, coll.Count);
break;
}
}
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;
}
}
