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