public void InnerAlias()
{
OrderCollection collection = new OrderCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.MSAccessProvider":
Assert.Ignore("Not supported");
break;
case "EntitySpaces.VistaDBProvider":
case "EntitySpaces.VistaDB4Provider":
OrderQuery oq = new OrderQuery("o");
OrderItemQuery odq = new OrderItemQuery("od");
oq.Select(oq.OrderID, odq.OrderID);
oq.InnerJoin(odq).On(oq.OrderID == odq.OrderID);
oq.OrderBy(oq.OrderID.Ascending);
Assert.IsTrue(collection.Load(oq), "Load");
string lq = collection.Query.es.LastQuery;
Assert.AreEqual(1, collection[0].OrderID.Value, "Property");
Assert.AreEqual(1, Convert.ToInt32(collection[0].GetColumn("OrderID_1")), "Virtual");
break;
default:
oq = new OrderQuery("o");
odq = new OrderItemQuery("od");
oq.Select(oq.OrderID, odq.OrderID);
oq.InnerJoin(odq).On(oq.OrderID == odq.OrderID);
oq.OrderBy(oq.OrderID.Ascending);
Assert.IsTrue(collection.Load(oq), "Load");
lq = collection.Query.es.LastQuery;
Assert.AreEqual(1, collection[0].OrderID.Value, "Property");
Assert.AreEqual(1, Convert.ToInt32(collection[0].GetColumn("OrderID1")), "Virtual");
break;
}
}
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")
);
orders.OrderBy(orders.OrderID.Ascending);
break;
}
Assert.IsTrue(collection.Load(orders));
Assert.AreEqual(8, collection.Count);
Assert.AreEqual(3m, collection[0].GetColumn("MaxUnitPrice"));
string all = collection.Query.es.LastQuery.Substring(7, 3);
Assert.AreEqual("o.*", all);
}
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 FromClauseUsingInstance()
{
OrderCollection collection = new OrderCollection();
collection.es.Connection.Name = "ForeignKeyTest";
// Select an arbitrary number of rows from
// any starting row.
int startRow = 4;
int numberOfRows = 7;
// OrderItem SubQuery 1
// Get all rows through start + number, ascending
OrderItemQuery oisq = new OrderItemQuery("ois");
oisq.es.Top = startRow + numberOfRows - 1;
oisq.Select(oisq.OrderID, oisq.ProductID, oisq.Quantity);
oisq.OrderBy(oisq.OrderID.Ascending,
oisq.ProductID.Ascending);
// OrderItem SubQuery 2
// Get just the number of rows, descending
OrderItemQuery oisq2 = new OrderItemQuery("ois2");
oisq2.es.Top = numberOfRows;
oisq2.From(oisq).As("sub1");
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
//case "EntitySpaces.SqlServerCeProvider":
// Assert.Ignore("Not supported.");
// break;
case "EntitySpaces.NpgsqlProvider":
case "EntitySpaces.Npgsql2Provider":
oisq2.OrderBy("<sub1.\"OrderID\">", esOrderByDirection.Descending);
oisq2.OrderBy("<sub1.\"ProductID\">", esOrderByDirection.Descending);
break;
case "EntitySpaces.OracleClientProvider":
Assert.Ignore("Not supported.");
break;
default:
oisq2.OrderBy("<sub1.OrderID>", esOrderByDirection.Descending);
oisq2.OrderBy("<sub1.ProductID>", esOrderByDirection.Descending);
break;
}
// Put it back in ascending order
OrderQuery oq = new OrderQuery("o");
oq.From(oisq2).As("sub2");
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.NpgsqlProvider":
case "EntitySpaces.Npgsql2Provider":
oq.OrderBy("<sub2.\"OrderID\">", esOrderByDirection.Ascending);
oq.OrderBy("<sub2.\"ProductID\">", esOrderByDirection.Ascending);
break;
default:
oq.OrderBy("<sub2.OrderID>", esOrderByDirection.Ascending);
oq.OrderBy("<sub2.ProductID>", esOrderByDirection.Ascending);
break;
}
Assert.IsTrue(collection.Load(oq));
Assert.AreEqual(7, collection.Count);
Assert.AreEqual(10, collection[0].GetColumn("Quantity"));
}
public void FromClauseWithAlias()
{
OrderCollection collection = new OrderCollection();
collection.es.Connection.Name = "ForeignKeyTest";
OrderQuery oq = new OrderQuery("o");
OrderItemQuery oiq = new OrderItemQuery("oi");
// Calculate total price per order
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
//case "EntitySpaces.SqlServerCeProvider":
// Assert.Ignore("Not supported.");
// break;
case "EntitySpaces.NpgsqlProvider":
case "EntitySpaces.Npgsql2Provider":
case "EntitySpaces.OracleClientProvider":
oq.Select(oq.CustID, oq.OrderDate, "<sub.\"OrderTotal\">");
break;
default:
oq.Select(oq.CustID, oq.OrderDate, "<sub.OrderTotal>");
break;
}
oq.From
(
oiq.Select(oiq.OrderID,
(oiq.UnitPrice * oiq.Quantity).Sum().As("OrderTotal"))
.GroupBy(oiq.OrderID)
).As("sub");
oq.InnerJoin(oq).On(oq.OrderID == oiq.OrderID);
oq.OrderBy(oq.OrderID.Ascending);
Assert.IsTrue(collection.Load(oq));
Assert.AreEqual(5, collection.Count);
Assert.AreEqual(13.11m, collection[0].GetColumn("OrderTotal"));
}
public void FromClause()
{
OrderCollection collection = new OrderCollection();
collection.es.Connection.Name = "ForeignKeyTest";
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
//case "EntitySpaces.SqlServerCeProvider":
// Assert.Ignore("Not supported.");
// break;
default:
OrderQuery oq = new OrderQuery("o");
OrderItemQuery oiq = new OrderItemQuery("oi");
// The inner Select contains an aggregate,
// which requires a GroupBy for each non-aggregate in the Select.
// The outer Select includes the aggregate from the inner Select,
// plus columns where no GroupBy was desired.
oq.Select(oq.CustID, oq.OrderDate, oiq.UnitPrice);
oq.From
(
oiq.Select(oiq.OrderID, oiq.UnitPrice.Sum()).GroupBy(oiq.OrderID)
).As("sub");
oq.InnerJoin(oq).On(oq.OrderID == oiq.OrderID);
oq.OrderBy(oq.OrderID.Ascending);
Assert.IsTrue(collection.Load(oq));
Assert.AreEqual(5, collection.Count);
Decimal up = Convert.ToDecimal(collection[0].GetColumn("UnitPrice"));
Assert.AreEqual(5.11m, Math.Round(up, 2));
break;
}
}
public void SingleUnionWithSubSelect()
{
OrderCollection coll = new OrderCollection();
coll.es.Connection.Name = "ForeignKeyTest";
switch (coll.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SqlServerCeProvider":
case "EntitySpaces.SqlServerCe4Provider":
Assert.Ignore("Scalar SubSelects are not supported in SqlCe.");
break;
default:
OrderQuery oq1 = new OrderQuery("o1");
OrderItemQuery oiq1 = new OrderItemQuery("oi1");
oq1.Select
(
oq1.OrderID,
oq1.OrderDate,
oiq1.Select(
oiq1.UnitPrice.Max())
.Where(oq1.OrderID == oiq1.OrderID).As("MaxUnitPrice")
);
oq1.Where(oq1.OrderDate.Between(Convert.ToDateTime("2005-01-01"), Convert.ToDateTime("2005-12-31")));
OrderQuery oq2 = new OrderQuery("o2");
OrderItemQuery oiq2 = new OrderItemQuery("oi2");
oq2.Select
(
oq2.OrderID,
oq2.OrderDate,
oiq2.Select(
oiq2.UnitPrice.Max())
.Where(oq2.OrderID == oiq2.OrderID).As("MaxUnitPrice")
);
oq2.Where(oq2.OrderDate.Between(Convert.ToDateTime("2004-01-01"), Convert.ToDateTime("2004-12-31")));
oq1.Union(oq2);
oq1.OrderBy(oq1.OrderID.Ascending);
//string lq = cq1.Parse();
Assert.IsTrue(coll.Load(oq1));
Assert.AreEqual(6, coll.Count);
Assert.AreEqual(3m, coll[0].GetColumn("MaxUnitPrice"));
break;
}
}
