public void WhereWithJoin()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
// SubQuery of Territories
TerritoryQuery tq = new TerritoryQuery("t");
tq.Select(tq.TerritoryID);
tq.Where(tq.Description == "North" |
tq.Description == "West");
// EmployeeTerritory Query for Join
EmployeeTerritoryQuery etq = new EmployeeTerritoryQuery("et");
// Employees matching those territories
EmployeeQuery eq = new EmployeeQuery("e");
eq.es.Distinct = true;
eq.Select(eq.EmployeeID, etq.TerrID);
eq.LeftJoin(etq).On(
eq.EmployeeID == etq.EmpID);
eq.Where(etq.TerrID.In(tq));
eq.OrderBy(eq.EmployeeID.Ascending);
Assert.IsTrue(collection.Load(eq));
Assert.AreEqual(3, collection.Count);
}
public void CrossDbJoin()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SqlClientProvider":
// AggregateDb
AggregateTestQuery aq = new AggregateTestQuery("a");
// ForeignKeyTest
EmployeeQuery eq = new EmployeeQuery("e");
eq.Select(eq.LastName, eq.FirstName, aq.Age);
eq.LeftJoin(aq).On(
eq.LastName == aq.LastName &
eq.FirstName == aq.FirstName);
eq.OrderBy(eq.LastName.Ascending,
eq.FirstName.Ascending);
Assert.IsTrue(collection.Load(eq));
Assert.AreEqual(22, collection[2].GetColumn("Age"));
break;
default:
Assert.Ignore("SQL Server only");
break;
}
}
public void LeftWithExplicitParen()
{
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);
eq.Where(eq.Age == 20);
eq.Where(new esComparison(esParenthesis.Open));
eq.es.DefaultConjunction = esConjunction.Or;
for (int i = 0; i < 4; i++)
{
eq.Where(
eq.Supervisor == i &
eq.EmployeeID == i + 1);
}
eq.Where(new esComparison(esParenthesis.Close));
Assert.IsTrue(collection.Load(eq));
Assert.AreEqual(5, collection.Count);
}
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);
emp.LeftJoin(empTerr).On(emp.EmployeeID == empTerr.EmpID);
emp.LeftJoin(terr).On(empTerr.TerrID == terr.TerritoryID);
emp.LeftJoin(terrEx).On(terr.TerritoryID == terrEx.TerritoryID);
emp.Where(emp.FirstName.Trim().Like("J___"));
Assert.IsTrue(collection.Load(emp));
Assert.AreEqual(7, collection.Count);
}
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 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);
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 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);
eq.LeftJoin(cq).On(eq.EmployeeID == cq.StaffAssigned);
eq.Where(cq.CustomerName.Contains(nameTerm));
Assert.IsTrue(collection.Load(eq));
Assert.AreEqual(2, collection.Count);
break;
default:
Assert.Ignore("Not supported");
break;
}
}
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 LeftIsNotNull()
{
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.Manager &
cq.StaffAssigned.IsNotNull());
Assert.IsTrue(collection.Load(eq));
Assert.AreEqual(13, collection.Count);
}