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 OneWhereSubtract()
{
OrderItemCollection collection = new OrderItemCollection();
collection.es.Connection.Name = "ForeignKeyTest";
OrderItemQuery oiq = new OrderItemQuery("oiq");
oiq.Select(oiq.OrderID, oiq.ProductID,
(oiq.UnitPrice - oiq.Discount).As("SomeDecimal"));
oiq.Where(oiq.UnitPrice - oiq.Discount < .2);
oiq.OrderBy(oiq.OrderID, esOrderByDirection.Ascending);
oiq.OrderBy(oiq.ProductID, esOrderByDirection.Ascending);
Assert.IsTrue(collection.Load(oiq));
decimal someDec = Convert.ToDecimal(collection[0].GetColumn("SomeDecimal"));
Assert.AreEqual(Convert.ToDecimal("0.10"), Math.Round(someDec, 2));
}
public void HavingWithSimpleExpression()
{
OrderItemCollection coll = new OrderItemCollection();
coll.es.Connection.Name = "ForeignKeyTest";
OrderItemQuery q = new OrderItemQuery();
//q.es2.Connection.Name = "ForeignKeyTest";
q.Select(q.OrderID, q.Quantity.Sum().As("TotalQty"));
q.Where(q.Discount.IsNull());
q.GroupBy(q.OrderID);
switch (coll.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SqlServerCeProvider":
case "EntitySpaces.SqlServerCe4Provider":
case "EntitySpaces.VistaDBProvider":
case "EntitySpaces.VistaDB4Provider":
q.Having(q.Quantity.Sum() > 100);
q.OrderBy("<TotalQty>", esOrderByDirection.Descending);
break;
case "EntitySpaces.SQLiteProvider":
q.Having((q.Quantity * 1).Sum() > 100);
q.OrderBy(q.Quantity.Sum().Descending);
break;
default:
q.Having(q.Quantity.Sum() > 100);
q.OrderBy(q.Quantity.Sum().Descending);
break;
}
Assert.IsTrue(coll.Load(q), "Load");
Assert.AreEqual(3, coll.Count, "Count");
int qty = Convert.ToInt32(coll[0].GetColumn("TotalQty"));
Assert.AreEqual(240, qty, "GetColumn");
}
public void OneModLiteral()
{
OrderItemCollection collection = new OrderItemCollection();
collection.es.Connection.Name = "ForeignKeyTest";
switch (collection.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.MSAccessProvider":
Assert.Ignore("Not supported");
break;
default:
OrderItemQuery oiq = new OrderItemQuery("oiq");
oiq.Select(oiq.OrderID, oiq.ProductID,
(oiq.Quantity % 2).As("SomeInteger"));
oiq.OrderBy(oiq.OrderID, esOrderByDirection.Ascending);
oiq.OrderBy(oiq.ProductID, esOrderByDirection.Ascending);
Assert.IsTrue(collection.Load(oiq));
decimal someInt = Convert.ToInt32(collection[0].GetColumn("SomeInteger"));
Assert.AreEqual(1, someInt);
break;
}
}
public void HavingWithComplexExpression()
{
OrderItemCollection coll = new OrderItemCollection();
coll.es.Connection.Name = "ForeignKeyTest";
OrderItemQuery q = new OrderItemQuery();
q.Select(q.OrderID, (q.Quantity * q.UnitPrice).Sum().As("TotalPrice"));
q.Where(q.Discount.IsNull());
q.GroupBy(q.OrderID);
q.Having((q.Quantity * q.UnitPrice).Sum() > 500);
switch (coll.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SqlServerCeProvider":
case "EntitySpaces.SqlServerCe4Provider":
case "EntitySpaces.VistaDBProvider":
case "EntitySpaces.VistaDB4Provider":
q.OrderBy("<TotalPrice>", esOrderByDirection.Descending);
break;
default:
q.OrderBy((q.Quantity * q.UnitPrice).Sum().Descending);
break;
}
Assert.IsTrue(coll.Load(q), "Load");
Assert.AreEqual(2, coll.Count, "Count");
decimal price = Convert.ToDecimal(coll[0].GetColumn("TotalPrice"));
Assert.AreEqual(1940.0M, price, "GetColumn");
}