public void SelectAllPlusSubQuery() { OrderCollection collection = new OrderCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); OrderQuery orders = new OrderQuery("o"); OrderItemQuery details = new OrderItemQuery("oi"); switch (collection.es.Connection.ProviderSignature.DataProviderName) { case "EntitySpaces.SqlServerCeProvider": case "EntitySpaces.SqlServerCe4Provider": Assert.Ignore("Not supported."); break; default: orders .Select(orders, details.Select(details.UnitPrice.Max()) .Where(orders.OrderID == details.OrderID).As("MaxUnitPrice")) .OrderBy(orders.OrderID.Ascending); break; } Assert.IsTrue(collection.Load(orders)); Assert.AreEqual(8, collection.Count); Assert.AreEqual(3m, collection[0].GetColumn("MaxUnitPrice")); string lq = collection.Query.es.LastQuery; string all = lq.Substring(7, 3); Assert.AreEqual("o.*", all); }
public void AllSubQuery() { OrderCollection collection = new OrderCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); switch (collection.es.Connection.ProviderSignature.DataProviderName) { case "EntitySpaces.SQLiteProvider": Assert.Ignore("Not supported by SQLite."); break; default: // DateAdded for Customers whose Manager = 3 CustomerQuery cq = new CustomerQuery("c"); cq.es.All = true; cq.Select(cq.DateAdded); cq.Where(cq.Manager == 3); // OrderID and CustID where the OrderDate is // less than all of the dates in the CustomerQuery above. OrderQuery oq = new OrderQuery("o"); oq.Select( oq.OrderID, oq.CustID ); oq.Where(oq.OrderDate < cq); Assert.IsTrue(collection.Load(oq)); Assert.AreEqual(8, collection.Count); break; } }
public void RightWithWhereIn() { EmployeeCollection collection = new EmployeeCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); switch (collection.es.Connection.ProviderSignature.DataProviderName) { case "EntitySpaces.SQLiteProvider": Assert.Ignore("RIGHT JOIN not supported."); break; default: List <string> custList = new List <string>(); custList.Add("01001"); custList.Add("40000"); custList.Add("XXXXX"); EmployeeQuery eq = new EmployeeQuery("eq"); CustomerQuery cq = new CustomerQuery("cq"); eq.Select(eq.EmployeeID, eq.LastName, cq.CustomerID, cq.CustomerName); eq.RightJoin(cq).On(eq.EmployeeID == cq.Manager); eq.Where(cq.CustomerID.In(custList)); EmployeeCollection coll = new EmployeeCollection(); coll.es.Connection.Name = "ForeignKeyTest"; Assert.IsTrue(coll.Load(eq)); Assert.AreEqual(14, coll.Count); break; } }
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 RightSimple() { EmployeeCollection collection = new EmployeeCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); switch (collection.es.Connection.ProviderSignature.DataProviderName) { case "EntitySpaces.SQLiteProvider": Assert.Ignore("RIGHT JOIN not supported."); break; default: EmployeeQuery eq = new EmployeeQuery("eq"); CustomerQuery cq = new CustomerQuery("cq"); eq.Select(eq.EmployeeID, eq.LastName, cq.CustomerName); eq.RightJoin(cq).On(eq.EmployeeID == cq.StaffAssigned); Assert.IsTrue(collection.Load(eq)); Assert.AreEqual(56, collection.Count); break; } }
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 Nested() { OrderCollection collection = new OrderCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); OrderQuery oq = new OrderQuery("o"); CustomerQuery cq = new CustomerQuery("c"); EmployeeQuery eq = new EmployeeQuery("e"); // OrderID and CustID for customers who ordered on the same date // a customer was added, and have a manager whose // last name starts with 'S'. oq.Select( oq.OrderID, oq.CustID ); oq.Where(oq.OrderDate .In( cq.Select(cq.DateAdded) .Where(cq.Manager.In( eq.Select(eq.EmployeeID) .Where(eq.LastName.Like("S%")) ) ) ) ); Assert.IsTrue(collection.Load(oq)); Assert.AreEqual(2, collection.Count); }
public void MixedANDAndORInOn() { ProductCollection collection = new ProductCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); int empId = 1; ProductQuery prd = new ProductQuery("pq"); OrderItemQuery item = new OrderItemQuery("oiq"); OrderQuery ord = new OrderQuery("oq"); CustomerQuery cust = new CustomerQuery("cq"); EmployeeQuery emp = new EmployeeQuery("eq"); prd.Select(prd.ProductID); prd.InnerJoin(item).On(prd.ProductID == item.ProductID); prd.InnerJoin(ord).On(item.OrderID == ord.OrderID); prd.InnerJoin(cust).On(ord.CustID == cust.CustomerID & (ord.CustSub == cust.CustomerSub | ord.EmployeeID == cust.StaffAssigned)); prd.InnerJoin(emp).On(cust.Manager == emp.EmployeeID); prd.Where(emp.EmployeeID == empId); prd.Where(prd.Discontinued == false); prd.OrderBy(prd.ProductID.Ascending); Assert.IsTrue(collection.Load(prd)); Assert.AreEqual(9, collection.Count); }
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) .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) .Where(eq.Exists(sq)); Assert.IsFalse(collection.Load(eq)); }
public void SimpleJoinOn() { OrderCollection collection = new OrderCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); switch (collection.es.Connection.ProviderSignature.DataProviderName) { case "EntitySpaces.MSAccessProvider": Assert.Ignore("SubQuery inside an ON clause not Supported"); break; default: // Query for the Join OrderItemQuery oiq = new OrderItemQuery("oi"); // SubQuery of OrderItems with a discount OrderItemQuery oisq = new OrderItemQuery("ois"); oisq.es.Distinct = true; oisq.Select(oisq.Discount); oisq.Where(oisq.Discount > 0); // Orders with discounted items OrderQuery oq = new OrderQuery("o"); oq.Select(oq.OrderID, oiq.Discount); oq.InnerJoin(oiq).On(oq.OrderID == oiq.OrderID & oiq.Discount.In(oisq)); Assert.IsTrue(collection.Load(oq)); Assert.AreEqual(2, collection.Count); break; } }
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 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 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 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 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 CombineFilteredOriginalAndCombine() { // Load a collection and apply a filter. OrderItemCollection coll = new OrderItemCollection(); coll.es.Connection.ConnectionString = UnitTestBase.GetFktString(coll.es.Connection); coll.LoadAll(); Assert.AreEqual(15, coll.Count); coll.Filter = coll.AsQueryable().Where(f => f.ProductID == 1); Assert.AreEqual(4, coll.Count); // Load a second collection and apply a different filter. OrderItemCollection coll2 = new OrderItemCollection(); coll2.es.Connection.ConnectionString = UnitTestBase.GetFktString(coll2.es.Connection); coll2.LoadAll(); Assert.AreEqual(15, coll2.Count); coll2.Filter = coll2.AsQueryable().Where(f => f.ProductID == 2); Assert.AreEqual(3, coll2.Count); // Add only the 3 filtered rows from coll2 // to all 15 rows in coll1. // The filter for coll1 still applies. // None of the items from coll2 appear, // until the filter is removed from coll1. coll.Combine(coll2); Assert.AreEqual(4, coll.Count); coll.Filter = null; Assert.AreEqual(18, coll.Count); }
public void TestAttachDetachEntityAdded() { try { using (esTransactionScope scope = new esTransactionScope()) { AggregateTestCollection aggTestColl = new AggregateTestCollection(); AggregateTestCollection aggCloneColl = new AggregateTestCollection(); aggCloneColl.LoadAll(); foreach (AggregateTest entity in aggCloneColl) { if (entity.LastName == "Doe") { entity.MarkAllColumnsAsDirty(esDataRowState.Added); aggTestColl.AttachEntity(aggCloneColl.DetachEntity(entity)); break; } } Assert.IsTrue(aggTestColl.IsDirty); aggTestColl.Save(); Assert.IsFalse(aggTestColl.IsDirty, "Collection is still dirty"); aggTestColl.LoadAll(); Assert.AreEqual(31, aggTestColl.Count); } } finally { UnitTestBase.RefreshDatabase(); } }
public void CombineFilteredOriginal() { // Load a collection and apply a filter OrderItemCollection coll = new OrderItemCollection(); coll.es.Connection.ConnectionString = UnitTestBase.GetFktString(coll.es.Connection); coll.LoadAll(); Assert.AreEqual(15, coll.Count); coll.Filter = coll.AsQueryable().Where(f => f.ProductID == 1); Assert.AreEqual(4, coll.Count); // Load a second collection OrderItemCollection coll2 = new OrderItemCollection(); coll2.es.Connection.ConnectionString = UnitTestBase.GetFktString(coll2.es.Connection); coll2.LoadAll(); Assert.AreEqual(15, coll2.Count); // Combine the 15 rows from the second collection // to the 15 rows from the first collection. // Since the first collection still has a filter, // only 8 rows are counted (4 from the first and 4 from the second). coll.Combine(coll2); Assert.AreEqual(8, coll.Count); // Remove the filter to count all 30 rows. coll.Filter = null; Assert.AreEqual(30, coll.Count); }
public void Correlated() { OrderItemCollection collection = new OrderItemCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); OrderItemQuery oiq = new OrderItemQuery("oi"); ProductQuery pq = new ProductQuery("p"); // oiq.ProductID in the inner Select is pulled from // the outer Select, making a correlated SubQuery. oiq.Select( oiq.OrderID, (oiq.Quantity * oiq.UnitPrice).Sum().As("Total") ); oiq.Where(oiq.ProductID .In( pq.Select(pq.ProductID) .Where(oiq.ProductID == pq.ProductID) ) ); oiq.GroupBy(oiq.OrderID); Assert.IsTrue(collection.Load(oiq)); 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) .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 SelectStatement() { OrderCollection collection = new OrderCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); OrderQuery orders = new OrderQuery("o"); OrderItemQuery details = new OrderItemQuery("oi"); // A SubQuery in the Select clause must return a single value. switch (collection.es.Connection.ProviderSignature.DataProviderName) { case "EntitySpaces.SqlServerCeProvider": case "EntitySpaces.SqlServerCe4Provider": Assert.Ignore("Not supported."); break; default: orders.Select ( orders.OrderID, orders.OrderDate, details.Select( details.UnitPrice.Max()) .Where(orders.OrderID == details.OrderID).As("MaxUnitPrice") ); orders.OrderBy(orders.OrderID.Ascending); break; } Assert.IsTrue(collection.Load(orders)); Assert.AreEqual(8, collection.Count); Assert.AreEqual(3m, collection[0].GetColumn("MaxUnitPrice")); }
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 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 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 CustomMultiInnerWithDistinct() { int empId = 1; ProductCollection collection = new ProductCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); Assert.IsTrue(collection.GetActiveProductIds(empId)); Assert.AreEqual(2, collection.Count); }
public void SubQueryWithGT_LT() { CustomerCollection coll = new CustomerCollection(); coll.es.Connection.ConnectionString = UnitTestBase.GetFktString(coll.es.Connection); switch (coll.es.Connection.ProviderSignature.DataProviderName) { case "EntitySpaces.SqlServerCeProvider": case "EntitySpaces.SqlServerCe4Provider": Assert.Ignore("Not supported."); break; default: DateTime fromDate = new DateTime(2005, 1, 1); DateTime toDate = new DateTime(2005, 8, 31); DateTime startDate = new DateTime(2000, 1, 1); DateTime endDate = new DateTime(2000, 12, 31); CustomerQuery cq = new CustomerQuery("c"); OrderQuery oq = new OrderQuery("o"); OrderQuery oqSub = new OrderQuery("oSub"); oqSub.Select(oqSub.OrderDate.Max()); oqSub.Where(oqSub.CustID == cq.CustomerID & oqSub.CustSub == cq.CustomerSub); // These work in SubQuery oqSub.Where(oqSub.OrderDate >= fromDate); oqSub.Where(oqSub.OrderDate <= toDate); // If you comment the above 2 GT/LT lines // and un-comment the Between line below // it gets Null Reference exception on Load() //oqSub.Where(oqSub.OrderDate.Between(fromDate, toDate)); cq.es.Distinct = true; cq.Select(cq.CustomerID, cq.CustomerSub, cq.DateAdded, oqSub.As("MaxOrderDate")); cq.InnerJoin(oq).On(cq.CustomerID == oq.CustID & cq.CustomerSub == oq.CustSub); // This works in outer query cq.Where(cq.DateAdded.Between(startDate, endDate)); Assert.IsTrue(coll.Load(cq)); Assert.AreEqual(1, coll.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) .LeftJoin(cq) .On(eq.EmployeeID == cq.StaffAssigned); Assert.IsTrue(collection.Load(eq)); Assert.AreEqual(11, collection.Count); }
public void LeftSimple() { 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); Assert.IsTrue(collection.Load(eq)); Assert.AreEqual(11, collection.Count); }
public void Init() { //esProviderFactory.Factory = new EntitySpaces.LoaderMT.esDataProviderFactory(); esProviderFactory.Factory = new EntitySpaces.Loader.esDataProviderFactory(); //ProfilerListener.BeginProfiling("EntitySpaces.SqlClientProvider", // ProfilerListener.Channels.Channel_1); esEntity.AddedByEventHandler += new ModifiedByEventHandler(esEntity_AddedByEventHandler); esEntity.ModifiedByEventHandler += new ModifiedByEventHandler(esEntity_ModifiedByHandler); UnitTestBase.RefreshDatabase(); UnitTestBase.RefreshForeignKeyTest(); }
public void LeftWithOperatorsInOn() { int record = 0; CustomerCollection collection = new CustomerCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); switch (collection.es.Connection.ProviderSignature.DataProviderName) { //case "EntitySpaces.NpgsqlProvider": //case "EntitySpaces.Npgsql2Provider": case "EntitySpaces.OracleClientProvider": record = 2; CustomerQuery cq = new CustomerQuery("cq"); EmployeeQuery eq = new EmployeeQuery("eq"); cq .Select(cq.CustomerName, eq.LastName) .LeftJoin(eq) .On(cq.StaffAssigned == eq.EmployeeID & eq.Supervisor == 1) .OrderBy(cq.CustomerName.Ascending); Assert.IsTrue(collection.Load(cq)); Assert.AreEqual(56, collection.Count); Assert.AreEqual("Doe", collection[record].GetColumn("LastName")); break; default: record = 1; cq = new CustomerQuery("cq"); eq = new EmployeeQuery("eq"); cq .Select(cq.CustomerName, eq.LastName) .LeftJoin(eq) .On(cq.StaffAssigned == eq.EmployeeID & eq.Supervisor == 1) .OrderBy(cq.CustomerName.Ascending); Assert.IsTrue(collection.Load(cq)); Assert.AreEqual(56, collection.Count); Assert.AreEqual("Doe", collection[record].GetColumn("LastName")); break; } }