public void WhereMixMultiWithParenNested()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.Name = "ForeignKeyTest";
collection.Query
.Select(collection.Query.EmployeeID,collection.Query.Supervisor,collection.Query.Age)
.Where(collection.Query.Age == 30)
.Where(new esComparison(esParenthesis.Open))
.Where(new esComparison(esParenthesis.Open));
collection.Query.es.DefaultConjunction = esConjunction.Or;
collection.Query
.Where(collection.Query.EmployeeID == 1 & collection.Query.Supervisor.IsNull())
.Where(collection.Query.EmployeeID == 2 & collection.Query.Supervisor == 1)
.Where(new esComparison(esParenthesis.Close));
collection.Query.es.DefaultConjunction = esConjunction.And;
collection.Query.Where(new esComparison(esParenthesis.Open));
collection.Query.es.DefaultConjunction = esConjunction.Or;
collection.Query
.Where(collection.Query.LastName == "Smith" & collection.Query.Supervisor.IsNull())
.Where(collection.Query.LastName == "Jones" & collection.Query.Supervisor == 1);
collection.Query.Where(new esComparison(esParenthesis.Close));
collection.Query.Where(new esComparison(esParenthesis.Close));
Assert.IsTrue(collection.Query.Load());
Assert.AreEqual(1, 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 SimpleUnion()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.Name = "ForeignKeyTest";
EmployeeQuery eq1 = new EmployeeQuery("eq1");
EmployeeQuery eq2 = new EmployeeQuery("eq2");
// This leaves out the record with Age 30
eq1.Where(eq1.Age < 30);
eq1.Union(eq2);
eq2.Where(eq2.Age > 30);
Assert.IsTrue(collection.Load(eq1));
Assert.AreEqual(4, collection.Count);
}
public void SimpleExcept()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.Name = "ForeignKeyTest";
EmployeeQuery eq1 = new EmployeeQuery("eq1");
EmployeeQuery eq2 = new EmployeeQuery("eq2");
// Includes all "J"s except "Jim"
eq1.Where(eq1.FirstName.Like("%J%"));
eq1.Except(eq2);
eq2.Where(eq2.FirstName == "Jim");
Assert.IsTrue(collection.Load(eq1));
Assert.AreEqual(3, 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 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 SimpleIntersect()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.Name = "ForeignKeyTest";
EmployeeQuery eq1 = new EmployeeQuery("eq1");
EmployeeQuery eq2 = new EmployeeQuery("eq2");
// Only includes rows with both "n" and"a"
eq1.Where(eq1.FirstName.Like("%n%"));
eq1.Intersect(eq2);
eq2.Where(eq2.FirstName.Like("%a%"));
Assert.IsTrue(collection.Load(eq1));
Assert.AreEqual(2, collection.Count);
}
public void SingleZeroToMany()
{
// The main Employee query
EmployeeQuery eq1 = new EmployeeQuery("e");
eq1.Where(eq1.EmployeeID < 3);
eq1.OrderBy(eq1.EmployeeID.Ascending);
// The Order Collection
OrderQuery oq1 = eq1.Prefetch<OrderQuery>(Employee.Prefetch_OrderCollectionByEmployeeID);
EmployeeQuery eq2 = oq1.GetQuery<EmployeeQuery>();
oq1.Where(eq2.EmployeeID < 3);
// Pre-test the Order query
OrderCollection oColl = new OrderCollection();
oColl.es.Connection.Name = "ForeignKeyTest";
oColl.Load(oq1);
Assert.AreEqual(3, oColl.Count, "Order pre-test");
// This will Prefetch the Order query
EmployeeCollection coll = new EmployeeCollection();
coll.es.Connection.Name = "ForeignKeyTest";
//coll.es.IsLazyLoadDisabled = true;
coll.Load(eq1);
foreach (Employee emp in coll)
{
emp.es.IsLazyLoadDisabled = true;
switch (emp.EmployeeID.Value)
{
case 1:
Assert.AreEqual(1, emp.EmployeeID.Value);
Assert.AreEqual(1, emp.OrderCollectionByEmployeeID.Count);
break;
case 2:
Assert.AreEqual(2, emp.EmployeeID.Value);
Assert.AreEqual(2, emp.OrderCollectionByEmployeeID.Count);
break;
default:
Assert.Fail("Only employees 1 and 2 should be loaded.");
break;
}
}
}
public void TestSerializeQuery()
{
EmployeeQuery query = new EmployeeQuery("e");
query.Where(query.EmployeeID.In(1, 2, 3, new List<object>() { 1, 2, 3 }));
string qq = EmployeeQuery.SerializeHelper.ToXml(query);
List<Type> types = new List<Type>();
types.Add(typeof(EmployeeQuery));
EmployeeQuery employeeQuery = EmployeeQuery.SerializeHelper.FromXml(
qq, typeof(EmployeeQuery), types) as EmployeeQuery;
EmployeeCollection c = new EmployeeCollection();
c.es.Connection.Name = "ForeignKeyTest";
c.Load(employeeQuery);
Assert.IsTrue(c.Count == 3);
}
public void SelectAllExceptTwo()
{
EmployeeCollection coll = new EmployeeCollection();
coll.es.Connection.Name = "ForeignKeyTest";
coll.Query.SelectAllExcept(coll.Query.EmployeeID, coll.Query.FirstName);
coll.Query.OrderBy(coll.Query.LastName.Descending);
coll.Query.OrderBy(coll.Query.FirstName.Descending);
coll.Query.Load();
// Confirm that EmployeeID and LastName are null,
// and all other columns are not.
Assert.AreEqual(5, coll.Count);
Assert.IsTrue(coll[0].GetColumn(EmployeeMetadata.ColumnNames.EmployeeID) == null);
Assert.IsTrue(coll[0].GetColumn(EmployeeMetadata.ColumnNames.FirstName) == null);
Assert.AreEqual("Smith", coll[0].GetColumn(EmployeeMetadata.ColumnNames.LastName));
Assert.AreEqual(30, coll[0].GetColumn(EmployeeMetadata.ColumnNames.Age));
Assert.IsTrue(coll[0].GetColumn(EmployeeMetadata.ColumnNames.Supervisor) == null);
}
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 SelectCollection()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.Name = "ForeignKeyTest";
collection.Query.OrderBy(collection.Query.EmployeeID.Ascending);
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
//case "EntitySpaces.MySqlClientProvider":
//case "EntitySpaces.NpgsqlProvider":
//case "EntitySpaces.Npgsql2Provider":
//case "EntitySpaces.SQLiteProvider":
//case "EntitySpaces.VistaDBProvider":
case "EntitySpaces.MSAccessProvider":
//case "EntitySpaces.SqlServerCeProvider":
Assert.Ignore("Not supported.");
break;
case "EntitySpaces.OracleClientProvider":
collection.Query.Select
(
(collection.Query.FirstName.ToUpper() + ":" + collection.Query.Age.Cast(esCastType.String, 2)).As("FirstName"),
collection.Query.LastName
);
Assert.IsTrue(collection.Query.Load());
Assert.AreEqual("JOHN:30", collection[0].FirstName);
break;
default:
collection.Query.Select
(
(collection.Query.FirstName.ToUpper() + ":" + (esString)collection.Query.Age).As("FirstName"),
collection.Query.LastName
);
Assert.IsTrue(collection.Query.Load());
Assert.AreEqual("JOHN:30", collection[0].FirstName);
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 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 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 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 WhereInANDedTogetherOperator()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery eq1 = new EmployeeQuery("e1");
EmployeeQuery eq2 = new EmployeeQuery("e2");
EmployeeQuery eq3 = new EmployeeQuery("e3");
eq2.Select(eq2.EmployeeID);
eq3.Select(eq3.EmployeeID);
eq1.Where(eq1.EmployeeID.In(eq2) & eq1.EmployeeID.In(eq3));
Assert.IsTrue(collection.Load(eq1));
Assert.AreEqual(5, collection.Count);
string lq = collection.Query.es.LastQuery;
string[] one = lq.Split('1');
Assert.AreEqual(4, one.GetLength(0));
string[] two = lq.Split('2');
Assert.AreEqual(3, two.GetLength(0));
string[] three = lq.Split('3');
Assert.AreEqual(3, three.GetLength(0));
}
public void WhereExists()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
// SubQuery of Employees with a null Supervisor column.
EmployeeQuery sq = new EmployeeQuery("s");
sq.es.Distinct = true;
sq.Select(sq.EmployeeID);
sq.Where(sq.Supervisor.IsNull());
// If even one employee has a null supervisor,
// i.e., the above query has a result set,
// then run a list of all employees.
EmployeeQuery eq = new EmployeeQuery("e");
eq.Select(eq.EmployeeID, eq.Supervisor);
eq.Where(eq.Exists(sq));
Assert.IsTrue(collection.Load(eq));
Assert.AreEqual(5, collection.Count);
}
public void SerializeDeserializeProxyStubCollection()
{
if (aggTest.es.Connection.Name == "SqlCe")
{
Assert.Ignore("Not tested for SqlCe.");
}
else
{
// Load all 5 Employees
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.Name = "ForeignKeyTest";
collection.LoadAll();
// Create a Proxy and Serialize into a string named "Packet"
EmployeeCollectionProxyStub proxy =
new EmployeeCollectionProxyStub(collection);
XmlSerializer sf =
new XmlSerializer(typeof(EmployeeCollectionProxyStub));
StringWriter sw = new StringWriter();
sf.Serialize(sw, proxy);
string packet = sw.ToString();
// Now let's DeSerialize it
XmlSerializer xs =
new XmlSerializer(typeof(EmployeeCollectionProxyStub));
StringReader sr = new StringReader(packet);
proxy = xs.Deserialize(sr) as EmployeeCollectionProxyStub;
// Count should = 5
Assert.AreEqual(5, proxy.GetCollection().Count);
Assert.AreEqual("Smith", proxy.Collection[0].LastName);
}
}
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 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 TestProxiesDirtyRowsOnly()
{
EmployeeCollection coll = new EmployeeCollection();
Employee e = coll.AddNew();
e.EmployeeID = 1;
e.FirstName = "unchanged";
e.LastName = "unchanged";
e = coll.AddNew();
e.EmployeeID = 2;
e.FirstName = "unchanged";
e.LastName = "unchanged";
e = coll.AddNew();
e.EmployeeID = 3;
e.FirstName = "deleted";
e.LastName = "deleted";
coll.AcceptChanges();
coll[2].MarkAsDeleted();
e = coll.AddNew();
e.EmployeeID = 4;
e.FirstName = "Added";
e.LastName = "Added";
EmployeeCollectionProxyStub proxy = new EmployeeCollectionProxyStub(coll);
string qq = esDataContractSerializer.ToXml(proxy);
}
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 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 WhereExistsANDed()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
EmployeeQuery eq1 = new EmployeeQuery("e1");
EmployeeQuery eq2 = new EmployeeQuery("e2");
eq1.Where(eq1.EmployeeID > 2, eq1.Exists(eq2));
Assert.IsTrue(collection.Load(eq1));
Assert.AreEqual(3, collection.Count);
string lq = collection.Query.es.LastQuery;
string[] one = lq.Split('1');
Assert.AreEqual(4, one.GetLength(0));
string[] two = lq.Split('2');
Assert.AreEqual(2, two.GetLength(0));
}
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 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 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 WhereMixMultiWithParen()
{
EmployeeCollection collection = new EmployeeCollection();
collection.es.Connection.Name = "ForeignKeyTest";
collection.Query.Select(
collection.Query.EmployeeID,
collection.Query.Supervisor,
collection.Query.Age);
collection.Query.Where(collection.Query.Age == 20);
collection.Query.Where(new esComparison(esParenthesis.Open));
collection.Query.es.DefaultConjunction = esConjunction.Or;
for (int i = 0; i < 4; i++)
{
collection.Query.Where(
collection.Query.Supervisor == i &
collection.Query.EmployeeID == i + 1);
}
collection.Query.Where(new esComparison(esParenthesis.Close));
Assert.IsTrue(collection.Query.Load());
Assert.AreEqual(1, collection.Count);
}
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);
}
