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 ContainsCaseInsensitive()
{
CustomerCollection collection = new CustomerCollection();
collection.es.Connection.Name = "ForeignKeyTest";
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SqlClientProvider":
if (collection.es.Connection.ProviderMetadataKey == "esSqlAzure")
{
Assert.Ignore("Not supported");
break;
}
string nameTerm =
"acme NEAR company";
collection.Query.Where(
collection.Query.CustomerName.Contains(nameTerm));
Assert.IsTrue(collection.Query.Load());
Assert.AreEqual(2, collection.Count);
break;
default:
Assert.Ignore("Not supported");
break;
}
}
public void ContainsWildCard()
{
CustomerCollection collection = new CustomerCollection();
collection.es.Connection.Name = "ForeignKeyTest";
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SqlClientProvider":
if (collection.es.Connection.ProviderMetadataKey == "esSqlAzure")
{
Assert.Ignore("Not supported");
break;
}
string nameTerm =
"\"2*\"";
collection.Query.Select(
collection.Query.CustomerID,
collection.Query.CustomerSub,
collection.Query.CustomerName.As("CName"),
collection.Query.Notes);
collection.Query.Where(
collection.Query.CustomerName.Contains(nameTerm));
Assert.IsTrue(collection.Query.Load());
Assert.AreEqual(9, collection.Count);
break;
default:
Assert.Ignore("Not supported");
break;
}
}
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 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 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 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 LeftWithOperatorsInOn()
{
int record = 0;
CustomerCollection collection = new CustomerCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
//case "EntitySpaces.NpgsqlProvider":
//case "EntitySpaces.Npgsql2Provider":
case "EntitySpaces.OracleClientProvider":
record = 2;
CustomerQuery cq = new CustomerQuery("cq");
EmployeeQuery eq = new EmployeeQuery("eq");
cq.Select(cq.CustomerName, eq.LastName);
cq.LeftJoin(eq).On(cq.StaffAssigned == eq.EmployeeID &
eq.Supervisor == 1);
cq.OrderBy(cq.CustomerName.Ascending);
Assert.IsTrue(collection.Load(cq));
Assert.AreEqual(56, collection.Count);
Assert.AreEqual("Doe", collection[record].GetColumn("LastName"));
break;
default:
record = 1;
cq = new CustomerQuery("cq");
eq = new EmployeeQuery("eq");
cq.Select(cq.CustomerName, eq.LastName);
cq.LeftJoin(eq).On(cq.StaffAssigned == eq.EmployeeID &
eq.Supervisor == 1);
cq.OrderBy(cq.CustomerName.Ascending);
Assert.IsTrue(collection.Load(cq));
Assert.AreEqual(56, collection.Count);
Assert.AreEqual("Doe", collection[record].GetColumn("LastName"));
break;
}
}
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 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 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 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"));
cust.InnerJoin(order).On(order.CustID == cust.CustomerID);
cust.InnerJoin(item).On(item.OrderID == order.OrderID);
cust.GroupBy(cust.CustomerName);
cust.OrderBy(orderAlias, esOrderByDirection.Descending);
Assert.IsTrue(collection.Load(cust));
Assert.AreEqual(6, collection.Count);
}
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 ContainsMultiTerms()
{
CustomerCollection coll = new CustomerCollection();
coll.es.Connection.Name = "ForeignKeyTest";
switch (coll.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SqlClientProvider":
if (coll.es.Connection.ProviderMetadataKey == "esSqlAzure")
{
Assert.Ignore("Not supported");
break;
}
string nameTerm =
"Acme NEAR Company";
string addressTerm =
"Road AND (\"St*\" OR \"Ave*\")";
coll.Query.Select(
coll.Query.CustomerID,
coll.Query.CustomerSub,
coll.Query.CustomerName,
coll.Query.Notes);
coll.Query.Where(
coll.Query.CustomerName.Contains(nameTerm) &&
coll.Query.Notes.Contains(addressTerm));
Assert.IsTrue(coll.Query.Load());
Assert.AreEqual(1, coll.Count);
break;
default:
Assert.Ignore("Not supported");
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 ContainswithSubOperator()
{
CustomerCollection collection = new CustomerCollection();
collection.es.Connection.Name = "ForeignKeyTest";
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SqlClientProvider":
if (collection.es.Connection.ProviderMetadataKey == "esSqlAzure")
{
Assert.Ignore("Not supported");
break;
}
string nameTerm =
"Acme NEAR Company";
// SubOperators are ignored for CONTAINS.
// The search conditions belong in the search term parameter
collection.Query.Where(
collection.Query.CustomerName.ToLower().Contains(nameTerm));
Assert.IsTrue(collection.Query.Load());
Assert.AreEqual(2, collection.Count);
break;
default:
Assert.Ignore("Not supported");
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);
}