Autoquery - Ormlite generated subquery problem

[alfadevel]

Issue Description:

Hi,

I have a problem with Autoquery OrmLite’s POCO’s references.

if i call my api /dorig, i have milions of record. The problem is that if i call api/dorig?Take=1 , it shold be quite fast, but its slow.

I analyzed the code and i discovered that the subqueries that Autoquery generate (for recover referenced type) NOT consider the limit(Take=1) that i set in the api.

the subquery in the second api, select all the records frorm the 'main' type, and this slow the performance down. and this is not correct, because in the second api with Take 1, must generate a subquery with LIMIT 1.

With debugging i found the point where the limt of subquery are cleared. the clearing operation, happens, on line 96.And unfortunately I can't bypass it. this function is in the file LoadList.cs https://github.com/ServiceStack/ServiceStack.OrmLite/blob/c85934ae1cfd729c698644ac011d2f81cfdd9252/src/ServiceStack.OrmLite/Support/LoadList.cs#L58

• ServiceStackVersion 6.1.0

Thank you

Reproduction:

Call an autoquery api with referenced typed object and analyse the query that Autoquery generate, to recover referenced type object, and you will see a subquery without 'limits'(such as Take = 1 or Order By)

Expected behavior

I expect a subqueries that consider the limits (such as Take 1) present in the main query.

[mythz]

The sub query's are for loading related references they can't be limited to 1 result.

To optimize for a single result I'd suggest creating a separate API that uses LoadSingleById instead.

[alfadevel]

Take 1 it was only an example, the same situation happens with take 10 or take 15.. with every 'Take'. The problem is always the subquery that select all records from 'main' type without LIMIT 15 or 10. the concept is that with Take 10 , also the subquery should use LIMIT 10, otherwise is too slow because ,the subquery used to recover referenced types select all the records from the 'main' type.

in this way my api very slow.

[mythz]

Some RDBMS's don't support Skip/Take/OrderBy in sub selects so the default implementation can't be changed and I expect you'll end up with incomplete loaded references but I've added the ability to change Reference SQL used in this commit by using a custom dialect provider that sub classes the dialect provider you're currently using:

public class My[Dialect]OrmLiteDialectProvider : [Dialect]OrmLiteDialectProviderBase
{
    public static My[Dialect]OrmLiteDialectProvider Instance = new();

    public override string GetRefSelfSql<From>(SqlExpression<From> refQ, ModelDefinition modelDef, 
            FieldDefinition refSelf, ModelDefinition refModelDef)
        {
            refQ.Select(this.GetQuotedColumnName(modelDef, refSelf));
            refQ.OrderBy().ClearLimits(); //clear any ORDER BY or LIMIT's in Sub Select's

            var subSqlRef = refQ.ToMergedParamsSelectStatement();

            var sqlRef = $"SELECT {GetColumnNames(refModelDef)} " +
                         $"FROM {GetQuotedTableName(refModelDef)} " +
                         $"WHERE {this.GetQuotedColumnName(refModelDef.PrimaryKey)} " +
                         $"IN ({subSqlRef})";

            if (OrmLiteConfig.LoadReferenceSelectFilter != null)
                sqlRef = OrmLiteConfig.LoadReferenceSelectFilter(refModelDef.ModelType, sqlRef);

            return sqlRef;
        }
}

Which your App can utilize instead with:

var dbFactory = new OrmLiteConnectionFactory(
    connectionString,  
    My[Dialect]OrmLiteDialectProvider.Instance);

This change is available from v6.4.1+ that's now available on MyGet.

[mythz]

Please also don't erase the issue template when opening priority issues here and ensure your GitHub user name is included in your accounts Support Page, thanks.