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