static private void OverAndAliasQuery()
{
esAlias aliasCompany = null, aliasPeriod = null, aliasAmount = null, aliasItemCount = null;
OrdersCollection coll = new OrdersQuery("q", out var q)
.From <OrdersQuery>(out var sub, () => // mimic a CTE
{
// Nested Query
return(new OrdersQuery("o", out var o)
.InnerJoin <CustomersQuery>("c", out var c).On(c.CustomerID == o.CustomerID)
.InnerJoin <OrderDetailsQuery>("od", out var od).On(od.OrderID == o.OrderID)
.Select
(
// We're going to grab the aliased columns here for re-use in the outer query later
o.Count().As("TotalItems", out aliasItemCount),
c.CompanyName.As("CompanyName", out aliasCompany),
o.OrderDate.DatePart("year").As("Period", out aliasPeriod),
((1.00M - od.Discount) * od.UnitPrice * od.Quantity).Cast(esCastType.Decimal, 19, 2).Sum().Round(2).As("Amount", out aliasAmount)
)
.GroupBy(c.CompanyName, o.OrderDate.DatePart("year")));
}).As("sub")
// Now act on "sub" query columns
.Select(
aliasCompany(), aliasPeriod(), aliasAmount(), aliasItemCount(),
q.Over.Sum(aliasAmount()).PartitionBy(aliasCompany()).OrderBy(aliasPeriod().Ascending).Rows.UnBoundedPreceding.As("CumulativeAmount"),
q.Over.Sum(aliasAmount()).PartitionBy(aliasCompany()).As("TotalAmount")
)
.OrderBy(aliasCompany().Ascending, aliasPeriod().Ascending)
.ToCollection <OrdersCollection>();
if (coll.Count > 0)
{
// we loaded data
}
}
internal esQueryItem As(string alias, out esAlias aliasFunc)
{
aliasFunc = () =>
{
esQueryItem aliasedItem = new esQueryItem(this.query, alias, esSystemType.Unassigned);
aliasedItem.Column.IsOutVar = true;
return(aliasedItem);
};
_alias = alias;
return(aliasFunc());
}
static private void OverAndAliasQuerySimple()
{
esAlias rowNumber = null;
OrdersCollection coll = new OrdersQuery("o", out var o)
.From <OrderDetailsQuery>(out var od, () =>
{
// Nested Query
return(new OrderDetailsQuery("od", out var subQuery)
.Select
(
subQuery.Over.RowNumber().OrderBy(subQuery.OrderID.Descending).As("RowNumber", out rowNumber)
)
.GroupBy(subQuery.OrderID));
}).As("sub")
.Select(rowNumber())
.Where(rowNumber() > 5)
.ToCollection <OrdersCollection>();
if (coll.Count > 0)
{
// Then we loaded at least one record
}
}
public IOverClause As(string alias, out esAlias aliasedItem)
{
this.overClause.As(alias, out aliasedItem);
return(this.overClause);
}
