private bool LoadByPrimaryKeyDynamic(System.String customerID, System.String customerSub)
{
CustomerQuery query = new CustomerQuery();
query.Where(query.CustomerID == customerID, query.CustomerSub == customerSub);
return(this.Load(query));
}
public void SingleUnion()
{
CustomerCollection coll = new CustomerCollection();
coll.es.Connection.Name = "ForeignKeyTest";
CustomerQuery cq1 = new CustomerQuery("c1");
cq1.SelectAllExcept(cq1.Notes);
cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31")));
CustomerQuery cq2 = new CustomerQuery("c2");
cq2.SelectAllExcept(cq2.Notes);
cq2.Where(cq2.DateAdded.Between(Convert.ToDateTime("2006-01-01"), Convert.ToDateTime("2006-12-31")));
// Combine 2005 and 2006 in one result set
cq1.Union(cq2);
//string lq = cq1.Parse();
Assert.IsTrue(coll.Load(cq1));
Assert.AreEqual(49, coll.Count);
}
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 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 SingleIntersectWithNoOverlap()
{
CustomerCollection coll = new CustomerCollection();
coll.es.Connection.Name = "ForeignKeyTest";
CustomerQuery cq1 = new CustomerQuery("c1");
cq1.SelectAllExcept(cq1.Notes);
cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31")));
CustomerQuery cq2 = new CustomerQuery("c2");
cq2.SelectAllExcept(cq2.Notes);
cq2.Where(cq2.DateAdded.Between(Convert.ToDateTime("2006-01-01"), Convert.ToDateTime("2006-12-31")));
// There is no intersection for 2005 and 2006
cq1.Intersect(cq2);
Assert.IsFalse(coll.Load(cq1));
Assert.AreEqual(0, coll.Count);
}
public void SingleIntersect()
{
CustomerCollection coll = new CustomerCollection();
coll.es.Connection.Name = "ForeignKeyTest";
CustomerQuery cq1 = new CustomerQuery("c1");
cq1.SelectAllExcept(cq1.Notes);
cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31")));
CustomerQuery cq2 = new CustomerQuery("c2");
cq2.SelectAllExcept(cq2.Notes);
cq2.Where(cq2.DateAdded.Between(Convert.ToDateTime("2005-12-31"), Convert.ToDateTime("2006-12-31")));
// Only the 2 rows for 2005-12-31
cq1.Intersect(cq2);
Assert.IsTrue(coll.Load(cq1));
Assert.AreEqual(2, coll.Count);
}
public void SingleExceptWithNoOverlap()
{
CustomerCollection coll = new CustomerCollection();
coll.es.Connection.Name = "ForeignKeyTest";
CustomerQuery cq1 = new CustomerQuery("c1");
cq1.SelectAllExcept(cq1.Notes);
cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2006-12-31")));
CustomerQuery cq2 = new CustomerQuery("c2");
cq2.SelectAllExcept(cq2.Notes);
cq2.Where(cq2.DateAdded == Convert.ToDateTime("1900-12-31"));
// All 2005 and 2006
cq1.Except(cq2);
Assert.IsTrue(coll.Load(cq1));
Assert.AreEqual(49, coll.Count);
}
public void SingleUnionAllWithOverlap()
{
CustomerCollection coll = new CustomerCollection();
coll.es.Connection.Name = "ForeignKeyTest";
CustomerQuery cq1 = new CustomerQuery("c1");
cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31")));
CustomerQuery cq2 = new CustomerQuery("c2");
cq2.Where(cq2.DateAdded.Between(Convert.ToDateTime("2005-12-31"), Convert.ToDateTime("2006-12-31")));
// Combine 2005 and 2006 in one result set with the 2 duplicate rows retained
cq1.UnionAll(cq2);
Assert.IsTrue(coll.Load(cq1));
Assert.AreEqual(51, coll.Count);
}
public void SingleUnionWithJoin()
{
CustomerCollection coll = new CustomerCollection();
coll.es.Connection.Name = "ForeignKeyTest";
CustomerQuery cq1 = new CustomerQuery("c1");
EmployeeQuery eq1 = new EmployeeQuery("e1");
cq1.Select(cq1.CustomerID, cq1.CustomerSub, cq1.CustomerName, eq1.LastName);
cq1.InnerJoin(eq1).On(cq1.Manager == eq1.EmployeeID);
cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31")));
CustomerQuery cq2 = new CustomerQuery("c2");
EmployeeQuery eq2 = new EmployeeQuery("e2");
cq2.Select(cq2.CustomerID, cq2.CustomerSub, cq2.CustomerName, eq2.LastName);
cq2.InnerJoin(eq2).On(cq2.Manager == eq2.EmployeeID);
cq2.Where(cq2.DateAdded.Between(Convert.ToDateTime("2006-01-01"), Convert.ToDateTime("2006-12-31")));
cq1.Union(cq2);
cq1.OrderBy(cq1.CustomerID.Ascending, cq1.CustomerSub.Ascending);
//string lq = cq1.Parse();
Assert.IsTrue(coll.Load(cq1));
Assert.AreEqual(49, coll.Count);
Assert.AreEqual("Smith", coll[0].GetColumn("LastName"));
}
public void RightWithFromSubQuery()
{
CustomerCollection coll = new CustomerCollection();
coll.es.Connection.Name = "ForeignKeyTest";
switch (coll.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SQLiteProvider":
Assert.Ignore("RIGHT JOIN not supported.");
break;
default:
CustomerQuery cq1 = new CustomerQuery("c1");
cq1.es.Top = 5;
cq1.Select(cq1.CustomerID, cq1.CustomerSub);
cq1.Where(cq1.Active == true);
cq1.OrderBy(cq1.CustomerID.Ascending);
CustomerQuery cq2 = new CustomerQuery("c2");
CustomerQuery cq3 = new CustomerQuery("c3");
cq3.Select(cq2);
cq3.From(cq1).As("cSub");
cq3.RightJoin(cq2).On(cq1.CustomerID == cq2.CustomerID &&
cq1.CustomerSub == cq2.CustomerSub);
cq3.Where(cq2.Active == true && cq1.CustomerID.IsNull());
cq3.OrderBy(cq2.CustomerID.Ascending);
Assert.IsTrue(coll.Load(cq3));
Assert.AreEqual(36, coll.Count);
break;
}
}
public void LeftWithWhere()
{
CustomerCollection collection = new CustomerCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
CustomerQuery cust = new CustomerQuery("cq");
EmployeeQuery emp = new EmployeeQuery("eq");
cust.Select(cust.CustomerID, emp.LastName,
cust.Manager, cust.StaffAssigned);
cust.LeftJoin(emp).On(cust.Manager ==
emp.EmployeeID);
cust.Where(
cust.Manager == cust.StaffAssigned);
Assert.IsTrue(collection.Load(cust));
Assert.AreEqual(4, collection.Count);
}
