public virtual void Union()
{
SqlUnion union = new SqlUnion();
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn(SqlExpression.Raw("price * 10"), "priceX10"));
query.FromClause.BaseTable = FromTerm.Table("products");
union.Add(query);
query = new SelectQuery();
query.Columns.Add(new SelectColumn(SqlExpression.Field("price"), "priceX10"));
query.FromClause.BaseTable = FromTerm.Table("products");
union.Add(query, DistinctModifier.All);
query = new SelectQuery();
query.Columns.Add(new SelectColumn(SqlExpression.Field("price"), "priceX10"));
query.FromClause.BaseTable = FromTerm.Table("products");
union.Add(query, DistinctModifier.Distinct);
string sql = Renderer.RenderUnion(union);
Console.WriteLine(sql);
RunSql(sql);
}
/// <summary></summary>
public Join(FromTerm leftTable, FromTerm rightTable, WhereClause conditions, JoinType type)
{
LeftTable = leftTable;
RightTable = rightTable;
Conditions = conditions;
Type = type;
}
/// <summary>
/// Generates a unique reference to an attribute-value pair table
/// </summary>
/// <param name="tableName">The name of the attribute-value pair table</param>
/// <returns>A new instance of the AVP table FromTerm</returns>
public FromTerm GetNextAVPTableReference(string tableName)
{
FromTerm term = FromTerm.Table(tableName, "tb" + ++this.currentAttributeTableNumber, "[dbo]");
this.attributeTables.Add(term);
return(term);
}
/// <summary>Описание конструкции SELECT ... FROM</summary>
public static From Union(Union union, string alias)
{
From from = new From();
from.Term = FromTerm.Union(Qb.GetQueryObject(union), alias);
return(from);
}
/// <summary>Описание конструкции SELECT ... FROM</summary>
public static From SubQuery(string subQuery, string alias)
{
From from = new From();
from.Term = FromTerm.SubQuery(subQuery, alias);
return(from);
}
public virtual void RawSql()
{
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn(SqlExpression.Raw("price * 10"), "priceX10"));
query.FromClause.BaseTable = FromTerm.Table("products");
RenderSelect(query);
}
/// <summary>Аналог конструкции SELECT ... FROM</summary>
public Select From(string tableName)
{
if (this.Query.FromClause.BaseTable != null)
{
throw new InvalidQueryException(SR.Err_RepeatFrom);
}
this.Query.FromClause.BaseTable = FromTerm.Table(tableName);
return(this);
}
/// <summary>
/// Creates a FromTerm which represents a sub-query.
/// </summary>
/// <param name="query">A SelectQuery instance representing the sub query</param>
/// <param name="alias">term alias</param>
/// <returns>A FromTerm which represents a sub-query.</returns>
public static FromTerm SubQuery(SelectQuery query, string alias)
{
FromTerm term = new FromTerm();
term.expr = query;
term.alias = alias;
term.type = FromTermType.SubQueryObj;
return(term);
}
//[ExpectedException(typeof(InvalidQueryException))]
public void PagingNoOrder()
{
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name"));
query.FromClause.BaseTable = FromTerm.Table("customers");
Assert.Throws <InvalidQueryException>(() => RenderPage(0, 2, 3, query));
}
public virtual void SimpleSelect()
{
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name"));
query.FromClause.BaseTable = FromTerm.Table("customers");
RenderSelect(query);
}
public virtual void Pivot1()
{
SelectQuery baseQuery = new SelectQuery();
baseQuery.Columns.Add(new SelectColumn("*"));
baseQuery.FromClause.BaseTable = FromTerm.Table("orders");
PivotTable pivot = new PivotTable();
SetupPivot(pivot);
pivot.BaseQuery = baseQuery;
pivot.Function = SqlAggregationFunction.Sum;
pivot.ValueField = "quantaty";
pivot.RowField = "customerId";
PivotColumn pivotCol = new PivotColumn("date", SqlDataType.Date);
TimePeriod currentYear = TimePeriod.FromToday(TimePeriodType.Year);
pivotCol.Values.Add(PivotColumnValue.CreateRange("PreviousYears", new Range(null, currentYear.Add(-1).PeriodStartDate)));
pivotCol.Values.Add(PivotColumnValue.CreateRange("LastYear", new Range(currentYear.Add(-1).PeriodStartDate, currentYear.PeriodStartDate)));
pivotCol.Values.Add(PivotColumnValue.CreateRange("FollowingYears", new Range(currentYear.PeriodStartDate, null)));
pivot.Columns.Add(pivotCol);
pivotCol = new PivotColumn("productId", SqlDataType.Number);
pivotCol.Values.Add(PivotColumnValue.CreateScalar("product1", 1));
pivotCol.Values.Add(PivotColumnValue.CreateRange("product2", new Range(2, 3)));
pivot.Columns.Add(pivotCol);
SelectQuery pivotQuery = pivot.BuildPivotSql();
DataTable data = Fill(pivotQuery);
if (data == null)
{
return;
}
WriteTable(data);
Console.WriteLine("Drill down");
SelectQuery drillDownQuery = pivot.BuildDrillDownSql(SqlConstant.Number(1), "LastYear");
data = Fill(drillDownQuery);
if (data == null)
{
return;
}
WriteTable(data);
Console.WriteLine("Drill down");
drillDownQuery = pivot.BuildDrillDownSql(null, "LastYear");
data = Fill(drillDownQuery);
if (data == null)
{
return;
}
WriteTable(data);
}
public virtual void TableSpace()
{
SelectQuery query = new SelectQuery();
query.TableSpace = "sqlom.dbo";
query.Columns.Add(new SelectColumn("name"));
query.FromClause.BaseTable = FromTerm.Table("customers", "t");
RenderSelect(query);
}
public virtual void Join4()
{
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("*"));
query.FromClause.BaseTable = FromTerm.Table("customers");
query.FromClause.Join(JoinType.Cross, query.FromClause.BaseTable, FromTerm.Table("products"));
RenderSelect(query);
}
/// <summary>
/// Creates a FromTerm which represents a database table or view.
/// </summary>
/// <param name="tableName">Name of the table or view</param>
/// <param name="alias">Alias of the FromTerm</param>
/// <param name="ns1">First table namespace</param>
/// <param name="ns2">Second table namespace</param>
/// <returns>A FromTerm which represents a database table or view</returns>
/// <remarks>Use the <paramref name="ns1"/> parameter to set table database and <paramref name="ns2"/> to set table owner.</remarks>
public static FromTerm Table(string tableName, string alias, string ns1, string ns2)
{
FromTerm term = new FromTerm();
term.expr = tableName;
term.alias = alias;
term.type = FromTermType.Table;
term.ns1 = ns1;
term.ns2 = ns2;
return(term);
}
public virtual void TableSpace2()
{
SelectQuery query = new SelectQuery();
query.TableSpace = "foo.bar";
query.Columns.Add(new SelectColumn("*"));
query.FromClause.BaseTable = FromTerm.Table("customers", "t1", "sqlom", "dbo");
query.FromClause.Join(JoinType.Inner, query.FromClause.BaseTable, FromTerm.Table("customers", "t2", "dbo"), "customerId", "customerId");
RenderSelect(query);
}
/// <summary>
/// Renders a row count SELECT statement.
/// </summary>
/// <param name="query">Query definition to count rows for</param>
/// <returns>Generated SQL statement</returns>
/// <remarks>
/// Renders a SQL statement which returns a result set with one row and one cell which contains the number of rows <paramref name="query"/> can generate.
/// The generated statement will work nicely with <see cref="System.Data.IDbCommand.ExecuteScalar"/> method.
/// </remarks>
public override string RenderRowCount(SelectQuery query)
{
string baseSql = RenderSelect(query, false);
SelectQuery countQuery = new SelectQuery();
SelectColumn col = new SelectColumn("*", null, "cnt", SqlAggregationFunction.Count);
countQuery.Columns.Add(col);
countQuery.FromClause.BaseTable = FromTerm.SubQuery(baseSql, "t");
return(RenderSelect(countQuery));
}
/// <summary>
/// Creates a SelectColumn with a column name, table, column alias and optional aggregation function
/// </summary>
/// <param name="columnName">Name of a column</param>
/// <param name="table">The table this field belongs to</param>
/// <param name="columnAlias">Alias of the column</param>
/// <param name="function">Aggregation function to be applied to the column. Use SqlAggregationFunction.None to specify that no function should be applied.</param>
public SelectColumn(string columnName, FromTerm table, string columnAlias, SqlAggregationFunction function)
{
if (function == SqlAggregationFunction.None)
{
expr = SqlExpression.Field(columnName, table);
}
else
{
expr = SqlExpression.Function(function, SqlExpression.Field(columnName, table));
}
this.alias = columnAlias;
}
public virtual void Paging2()
{
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name"));
query.FromClause.BaseTable = FromTerm.Table("customers");
query.OrderByTerms.Add(new OrderByTerm("name", null, OrderByDirection.Descending));
query.OrderByTerms.Add(new OrderByTerm("birthDate", null, OrderByDirection.Ascending));
int count = RenderRowCount(query);
RenderPage(1, 2, count, query);
}
public virtual void Join3()
{
FromTerm tProducts = FromTerm.Table("products", "p");
FromTerm tOrders = FromTerm.Table("orders", "o");
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tProducts));
query.Columns.Add(new SelectColumn("date", tOrders));
query.FromClause.BaseTable = tOrders;
query.FromClause.Join(JoinType.Right, query.FromClause.BaseTable, tProducts, "productId", "productId");
RenderSelect(query);
}
public virtual void ComplicatedQuery()
{
FromTerm tCustomers = FromTerm.Table("customers", "c");
FromTerm tProducts = FromTerm.Table("products", "p");
FromTerm tOrders = FromTerm.Table("orders", "o");
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tCustomers));
query.Columns.Add(new SelectColumn(SqlExpression.IfNull(SqlExpression.Field("name", tCustomers), SqlExpression.Constant(SqlConstant.String("name"))), "notNull"));
query.Columns.Add(new SelectColumn(SqlExpression.Null(), "nullValue"));
query.Columns.Add(new SelectColumn("name", tProducts, "productName", SqlAggregationFunction.None));
query.Columns.Add(new SelectColumn("price", tProducts));
query.FromClause.BaseTable = tCustomers;
query.FromClause.Join(JoinType.Left, tCustomers, tOrders, "customerId", "customerId");
query.FromClause.Join(JoinType.Inner, tOrders, tProducts, "productId", "productId");
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("name", tCustomers), SqlExpression.String("John"), CompareOperator.Equal));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.String("Dohe"), SqlExpression.Field("name", tCustomers), CompareOperator.NotEqual));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("name", tCustomers), SqlExpression.String("J%"), CompareOperator.Like));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Date(DateTime.Now), SqlExpression.Field("date", tOrders), CompareOperator.Greater));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(10), SqlExpression.Number(9), CompareOperator.Greater));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(10), SqlExpression.Number(9), CompareOperator.GreaterOrEqual));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(10), SqlExpression.Number(11.5), CompareOperator.LessOrEqual));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(1), SqlExpression.Number(1), CompareOperator.BitwiseAnd));
WhereClause group = new WhereClause(WhereClauseRelationship.Or);
group.Terms.Add(WhereTerm.CreateBetween(SqlExpression.Field("price", tProducts), SqlExpression.Number(1), SqlExpression.Number(10)));
group.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field("name", tProducts), SqlConstantCollection.FromList(new string[] { "Nail", "Hamer", "Skrewdriver" })));
group.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field("name", tProducts), "select name from products"));
group.Terms.Add(WhereTerm.CreateNotIn(SqlExpression.Field("name", tProducts), SqlConstantCollection.FromList(new string[] { "Unkown" })));
group.Terms.Add(WhereTerm.CreateNotIn(SqlExpression.Field("name", tProducts), "select name from products"));
group.Terms.Add(WhereTerm.CreateIsNull(SqlExpression.Field("name", tProducts)));
group.Terms.Add(WhereTerm.CreateIsNotNull(SqlExpression.Field("name", tProducts)));
group.Terms.Add(WhereTerm.CreateExists("select productId from products"));
group.Terms.Add(WhereTerm.CreateNotExists("select productId from products"));
query.WherePhrase.SubClauses.Add(group);
query.OrderByTerms.Add(new OrderByTerm("name", tCustomers, OrderByDirection.Descending));
query.OrderByTerms.Add(new OrderByTerm("price", OrderByDirection.Ascending));
query.Distinct = true;
query.Top = 10;
RenderSelect(query);
}
public virtual void Join2()
{
FromTerm tCustomers = FromTerm.Table("customers");
FromTerm tProducts = FromTerm.Table("products", "p");
FromTerm tOrders = FromTerm.Table("orders", "o");
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tCustomers));
query.Columns.Add(new SelectColumn("name", tProducts));
query.FromClause.BaseTable = tCustomers;
query.FromClause.Join(JoinType.Left, tCustomers, tOrders, new JoinCondition("customerId"));
query.FromClause.Join(JoinType.Left, tOrders, tProducts, new JoinCondition("productId", "productId"));
RenderSelect(query);
}
/// <summary>
/// Renders a SELECT statement
/// </summary>
/// <param name="query">Query definition</param>
/// <returns>Generated SQL statement</returns>
public override string RenderSelect(SelectQuery query)
{
if (query.Top > -1 && query.OrderByTerms.Count > 0)
{
string baseSql = RenderSelect(query, -1);
SelectQuery countQuery = new SelectQuery();
SelectColumn col = new SelectColumn("*");
countQuery.Columns.Add(col);
countQuery.FromClause.BaseTable = FromTerm.SubQuery(baseSql, "t");
return(RenderSelect(countQuery, query.Top));
}
else
{
return(RenderSelect(query, query.Top));
}
}
/// <summary>
/// Builds the query
/// </summary>
/// <param name="whereClause">The WHERE conditions</param>
/// <param name="maxResults">The number of results to return</param>
/// <returns>The constructed SQL query</returns>
private string BuildQuery(WhereClause whereClause, int maxResults)
{
SelectQuery query = new SelectQuery();
AcmaSchemaAttribute objectIdAttribute = ActiveConfig.DB.GetAttribute("objectId");
FromTerm objectBaseTable = objectIdAttribute.DBTable;
query.Columns.Add(new SelectColumn("*", objectBaseTable));
query.Distinct = true;
query.FromClause.BaseTable = objectBaseTable;
foreach (FromTerm term in this.attributeTables)
{
query.FromClause.Join(JoinType.Left, objectBaseTable, term, new JoinCondition("objectId", "objectId"));
}
if (maxResults > 0)
{
query.Top = maxResults;
}
//if (this.parameters.Count == 0)
//{
// return null;
//}
// Prevents returning the querying object as a search result
if (Guid.Empty != this.SourceObjectId)
{
string param = this.AddParameter(this.SourceObjectId);
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(objectIdAttribute.Name, objectIdAttribute.DBTable), SqlExpression.Parameter(param), CompareOperator.NotEqual));
}
if (this.OrderByTerms != null)
{
query.OrderByTerms.AddRange(this.OrderByTerms);
}
query.WherePhrase.SubClauses.Add(whereClause);
SqlServerRenderer renderer = new SqlServerRenderer();
return(renderer.RenderSelect(query));
}
/// <summary>
/// Renders a single FROM term
/// </summary>
/// <param name="builder"></param>
/// <param name="table"></param>
/// <param name="tableSpace">Common prefix for all tables in the term</param>
protected virtual void RenderFromTerm(StringBuilder builder, FromTerm table, string tableSpace)
{
if (table.Type == FromTermType.Table)
{
if (table.Ns1 != null)
{
TableNamespace(builder, table.Ns1);
}
if (table.Ns2 != null)
{
TableNamespace(builder, table.Ns2);
}
if (table.Ns1 == null && table.Ns2 == null && tableSpace != null)
{
TableNamespace(builder, tableSpace);
}
Identifier(builder, (string)table.Expression);
}
else if (table.Type == FromTermType.SubQuery)
{
builder.AppendFormat("( {0} )", table.Expression);
}
else if (table.Type == FromTermType.SubQueryObj)
{
builder.AppendFormat("( {0} )", RenderSelect((SelectQuery)table.Expression));
}
else
{
throw new InvalidQueryException("Unknown FromExpressionType: " + table.Type.ToString());
}
if (table.Alias != null)
{
builder.AppendFormat(" ");
if (Prealias != string.Empty)
{
builder.AppendFormat("as ");
}
Identifier(builder, table.Alias);
}
}
public virtual void Join5()
{
FromTerm tCustomers = FromTerm.Table("customers");
FromTerm tProducts = FromTerm.Table("products", "p");
FromTerm tOrders = FromTerm.Table("orders", "o");
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tCustomers));
query.Columns.Add(new SelectColumn("name", tProducts));
query.FromClause.BaseTable = tCustomers;
query.FromClause.Join(JoinType.Left, tCustomers, tOrders, new JoinCondition("customerId"), new JoinCondition("customerId"));
WhereClause condition = new WhereClause(WhereClauseRelationship.Or);
condition.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("productId", tOrders), SqlExpression.Field("productId", tProducts), CompareOperator.Equal));
condition.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("orderId", tOrders), SqlExpression.Field("productId", tProducts), CompareOperator.Equal));
query.FromClause.Join(JoinType.Left, tOrders, tProducts, condition);
RenderSelect(query);
}
public virtual void Paging4()
{
FromTerm tCustomers = FromTerm.Table("customers");
FromTerm tProducts = FromTerm.Table("products", "p");
FromTerm tOrders = FromTerm.Table("orders", "o");
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tCustomers, "customerName"));
query.Columns.Add(new SelectColumn("name", tProducts, "productName"));
query.FromClause.BaseTable = tCustomers;
query.FromClause.Join(JoinType.Inner, query.FromClause.BaseTable, tOrders, "customerId", "customerId");
query.FromClause.Join(JoinType.Inner, tOrders, tProducts, "productId", "productId");
query.OrderByTerms.Add(new OrderByTerm("name", tCustomers, OrderByDirection.Descending));
int count = RenderRowCount(query);
RenderPage(0, 2, count, query);
}
public virtual void SubQuery()
{
FromTerm tCustomers = FromTerm.Table("customers");
SelectQuery subQuery = new SelectQuery();
subQuery.Top = 1;
subQuery.Columns.Add(new SelectColumn("name", tCustomers));
subQuery.FromClause.BaseTable = FromTerm.Table("customers");
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tCustomers));
query.Columns.Add(new SelectColumn(SqlExpression.SubQuery("select count(*) from customers"), "cnt"));
query.Columns.Add(new SelectColumn(SqlExpression.SubQuery(subQuery), "subq"));
query.FromClause.BaseTable = tCustomers;
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("customerId", tCustomers), SqlExpression.SubQuery("select customerId from customers where name='John'"), CompareOperator.Equal));
RenderSelect(query);
}
public virtual void GroupByWithCube()
{
FromTerm tCustomers = FromTerm.Table("customers");
FromTerm tProducts = FromTerm.Table("products", "p");
FromTerm tOrders = FromTerm.Table("orders", "o");
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tCustomers));
query.Columns.Add(new SelectColumn("price", tProducts, "sum", SqlAggregationFunction.Sum));
query.FromClause.BaseTable = tCustomers;
query.FromClause.Join(JoinType.Inner, query.FromClause.BaseTable, tOrders, "customerId", "customerId");
query.FromClause.Join(JoinType.Inner, tOrders, tProducts, "productId", "productId");
query.GroupByTerms.Add(new GroupByTerm("name", tCustomers));
query.GroupByWithCube = true;
RenderSelect(query);
}
public virtual void Having()
{
FromTerm tCustomers = FromTerm.Table("customers");
FromTerm tProducts = FromTerm.Table("products", "p");
FromTerm tOrders = FromTerm.Table("orders", "o");
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tCustomers));
query.Columns.Add(new SelectColumn("price", tProducts, "sum", SqlAggregationFunction.Sum));
query.FromClause.BaseTable = tCustomers;
query.FromClause.Join(JoinType.Inner, query.FromClause.BaseTable, tOrders, "customerId", "customerId");
query.FromClause.Join(JoinType.Inner, tOrders, tProducts, "productId", "productId");
query.GroupByTerms.Add(new GroupByTerm("name", tCustomers));
query.HavingPhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("name", tCustomers), SqlExpression.String("John"), CompareOperator.Equal));
RenderSelect(query);
}
public virtual void Parameter()
{
FromTerm tCustomers = FromTerm.Table("customers");
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tCustomers));
query.FromClause.BaseTable = tCustomers;
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Parameter(GetParameterName("pName")), SqlExpression.Field("name", tCustomers), CompareOperator.Equal));
string sql = Renderer.RenderSelect(query);
Console.WriteLine(sql);
if (connection != null)
{
IDbCommand command = connection.CreateCommand();
command.CommandText = sql;
command.Parameters.Add(CreateParameter("@pName", "John"));
command.ExecuteNonQuery();
}
}
/// <summary>
/// Renders a single FROM term
/// </summary>
/// <param name="builder"></param>
/// <param name="table"></param>
/// <param name="tableSpace">Common prefix for all tables in the term</param>
protected virtual void RenderFromTerm(StringBuilder builder, FromTerm table, string tableSpace)
{
if (table.Type == FromTermType.Table)
{
if (table.Ns1 != null)
{
TableNamespace(builder, table.Ns1);
}
if (table.Ns2 != null)
{
TableNamespace(builder, table.Ns2);
}
if (table.Ns1 == null && table.Ns2 == null && tableSpace != null)
{
TableNamespace(builder, tableSpace);
}
Identifier(builder, (string) table.Expression);
}
else if (table.Type == FromTermType.SubQuery)
{
builder.AppendFormat("( {0} )", table.Expression);
}
else if (table.Type == FromTermType.SubQueryObj)
{
builder.AppendFormat("( {0} )", RenderSelect((SelectQuery) table.Expression));
}
else
{
throw new InvalidQueryException("Unknown FromExpressionType: " + table.Type.ToString());
}
if (table.Alias != null)
{
builder.AppendFormat(" ");
if (Prealias != string.Empty)
{
builder.AppendFormat("as ");
}
Identifier(builder, table.Alias);
}
}
private void ApplyOrderBy(OrderByTermCollection terms, SelectQuery orderQuery, bool forward, FromTerm table)
{
foreach (OrderByTerm expr in terms)
{
OrderByDirection dir = expr.Direction;
//Reverse order direction if required
if (!forward && dir == OrderByDirection.Ascending)
{
dir = OrderByDirection.Descending;
}
else if (!forward && dir == OrderByDirection.Descending)
{
dir = OrderByDirection.Ascending;
}
orderQuery.OrderByTerms.Add(new OrderByTerm(FormatSortFieldName(expr.Field.ToString()), table, dir));
}
}