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); }