Orchardcore sql Return Documents

As an AI and Orchard Core expert, I can spot exactly what is causing this issue. It is a classic gotcha when working with custom SQL Queries and Liquid in Orchard Core.

The discrepancy isn't in your SQL syntax or your Liquid template. The difference lies in a single configuration toggle within the Orchard Core admin UI: the Return Documents checkbox.

Why This Happens

Even though both SQL queries look nearly identical and query the exact same table (ContentItemIndex), they are configured differently in the dashboard.

  1. RecentTecArticles (Return Documents is ON): When the Return Documents option is checked on a query, Orchard Core takes the DocumentId returned by your SQL query and automatically fetches the full JSON ContentItem from the underlying document database. Because the display_url Liquid filter receives a complete ContentItem object, it can successfully read the AutoroutePart and generate the friendly URL (e.g., /blog-technology-article/...).

  2. AllBusinessPlans (Return Documents is OFF): When the option is unchecked, Orchard Core returns the raw SQL row exactly as it exists in the database. You are passing a flat, dynamic SQL object (representing the columns of ContentItemIndex) to the display_url filter. Since it isn't a true ContentItem, the filter falls back to the default MVC route (/Contents/ContentItems/{ContentItemId}). Because it doesn't know how to extract just the ID from the dynamic object, it simply calls .ToString() on the entire row, resulting in the raw JSON payload being appended to your URL.

How to Fix It (The Easy Way)

To bring your AllBusinessPlans query in line with your technology articles, you just need to update the query settings:

  1. In your Orchard Core Admin Dashboard, go to Search -> Queries.
  2. Edit the AllBusinessPlans query.
  3. Scroll down below the SQL editor and check the Return Documents checkbox.
  4. Save the query.

Your Liquid template will immediately start outputting the correct friendly URLs.

Pro-Tip: How to Fix It (The High-Performance Way)

Checking "Return Documents" is the easiest fix, but it comes with a performance cost: Orchard Core has to deserialize the full JSON document for every row returned. If you are retrieving 50+ items and only need the URL, doing this is highly inefficient.

If you want to keep your application fast, leave Return Documents unchecked for both queries and modify your SQL to explicitly fetch the Autoroute path by joining the AutoroutePartIndex table:

Updated SQL Query (AllBusinessPlans):

SELECT A.Path 
FROM ContentItemIndex C
INNER JOIN AutoroutePartIndex A ON C.ContentItemId = A.ContentItemId
WHERE C.ContentType = 'BusinessPlan' AND C.Published = true 
ORDER BY C.DisplayText

Updated Liquid Template: Since your query now returns just the string path rather than an object, you don't need the display_url filter. You can prepend the base URL directly:

{% assign businessPlans = Queries.AllBusinessPlans | query %}
{% for plan in businessPlans %}
  {{ '"' | append: plan.Path | href }}
{% endfor %}

(Note: Using the href filter or the append_base_url filter ensures it resolves correctly from the root of your domain).