SQL document queries don't work on older existing tenants

[sarahelsaig]

Describe the bug

The query Liquid filter (and SqlQuerySource in general) doesn't work with content item queries in certain databases. Only older/legacy databases are affected, for example I was able to reproduce this problem in two DotNest tenants (using the OC 2.0 branch of DotNest) that iirc were created in 2021 and 2022 respectively. Newer tenants are not affected. The problem is that in these databases the document ID column is int instead of bigint, which is the case for newer tenants:

This was fine in 1.8, but became a problem after #16234 where the assumption that the document ID column must be long is hard coded.

Orchard Core version

Using the latest main branch at the time of writing,

To Reproduce

Steps to reproduce the behavior:

1. Find an older tenant or somehow create an SQL Server DB with int document IDs. Below for convenience I will also assume the site has BlogPost content items, like when running the Blog recipe.
2. Go to Admin > Serach > Queries > All Queries.
3. "Add Query", select "SQL" and fill:
   • Name: BlogPosts
   • Schema: leave empty
   • Return Documents: checked
   • Query: select DocumentId from ContentItemIndex where Published = true and Latest = true and ContentType = 'BlogPost'
4. Create new content type "Query" with a Liquid part.
5. Create new Query content item with the following Liquid:

<h1>Query Test</h1>
<code>
{% assign posts = Queries.BlogPosts | query %}
{% for post in posts %}
- {{ post.DisplayText }}<br />
{% endfor %}
<code>

6. View the content item, observe that no blog post title is listed.

Expected behavior

The query should return expected results. Note that the same query actually returns results if you uncheck the "Return Documents" box.

Logs and screenshots

If applicable, add log files, browser console logs, and screenshots (or screen recording videos) to help explain your problem.

[Piedone]

A workaround, or possible solution, is to set the YesSql ID column type from appsettings for these tenants.

[github-actions]

We triaged this issue and set the milestone according to the priority we think is appropriate (see the docs on how we triage and prioritize issues).

This indicates when the core team may start working on it. However, if you'd like to contribute, we'd warmly welcome you to do that anytime. See our guide on contributions here.

[sarahelsaig]

A workaround, or possible solution, is to set the YesSql ID column type from appsettings for these tenants.

Does that affect an already deployed DB? I thought it only helps with creating new tables in the future. (also note that the SQL Query source uses Dapper directly, not via YesSql)