public void WhereNotIn() { TerritoryCollection collection = new TerritoryCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); // SubQuery of Territories that Employee 1 is assigned to. EmployeeTerritoryQuery etq = new EmployeeTerritoryQuery("et"); etq.Select(etq.TerrID); etq.Where(etq.EmpID == 1); // Territories that Employee 1 is not assigned to. TerritoryQuery tq = new TerritoryQuery("t"); tq.Select(tq.Description); tq.Where(tq.TerritoryID.NotIn(etq)); tq.OrderBy(tq.TerritoryID.Ascending); Assert.IsTrue(collection.Load(tq)); Assert.AreEqual(2, collection.Count); Assert.AreEqual("West", collection[0].GetColumn( TerritoryMetadata.ColumnNames.Description)); }
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 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 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); }
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 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; } }