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);
emp.InnerJoin(empTerr).On(emp.EmployeeID == empTerr.EmpID);
emp.InnerJoin(terr).On(terr.TerritoryID == empTerr.TerrID);
emp.InnerJoin(terrEx).On(terrEx.TerritoryID == terr.TerritoryID);
emp.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 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);
emp.LeftJoin(empTerr).On(emp.EmployeeID == empTerr.EmpID);
emp.InnerJoin(terr).On(empTerr.TerrID == terr.TerritoryID);
emp.LeftJoin(terrEx).On(terr.TerritoryID == terrEx.TerritoryID);
Assert.IsTrue(collection.Load(emp));
Assert.AreEqual(8, collection.Count);
break;
}
}
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);
emp.InnerJoin(empTerr).On(empTerr.TerrID == emp.EmployeeID);
emp.InnerJoin(terr).On(terr.TerritoryID == empTerr.TerrID);
emp.InnerJoin(terrEx).On(terrEx.TerritoryID == terr.TerritoryID);
emp.Where(terrEx.Notes.IsNotNull());
emp.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 InnerSimple()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.Name = "ForeignKeyTest";
//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.InnerJoin(cq).On(eq.EmployeeID == cq.StaffAssigned);
Assert.IsTrue(collection.Load(eq));
Assert.AreEqual(10, collection.Count);
}
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 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);
}
}