/// <summary>
/// Allows for Related Pages lookup using Ordering on non-MultpleDocumentQuery queries. The given Node must be on the "left" hand side in this case for ordering.
/// </summary>
/// <param name="baseQuery">The Base Document Query</param>
/// <param name="nodeGuid">The NodeGuid</param>
/// <param name="relationshipName">Name of the relationship. If not provided documents from all relationships will be retrieved.</param>
public static DocumentQuery <TDocument> InRelationWithOrder <TDocument>(this DocumentQuery <TDocument> baseQuery, Guid nodeGuid, string relationshipName = null) where TDocument : TreeNode, new()
{
// Get the RelationshipID and NodeID
int?RelationshipNameID = GetRelationshipNameID(relationshipName);
int?NodeID = GetNodeID(nodeGuid);
if (!NodeID.HasValue)
{
return(baseQuery);
}
// Add the Inner Join with proper alias formatting
if (RelationshipNameID.HasValue)
{
baseQuery.Source((QuerySource s) => s.InnerJoin(new QuerySourceTable("CMS_Relationship"), new WhereCondition("NodeID = RightNodeID").WhereEquals("RelationshipNameID", RelationshipNameID.Value).WhereEquals("LeftNodeID", NodeID.Value)));
}
else
{
baseQuery.Source((QuerySource s) => s.InnerJoin(new QuerySourceTable("CMS_Relationship"), new WhereCondition("NodeID = RightNodeID").WhereEquals("LeftNodeID", NodeID.Value)));
}
// add the by the Relationship Order
baseQuery.OrderBy("RelationshipOrder");
return(baseQuery);
}
/// <summary>
/// Allows for Related Pages lookup using Ordering on non-MultpleDocumentQuery queries. The given Node must be on the "left" hand side in this case for ordering.
/// </summary>
/// <param name="baseQuery">The Base Document Query</param>
/// <param name="nodeID">The NodeID</param>
/// <param name="relationshipName">Name of the relationship. If not provided documents from all relationships will be retrieved.</param>
public static DocumentQuery InRelationWithOrder(this DocumentQuery baseQuery, int nodeID, string relationshipName = null)
{
// Get the RelationshipID and NodeID
int?RelationshipNameID = GetRelationshipNameID(relationshipName);
// Add the Inner Join with proper alias formatting
if (RelationshipNameID.HasValue)
{
baseQuery.Source((QuerySource s) => s.InnerJoin(new QuerySourceTable("CMS_Relationship"), new WhereCondition("NodeID = RightNodeID").WhereEquals("RelationshipNameID", RelationshipNameID.Value).WhereEquals("LeftNodeID", nodeID)));
}
else
{
baseQuery.Source((QuerySource s) => s.InnerJoin(new QuerySourceTable("CMS_Relationship"), new WhereCondition("NodeID = RightNodeID").WhereEquals("LeftNodeID", nodeID)));
}
// add the by the Relationship Order
baseQuery.OrderBy("RelationshipOrder");
return(baseQuery);
}
/// <summary> /// Returns the <see cref="DocumentQuery"/> ordered by <see cref="TreeNode.NodeOrder"/> /// </summary> /// <typeparam name="TNode"></typeparam> /// <param name="query">The current DocumentQuery</param> /// <returns></returns> public static DocumentQuery <TNode> OrderByNodeOrder <TNode>(this DocumentQuery <TNode> query) where TNode : TreeNode, new() => query.OrderBy(nameof(TreeNode.NodeOrder));
public void TestOrderByTranslation()
{
DocumentClient client = TestCommon.CreateClient(true);
IOrderedQueryable <Family> families = new DocumentQuery <Family>(client, ResourceType.Document, typeof(Document), "//dbs/", null);
// Ascending
IQueryable query = from f in families
where f.Int == 5 && f.NullableInt != null
orderby f.IsRegistered
select f.FamilyId;
this.VerifyQueryTranslation(query.ToString(), "SELECT VALUE root[\"id\"] FROM root WHERE ((root[\"Int\"] = 5) AND (root[\"NullableInt\"] != null)) ORDER BY root[\"IsRegistered\"] ASC ");
query = families.Where(f => f.Int == 5 && f.NullableInt != null).OrderBy(f => f.IsRegistered).Select(f => f.FamilyId);
this.VerifyQueryTranslation(query.ToString(), "SELECT VALUE root[\"id\"] FROM root WHERE ((root[\"Int\"] = 5) AND (root[\"NullableInt\"] != null)) ORDER BY root[\"IsRegistered\"] ASC ");
query = from f in families
orderby f.FamilyId
select f;
this.VerifyQueryTranslation(query.ToString(), "SELECT * FROM root ORDER BY root[\"id\"] ASC ");
query = families.OrderBy(f => f.FamilyId);
this.VerifyQueryTranslation(query.ToString(), "SELECT * FROM root ORDER BY root[\"id\"] ASC ");
query = from f in families
orderby f.FamilyId
select f.FamilyId;
this.VerifyQueryTranslation(query.ToString(), "SELECT VALUE root[\"id\"] FROM root ORDER BY root[\"id\"] ASC ");
query = families.OrderBy(f => f.FamilyId).Select(f => f.FamilyId);
this.VerifyQueryTranslation(query.ToString(), "SELECT VALUE root[\"id\"] FROM root ORDER BY root[\"id\"] ASC ");
// Descending
query = from f in families
where f.Int == 5 && f.NullableInt != null
orderby f.IsRegistered descending
select f.FamilyId;
this.VerifyQueryTranslation(query.ToString(), "SELECT VALUE root[\"id\"] FROM root WHERE ((root[\"Int\"] = 5) AND (root[\"NullableInt\"] != null)) ORDER BY root[\"IsRegistered\"] DESC ");
query = families.Where(f => f.Int == 5 && f.NullableInt != null).OrderByDescending(f => f.IsRegistered).Select(f => f.FamilyId);
this.VerifyQueryTranslation(query.ToString(), "SELECT VALUE root[\"id\"] FROM root WHERE ((root[\"Int\"] = 5) AND (root[\"NullableInt\"] != null)) ORDER BY root[\"IsRegistered\"] DESC ");
query = from f in families
orderby f.FamilyId descending
select f;
this.VerifyQueryTranslation(query.ToString(), "SELECT * FROM root ORDER BY root[\"id\"] DESC ");
query = families.OrderByDescending(f => f.FamilyId);
this.VerifyQueryTranslation(query.ToString(), "SELECT * FROM root ORDER BY root[\"id\"] DESC ");
query = from f in families
orderby f.FamilyId descending
select f.FamilyId;
this.VerifyQueryTranslation(query.ToString(), "SELECT VALUE root[\"id\"] FROM root ORDER BY root[\"id\"] DESC ");
query = families.OrderByDescending(f => f.FamilyId).Select(f => f.FamilyId);
this.VerifyQueryTranslation(query.ToString(), "SELECT VALUE root[\"id\"] FROM root ORDER BY root[\"id\"] DESC ");
// orderby multiple expression is not supported yet
query = from f in families
orderby f.FamilyId, f.Int
select f.FamilyId;
try
{
query.ToString();
}
catch (Exception e)
{
Assert.IsTrue(e.Message.Contains("Method 'ThenBy' is not supported."));
}
}
