public void JoinFourTablesInnerLeft()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.MSAccessProvider":
Assert.Ignore("Not supported.");
break;
default:
EmployeeQuery emp = new EmployeeQuery("e");
EmployeeTerritoryQuery empTerr = new EmployeeTerritoryQuery("et");
TerritoryQuery terr = new TerritoryQuery("t");
TerritoryExQuery terrEx = new TerritoryExQuery("tx");
emp
.Select(emp.FirstName, emp.LastName, terr.Description.As("Territory"), terrEx.Notes)
.LeftJoin(empTerr)
.On(emp.EmployeeID == empTerr.EmpID)
.InnerJoin(terr)
.On(empTerr.TerrID == terr.TerritoryID)
.LeftJoin(terrEx)
.On(terr.TerritoryID == terrEx.TerritoryID);
Assert.IsTrue(collection.Load(emp));
Assert.AreEqual(8, collection.Count);
break;
}
}
public void TwoAddStringsThenConcatenated()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery eq = new EmployeeQuery("eq");
eq.Select
(
eq.EmployeeID,
(
(eq.LastName.ToLower() + eq.FirstName.ToLower()).Trim() +
" : " +
(eq.LastName.ToUpper() + eq.FirstName.ToUpper()).Trim()
).Trim().As("SomeColumn")
);
Assert.IsTrue(collection.Load(eq));
string theName = collection[0].GetColumn("SomeColumn") as string;
Assert.AreEqual("smithjohn : SMITHJOHN", theName);
}
public void InnerJoinFourTables()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery emp = new EmployeeQuery("e");
EmployeeTerritoryQuery empTerr = new EmployeeTerritoryQuery("et");
TerritoryQuery terr = new TerritoryQuery("t");
TerritoryExQuery terrEx = new TerritoryExQuery("tx");
emp.Select(emp.FirstName, emp.LastName, terr.Description.As("Territory"), terrEx.Notes)
.InnerJoin(empTerr).On(emp.EmployeeID == empTerr.EmpID)
.InnerJoin(terr).On(terr.TerritoryID == empTerr.TerrID)
.InnerJoin(terrEx).On(terrEx.TerritoryID == terr.TerritoryID)
.Where(terrEx.Notes.IsNotNull());
Assert.IsTrue(collection.Load(emp));
Assert.AreEqual(2, collection.Count);
string theName = collection[1].GetColumn("Territory") as string;
Assert.AreEqual("North", theName);
}
public void RightSimple()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SQLiteProvider":
Assert.Ignore("RIGHT JOIN not supported.");
break;
default:
EmployeeQuery eq = new EmployeeQuery("eq");
CustomerQuery cq = new CustomerQuery("cq");
eq
.Select(eq.EmployeeID, eq.LastName, cq.CustomerName)
.RightJoin(cq)
.On(eq.EmployeeID == cq.StaffAssigned);
Assert.IsTrue(collection.Load(eq));
Assert.AreEqual(56, collection.Count);
break;
}
}
public void LeftWithContains()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SqlClientProvider":
string nameTerm =
"acme NEAR company";
EmployeeQuery eq = new EmployeeQuery("eq");
CustomerQuery cq = new CustomerQuery("cq");
eq
.Select(eq.EmployeeID, eq.LastName, cq.CustomerName)
.LeftJoin(cq)
.On(eq.EmployeeID == cq.StaffAssigned)
.Where(cq.CustomerName.Contains(nameTerm));
Assert.IsTrue(collection.Load(eq));
Assert.AreEqual(2, collection.Count);
break;
default:
Assert.Ignore("Not supported");
break;
}
}
public void LeftJoinFourTablesWithWhere()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery emp = new EmployeeQuery("e");
EmployeeTerritoryQuery empTerr = new EmployeeTerritoryQuery("et");
TerritoryQuery terr = new TerritoryQuery("t");
TerritoryExQuery terrEx = new TerritoryExQuery("tx");
emp
.Select(emp.FirstName, emp.LastName, terr.Description.As("Territory"), terrEx.Notes)
.LeftJoin(empTerr)
.On(emp.EmployeeID == empTerr.EmpID)
.LeftJoin(terr)
.On(empTerr.TerrID == terr.TerritoryID)
.LeftJoin(terrEx)
.On(terr.TerritoryID == terrEx.TerritoryID)
.Where(emp.FirstName.Trim().Like("J___"));
Assert.IsTrue(collection.Load(emp));
Assert.AreEqual(7, collection.Count);
}
private void Form1_Load(object sender, EventArgs e)
{
EmployeeCollection coll = new EmployeeCollection();
EmployeeQuery query = coll.Query; // short hand
query.Select(query.EmployeeID, query.LastName, query.FirstName);
if (coll.Query.Load())
{
this.dataGrid1.DataSource = coll;
}
}
public void LeftRawSelect()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery eq = new EmployeeQuery("eq");
CustomerQuery cq = new CustomerQuery("cq");
string special = "";
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.MSAccessProvider":
special = "<cq.[CustomerID] + '-' + cq.[CustomerSub] AS FullCustomerId>";
break;
case "EntitySpaces.MySqlClientProvider":
special = "<CONCAT(cq.`CustomerID`, '-', cq.`CustomerSub`) AS 'FullCustomerId'>";
break;
case "EntitySpaces.NpgsqlProvider":
case "EntitySpaces.Npgsql2Provider":
case "EntitySpaces.OracleClientProvider":
special = "<cq.\"CustomerID\" || '-' || cq.\"CustomerSub\" AS \"FullCustomerId\">";
break;
default:
if (collection.es.Connection.Name == "SqlCe")
{
special = "<cq.[CustomerID] + '-' + cq.[CustomerSub] AS \"FullCustomerId\">";
}
else
{
special = "<cq.[CustomerID] + '-' + cq.[CustomerSub] As FullCustomerId>";
}
break;
}
eq
.Select(eq.EmployeeID, eq.LastName, special, cq.CustomerName)
.LeftJoin(cq)
.On(eq.EmployeeID == cq.StaffAssigned);
Assert.IsTrue(collection.Load(eq));
Assert.AreEqual(11, collection.Count);
}
public void LeftSimple()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery eq = new EmployeeQuery("eq");
CustomerQuery cq = new CustomerQuery("cq");
eq.Select(eq.EmployeeID, eq.LastName, cq.CustomerName);
eq.LeftJoin(cq).On(eq.EmployeeID == cq.StaffAssigned);
Assert.IsTrue(collection.Load(eq));
Assert.AreEqual(11, collection.Count);
}
public void LeftSelfJoin()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery eq = new EmployeeQuery("eq");
EmployeeQuery supervisorQuery = new EmployeeQuery("sq");
eq.Select(eq.EmployeeID, eq.LastName,
supervisorQuery.LastName.As("Reports To"));
eq.LeftJoin(supervisorQuery)
.On(eq.Supervisor == supervisorQuery.EmployeeID);
Assert.IsTrue(collection.Load(eq));
Assert.AreEqual(5, collection.Count);
}
public void ConcatTwoStringsAndTwoLiterals()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery eq = new EmployeeQuery("eq");
eq.Select(eq.EmployeeID,
(eq.LastName + " (" + eq.FirstName + ")").As("FullName"));
Assert.IsTrue(collection.Load(eq));
string theName = collection[0].GetColumn("FullName") as string;
Assert.AreEqual(12, theName.Length);
}
public void SerializeDeserializeJoin()
{
if (aggTest.es.Connection.Name == "SqlCe")
{
Assert.Ignore("Not tested for SqlCe.");
}
else
{
// Test serializing a DynamicQuery
// Must use binary serialization
// Xml serialization doesn't serialize all of your private properties
// 1) Create our Query on the client
EmployeeQuery emp = new EmployeeQuery("eq");
//emp.es.Connection.Name = "ForeignKeyTest";
EmployeeTerritoryQuery et = new EmployeeTerritoryQuery("etq");
//et.es.Connection.Name = "ForeignKeyTest";
emp.Select(emp.FirstName, emp.LastName, et.TerrID);
emp.InnerJoin(et).On(emp.EmployeeID == et.EmpID);
emp.Where(emp.LastName.Like("S%"));
// 2) Serialize it in binary
BinaryFormatter bf = new BinaryFormatter();
MemoryStream ms = new MemoryStream();
bf.Serialize(ms, emp);
byte[] query = ms.ToArray();
// 3) Send it over the wire
// 4) Deserialize it on the Server
bf = new BinaryFormatter();
ms = new MemoryStream(query);
EmployeeQuery newQuery = bf.Deserialize(ms) as EmployeeQuery;
// Now load it
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.Name = "ForeignKeyTest";
collection.Load(newQuery);
Assert.AreEqual(5, collection.Count);
Assert.AreEqual("S", collection[0].LastName.Substring(0, 1));
}
}
public void LeftWithDateComparisonInOn()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery eq = new EmployeeQuery("eq");
CustomerQuery cq = new CustomerQuery("cq");
eq.Select(eq.LastName);
eq.LeftJoin(cq).On(eq.EmployeeID == cq.StaffAssigned &
cq.DateAdded < Convert.ToDateTime("2005-12-31"));
eq.Where(cq.DateAdded < Convert.ToDateTime("2000-12-31"));
eq.OrderBy(eq.LastName.Ascending);
Assert.IsTrue(collection.Load(eq));
Assert.AreEqual(7, collection.Count);
}
public void TwoAddStringsWithOrderBy()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery eq = new EmployeeQuery("eq");
eq.Select(eq.EmployeeID,
(eq.LastName + ", " + eq.FirstName).As("FullName"));
eq.OrderBy(eq.EmployeeID, esOrderByDirection.Ascending);
Assert.IsTrue(collection.Load(eq));
string theName = collection[0].GetColumn("FullName") as string;
Assert.AreEqual(11, theName.Length);
}
public void JoinWithPaging()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.MSAccessProvider":
case "EntitySpaces.SqlServerCeProvider":
case "EntitySpaces.VistaDBProvider":
case "EntitySpaces.VistaDB4Provider":
Assert.Ignore("Not supported");
break;
default:
EmployeeQuery emp = new EmployeeQuery("e");
EmployeeTerritoryQuery empTerr = new EmployeeTerritoryQuery("et");
TerritoryQuery terr = new TerritoryQuery("t");
TerritoryExQuery terrEx = new TerritoryExQuery("tx");
emp
.Select(emp, terr.Description.As("Territory"), terrEx.Notes)
.InnerJoin(empTerr)
.On(empTerr.TerrID == emp.EmployeeID)
.InnerJoin(terr)
.On(terr.TerritoryID == empTerr.TerrID)
.InnerJoin(terrEx)
.On(terrEx.TerritoryID == terr.TerritoryID)
.Where(terrEx.Notes.IsNotNull())
.OrderBy(emp.FirstName.Ascending);
emp.es.PageNumber = 1;
emp.es.PageSize = 20;
Assert.IsTrue(collection.Load(emp));
Assert.AreEqual(2, collection.Count);
break;
}
}
public void TwoAddStringsWithToUpper()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery eq = new EmployeeQuery("eq");
eq.Select(eq.EmployeeID,
(eq.LastName.ToUpper() + ", " + eq.FirstName).As("FullName"));
eq.Where(eq.LastName == "Doe");
eq.OrderBy(eq.FirstName.Ascending);
Assert.IsTrue(collection.Load(eq));
string theName = collection[0].GetColumn("FullName") as string;
Assert.AreEqual("DOE, Jane", theName);
}
public void SimpleSyntaxCheckWithLike()
{
EmployeeQuery q = new EmployeeQuery();
q.es2.Connection.Name = "ForeignKeyTest";
q.Select
(
q.LastName
.Case()
.When(q.LastName.Like("%a")).Then("Last Name Contains an A")
.Else("Last Name Doesnt Contain an A")
.End().As("SpecialLastName")
);
EmployeeCollection coll = new EmployeeCollection();
coll.es.Connection.Name = "ForeignKeyTest";
coll.Load(q);
}
public void FullSimple()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
if (collection.es.Connection.Name == "SqlCe")
{
Assert.Ignore("FULL JOIN not supported.");
}
else
{
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.MSAccessProvider":
case "EntitySpaces.MySqlClientProvider":
case "EntitySpaces.SQLiteProvider":
case "EntitySpaces.SqlServerCeProvider":
case "EntitySpaces.SqlServerCe4Provider":
case "EntitySpaces.VistaDBProvider":
case "EntitySpaces.VistaDB4Provider":
Assert.Ignore("FULL JOIN not supported.");
break;
default:
EmployeeQuery eq = new EmployeeQuery("eq");
CustomerQuery cq = new CustomerQuery("cq");
eq
.Select(eq.EmployeeID, eq.LastName, cq.CustomerName)
.FullJoin(cq)
.On(eq.EmployeeID == cq.StaffAssigned);
Assert.IsTrue(collection.Load(eq));
Assert.AreEqual(57, 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 CombineJoinQueriedCollections()
{
EmployeeQuery eq1 = new EmployeeQuery("e1");
CustomerQuery cq1 = new CustomerQuery("c1");
eq1.Select(eq1, cq1.CustomerName);
eq1.InnerJoin(cq1).On(eq1.EmployeeID == cq1.Manager);
eq1.Where(eq1.EmployeeID == 1);
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
collection.Load(eq1);
Assert.AreEqual(35, collection.Count);
EmployeeQuery eq2 = new EmployeeQuery("e2");
CustomerQuery cq2 = new CustomerQuery("c2");
eq2.Select(eq2, cq2.CustomerName);
eq2.InnerJoin(cq2).On(eq2.EmployeeID == cq2.Manager);
eq2.Where(eq2.EmployeeID == 2);
EmployeeCollection collection2 = new EmployeeCollection();
collection2.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection2.es.Connection);
collection2.Load(eq2);
Assert.AreEqual(12, collection2.Count);
collection.Combine(collection2);
Assert.AreEqual(47, collection.Count);
foreach (Employee emp in collection)
{
string custName = CustomerMetadata.ColumnNames.CustomerName;
Assert.IsTrue(emp.GetColumn(custName).ToString().Length > 0);
}
}
public void OneAddIntegers()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.Name = "ForeignKeyTest";
EmployeeQuery eq = new EmployeeQuery("eq");
eq.Select(eq.EmployeeID, eq.Age,
(eq.Age + eq.Supervisor).As("SomeInt"));
eq.OrderBy(eq.EmployeeID, esOrderByDirection.Ascending);
Assert.IsTrue(collection.Load(eq));
object si = collection[0].GetColumn("SomeInt");
Assert.AreEqual(null, si);
int someInt = Convert.ToInt32(collection[1].GetColumn("SomeInt"));
Assert.AreEqual(21, someInt);
}
public void WhereExistsFalse()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
// EmployeeID is required and will never be NULL
EmployeeQuery sq = new EmployeeQuery("s");
sq.es.Distinct = true;
sq.Select(sq.EmployeeID);
sq.Where(sq.EmployeeID.IsNull());
// This should produce no results as the
// inner query does not exist.
EmployeeQuery eq = new EmployeeQuery("e");
eq.Select(eq.EmployeeID, eq.Supervisor);
eq.Where(eq.Exists(sq));
Assert.IsFalse(collection.Load(eq));
}
public void MultiExpression()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery eq = new EmployeeQuery("eq");
eq.Select(eq.EmployeeID, eq.Age,
((eq.Age + eq.Supervisor) / 3).As("SomeInt"));
eq.OrderBy(eq.EmployeeID, esOrderByDirection.Ascending);
Assert.IsTrue(collection.Load(eq));
object si = collection[0].GetColumn("SomeInt");
Assert.AreEqual(null, si);
int someInt = Convert.ToInt32(collection[1].GetColumn("SomeInt"));
Assert.AreEqual(7, someInt);
}
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)
.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);
}