public void SingleUnion() { CustomerCollection coll = new CustomerCollection(); coll.es.Connection.Name = "ForeignKeyTest"; CustomerQuery cq1 = new CustomerQuery("c1"); cq1.SelectAllExcept(cq1.Notes); cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31"))); CustomerQuery cq2 = new CustomerQuery("c2"); cq2.SelectAllExcept(cq2.Notes); cq2.Where(cq2.DateAdded.Between(Convert.ToDateTime("2006-01-01"), Convert.ToDateTime("2006-12-31"))); // Combine 2005 and 2006 in one result set cq1.Union(cq2); //string lq = cq1.Parse(); Assert.IsTrue(coll.Load(cq1)); Assert.AreEqual(49, coll.Count); }
public void ContainsCaseInsensitive() { CustomerCollection collection = new CustomerCollection(); collection.es.Connection.Name = "ForeignKeyTest"; switch (collection.es.Connection.ProviderSignature.DataProviderName) { case "EntitySpaces.SqlClientProvider": if (collection.es.Connection.ProviderMetadataKey == "esSqlAzure") { Assert.Ignore("Not supported"); break; } string nameTerm = "acme NEAR company"; collection.Query.Where( collection.Query.CustomerName.Contains(nameTerm)); Assert.IsTrue(collection.Query.Load()); Assert.AreEqual(2, collection.Count); break; default: Assert.Ignore("Not supported"); break; } }
public void ContainsWildCard() { CustomerCollection collection = new CustomerCollection(); collection.es.Connection.Name = "ForeignKeyTest"; switch (collection.es.Connection.ProviderSignature.DataProviderName) { case "EntitySpaces.SqlClientProvider": if (collection.es.Connection.ProviderMetadataKey == "esSqlAzure") { Assert.Ignore("Not supported"); break; } string nameTerm = "\"2*\""; collection.Query.Select( collection.Query.CustomerID, collection.Query.CustomerSub, collection.Query.CustomerName.As("CName"), collection.Query.Notes); collection.Query.Where( collection.Query.CustomerName.Contains(nameTerm)); Assert.IsTrue(collection.Query.Load()); Assert.AreEqual(9, collection.Count); break; default: Assert.Ignore("Not supported"); 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 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 LoadJoined() { CustomerQuery cq = new CustomerQuery("c"); EmployeeQuery eq = new EmployeeQuery("e"); EmployeeQuery eq2 = new EmployeeQuery("e2"); OrderQuery oq = new OrderQuery("o"); OrderItemQuery oiq = new OrderItemQuery("oi"); ProductQuery pq = new ProductQuery("p"); cq.Select( cq.CustomerID, cq.CustomerSub, cq.CustomerName, eq, eq2.LastName.As("ReportsTo"), oq.PlacedBy, oq.OrderDate, oiq, pq.ProductName, pq.Discontinued); cq.LeftJoin(eq).On(eq.EmployeeID == cq.Manager); cq.LeftJoin(eq2).On(eq.Supervisor == eq2.EmployeeID); cq.LeftJoin(oq).On(cq.CustomerID == oq.CustID && cq.CustomerSub == oq.CustSub); cq.LeftJoin(oiq).On(oq.OrderID == oiq.OrderID); cq.LeftJoin(pq).On(oiq.ProductID == pq.ProductID); CustomerCollection coll = new CustomerCollection(); coll.es.Connection.Name = "ForeignKeyTest"; Assert.IsTrue(coll.Load(cq)); Assert.AreEqual(69, coll.Count); }
public void SingleIntersect() { CustomerCollection coll = new CustomerCollection(); coll.es.Connection.Name = "ForeignKeyTest"; CustomerQuery cq1 = new CustomerQuery("c1"); cq1.SelectAllExcept(cq1.Notes); cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31"))); CustomerQuery cq2 = new CustomerQuery("c2"); cq2.SelectAllExcept(cq2.Notes); cq2.Where(cq2.DateAdded.Between(Convert.ToDateTime("2005-12-31"), Convert.ToDateTime("2006-12-31"))); // Only the 2 rows for 2005-12-31 cq1.Intersect(cq2); Assert.IsTrue(coll.Load(cq1)); Assert.AreEqual(2, coll.Count); }
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); cq.LeftJoin(eq).On(cq.StaffAssigned == eq.EmployeeID & eq.Supervisor == 1); cq.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); cq.LeftJoin(eq).On(cq.StaffAssigned == eq.EmployeeID & eq.Supervisor == 1); cq.OrderBy(cq.CustomerName.Ascending); Assert.IsTrue(collection.Load(cq)); Assert.AreEqual(56, collection.Count); Assert.AreEqual("Doe", collection[record].GetColumn("LastName")); break; } }
public void SingleUnionAllWithOverlap() { CustomerCollection coll = new CustomerCollection(); coll.es.Connection.Name = "ForeignKeyTest"; CustomerQuery cq1 = new CustomerQuery("c1"); cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31"))); CustomerQuery cq2 = new CustomerQuery("c2"); cq2.Where(cq2.DateAdded.Between(Convert.ToDateTime("2005-12-31"), Convert.ToDateTime("2006-12-31"))); // Combine 2005 and 2006 in one result set with the 2 duplicate rows retained cq1.UnionAll(cq2); Assert.IsTrue(coll.Load(cq1)); Assert.AreEqual(51, coll.Count); }
public void SingleExceptWithNoOverlap() { CustomerCollection coll = new CustomerCollection(); coll.es.Connection.Name = "ForeignKeyTest"; CustomerQuery cq1 = new CustomerQuery("c1"); cq1.SelectAllExcept(cq1.Notes); cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2006-12-31"))); CustomerQuery cq2 = new CustomerQuery("c2"); cq2.SelectAllExcept(cq2.Notes); cq2.Where(cq2.DateAdded == Convert.ToDateTime("1900-12-31")); // All 2005 and 2006 cq1.Except(cq2); Assert.IsTrue(coll.Load(cq1)); Assert.AreEqual(49, coll.Count); }
public void SingleUnionWithJoin() { CustomerCollection coll = new CustomerCollection(); coll.es.Connection.Name = "ForeignKeyTest"; CustomerQuery cq1 = new CustomerQuery("c1"); EmployeeQuery eq1 = new EmployeeQuery("e1"); cq1.Select(cq1.CustomerID, cq1.CustomerSub, cq1.CustomerName, eq1.LastName); cq1.InnerJoin(eq1).On(cq1.Manager == eq1.EmployeeID); cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31"))); CustomerQuery cq2 = new CustomerQuery("c2"); EmployeeQuery eq2 = new EmployeeQuery("e2"); cq2.Select(cq2.CustomerID, cq2.CustomerSub, cq2.CustomerName, eq2.LastName); cq2.InnerJoin(eq2).On(cq2.Manager == eq2.EmployeeID); cq2.Where(cq2.DateAdded.Between(Convert.ToDateTime("2006-01-01"), Convert.ToDateTime("2006-12-31"))); cq1.Union(cq2); cq1.OrderBy(cq1.CustomerID.Ascending, cq1.CustomerSub.Ascending); //string lq = cq1.Parse(); Assert.IsTrue(coll.Load(cq1)); Assert.AreEqual(49, coll.Count); Assert.AreEqual("Smith", coll[0].GetColumn("LastName")); }
public void JoinWithArithmeticExpressionOrderByCalulatedColumn() { CustomerCollection collection = new CustomerCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); string orderAlias = ""; switch (collection.es.Connection.ProviderSignature.DataProviderName) { case "EntitySpaces.MSAccessProvider": orderAlias = "<'TotalSales'>"; break; default: orderAlias = "TotalSales"; break; } // Notice I create a calulated columns based on the TotalSales, // then Order by it descending CustomerQuery cust = new CustomerQuery("c"); OrderQuery order = new OrderQuery("o"); OrderItemQuery item = new OrderItemQuery("oi"); cust.Select(cust.CustomerName, (item.Quantity * item.UnitPrice).Sum().As("TotalSales")); cust.InnerJoin(order).On(order.CustID == cust.CustomerID); cust.InnerJoin(item).On(item.OrderID == order.OrderID); cust.GroupBy(cust.CustomerName); cust.OrderBy(orderAlias, esOrderByDirection.Descending); Assert.IsTrue(collection.Load(cust)); Assert.AreEqual(6, collection.Count); }
public void RightWithFromSubQuery() { CustomerCollection coll = new CustomerCollection(); coll.es.Connection.Name = "ForeignKeyTest"; switch (coll.es.Connection.ProviderSignature.DataProviderName) { case "EntitySpaces.SQLiteProvider": Assert.Ignore("RIGHT JOIN not supported."); break; default: CustomerQuery cq1 = new CustomerQuery("c1"); cq1.es.Top = 5; cq1.Select(cq1.CustomerID, cq1.CustomerSub); cq1.Where(cq1.Active == true); cq1.OrderBy(cq1.CustomerID.Ascending); CustomerQuery cq2 = new CustomerQuery("c2"); CustomerQuery cq3 = new CustomerQuery("c3"); cq3.Select(cq2); cq3.From(cq1).As("cSub"); cq3.RightJoin(cq2).On(cq1.CustomerID == cq2.CustomerID && cq1.CustomerSub == cq2.CustomerSub); cq3.Where(cq2.Active == true && cq1.CustomerID.IsNull()); cq3.OrderBy(cq2.CustomerID.Ascending); Assert.IsTrue(coll.Load(cq3)); Assert.AreEqual(36, coll.Count); break; } }
public void ContainsMultiTerms() { CustomerCollection coll = new CustomerCollection(); coll.es.Connection.Name = "ForeignKeyTest"; switch (coll.es.Connection.ProviderSignature.DataProviderName) { case "EntitySpaces.SqlClientProvider": if (coll.es.Connection.ProviderMetadataKey == "esSqlAzure") { Assert.Ignore("Not supported"); break; } string nameTerm = "Acme NEAR Company"; string addressTerm = "Road AND (\"St*\" OR \"Ave*\")"; coll.Query.Select( coll.Query.CustomerID, coll.Query.CustomerSub, coll.Query.CustomerName, coll.Query.Notes); coll.Query.Where( coll.Query.CustomerName.Contains(nameTerm) && coll.Query.Notes.Contains(addressTerm)); Assert.IsTrue(coll.Query.Load()); Assert.AreEqual(1, coll.Count); break; default: Assert.Ignore("Not supported"); break; } }
public void SingleIntersectWithNoOverlap() { CustomerCollection coll = new CustomerCollection(); coll.es.Connection.Name = "ForeignKeyTest"; CustomerQuery cq1 = new CustomerQuery("c1"); cq1.SelectAllExcept(cq1.Notes); cq1.Where(cq1.DateAdded.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31"))); CustomerQuery cq2 = new CustomerQuery("c2"); cq2.SelectAllExcept(cq2.Notes); cq2.Where(cq2.DateAdded.Between(Convert.ToDateTime("2006-01-01"), Convert.ToDateTime("2006-12-31"))); // There is no intersection for 2005 and 2006 cq1.Intersect(cq2); Assert.IsFalse(coll.Load(cq1)); Assert.AreEqual(0, coll.Count); }
public void ContainswithSubOperator() { CustomerCollection collection = new CustomerCollection(); collection.es.Connection.Name = "ForeignKeyTest"; switch (collection.es.Connection.ProviderSignature.DataProviderName) { case "EntitySpaces.SqlClientProvider": if (collection.es.Connection.ProviderMetadataKey == "esSqlAzure") { Assert.Ignore("Not supported"); break; } string nameTerm = "Acme NEAR Company"; // SubOperators are ignored for CONTAINS. // The search conditions belong in the search term parameter collection.Query.Where( collection.Query.CustomerName.ToLower().Contains(nameTerm)); Assert.IsTrue(collection.Query.Load()); Assert.AreEqual(2, collection.Count); break; default: Assert.Ignore("Not supported"); break; } }
public void LeftWithWhere() { CustomerCollection collection = new CustomerCollection(); collection.es.Connection.ConnectionString = UnitTestBase.GetFktString(collection.es.Connection); CustomerQuery cust = new CustomerQuery("cq"); EmployeeQuery emp = new EmployeeQuery("eq"); cust.Select(cust.CustomerID, emp.LastName, cust.Manager, cust.StaffAssigned); cust.LeftJoin(emp).On(cust.Manager == emp.EmployeeID); cust.Where( cust.Manager == cust.StaffAssigned); Assert.IsTrue(collection.Load(cust)); Assert.AreEqual(4, collection.Count); }