cannot apply the specified columns kentico

cannot apply the specified columns kentico

Pagination in union query of two page type

The error has 2 specific messages in it:

• [SqlHelper.ReplaceColumns]: Missing ##COLUMNS## macro in the query text
• cannot apply the specified columns '*, ROW_NUMBER() OVER (ORDER BY [DocumentName]) AS [CMS_RN]'

There is also a spelling error in your code for the column name:

DataTable dt= resultQuery.OrderByDescending("DocumentPublihFrom").Page(1,50).Result.Tables[0];

Should be:

DataTable dt= resultQuery.OrderByDescending("DocumentPublishFrom").Page(1,50).Result.Tables[0];

Lastly, what code do these methods have in them and what is the input paramater value:

• getVideoQuery()
• getArticleQuery()

自己遇到的错误，

System.NotSupportedException: [SqlHelper.ReplaceColumns]: Missing ##COLUMNS## macro in the query text '
WITH AllData AS 
(
    SELECT *
FROM Edenred_CardholderCardSettings ccs
WHERE ##WHERE##
) 
SELECT  *, (SELECT COUNT(*) FROM AllData) AS [CMS_TOT] 
FROM AllData 
WHERE CMS_RN BETWEEN 1 AND 1000 
ORDER BY CMS_RN
', cannot apply the specified columns '*, ROW_NUMBER() OVER (ORDER BY [ItemID] ASC) AS [CMS_RN]'.
   at CMS.DataEngine.SqlMacroHelper.ReplaceColumns(String queryText, String columns)
   at CMS.DataEngine.SqlMacroHelper.ResolveMacrosInQueryText(QueryMacros queryMacros, String queryText)
   at CMS.DataEngine.SqlMacroHelper.ResolveQueryMacros(QueryMacros queryMacros, String queryText)
   at CMS.DataEngine.QueryParameters.ResolveMacrosInternal(Boolean updateQueryText)
   at CMS.DataEngine.GeneralConnection.ExecuteQuery(QueryParameters query)
   at CMS.DataEngine.GeneralConnection.ExecuteQuery(QueryParameters query, Int32& totalRecords)
   at CMS.DataEngine.DataQueryBase`1.GetDataFromDBInternal()
   at CMS.DataEngine.DataQueryBase`1.GetDataFromDB()
   at CMS.DataEngine.DataQueryBase`1.GetData()
   at CMS.DataEngine.DataQueryBase`1.get_Result()
   at CMS.DataEngine.ObjectQueryBase`2.GetResults(IDataQuery query, Int32& totalRecords)
   at CMS.DataEngine.DataQueryBase`1.GetDataFromDB()
   at CMS.DataEngine.DataQueryBase`1.GetData()
   at CMS.DataEngine.ObjectQueryBase`2.GetData()
   at CMS.DataEngine.DataQueryBase`1.get_Result()
   at CMS.DataEngine.InfoObjectCollection`1.GetData(IWhereCondition where, Int32 offset, Int32 maxRecords, Int32& totalRecords)
   at CMS.DataEngine.InfoObjectCollection`1.GetItemInternal(Int32 index)
   at CMS.DataEngine.InfoObjectCollection`1.GetItem(Int32 index)
   at CMS.DataEngine.InfoObjectCollection`1.get_InternalCount()
   at CMS.DataEngine.InfoObjectCollection`1.<GetEnumeratorInternal>d__234.MoveNext()
   at CMS.DataEngine.InfoObjectCollection`1.<GetEnumerator>d__233.MoveNext()

  var infos = new InfoObjectCollection(objectType);

                            var csi = infos.TypeInfo.ClassStructureInfo;
                            var orderByIndex = FindOrderByIndex(csi);
                            if (orderByIndex != null)
                            {
                                infos.OrderByColumns = orderByIndex.GetOrderBy();
                            }

                            infos.PageSize = 1000;

                            // Skip object types derived from general data class object type to avoid duplicities
                            if ((infos.TypeInfo.OriginalObjectType == DataClassInfo.OBJECT_TYPE) && (infos.TypeInfo.ObjectType != DataClassInfo.OBJECT_TYPE))
                            {
                                continue;
                            }

                            foreach (BaseInfo info in infos)

在foreach的时候遇到问题，怀疑是之前的赋值出问题了。

 if (orderByIndex != null)
                            {
                                infos.OrderByColumns = orderByIndex.GetOrderBy();
                            }

// CMS.DataEngine.InfoObjectCollection<TInfo>
/// <summary>
/// Gets the  enumerator for the collection
/// </summary>
// Token: 0x0600116B RID: 4459 RVA: 0x00030D4B File Offset: 0x0002FD4B
public IEnumerator<TInfo> GetEnumerator()
{
    IEnumerator<TInfo> baseEnum = this.GetEnumeratorInternal();
    while (baseEnum.MoveNext())
    {
        TInfo tinfo = baseEnum.Current;
        yield return tinfo;
    }
    yield break;
}

// CMS.DataEngine.InfoObjectCollection<TInfo>
/// <summary>
/// Gets the enumerator for the collection.
/// </summary>
// Token: 0x0600116C RID: 4460 RVA: 0x00030D5A File Offset: 0x0002FD5A
public IEnumerator<TInfo> GetEnumeratorInternal()
{
    try
    {
        object obj = this.lockObject;
        lock (obj)
        {
            this.mEnumerators++;
        }
        int internalCount = this.InternalCount;
        int num;
        for (int i = 0; i < internalCount; i = num + 1)
        {
            TInfo item = this.GetItem(i);
            if (item != null)
            {
                yield return item;
            }
            num = i;
        }
        if (this.mNewItems != null)
        {
            int newCount = this.mNewItems.Count;
            for (int j = 0; j < newCount; j = num + 1)
            {
                TInfo tinfo = this.mNewItems[j];
                if (tinfo != null && !this.IsRemoved(tinfo))
                {
                    yield return tinfo;
                }
                num = j;
            }
        }
    }
    finally
    {
        object obj2 = this.lockObject;
        lock (obj2)
        {
            int num2 = this.mEnumerators - 1;
            this.mEnumerators = num2;
            if (num2 <= 0)
            {
                if (this.mClearAfterEnumeration)
                {
                    this.Clear();
                    this.mClearAfterEnumeration = false;
                }
                this.mEnumerators = 0;
            }
        }
    }
    yield break;
    yield break;
}

// CMS.DataEngine.InfoObjectCollection<TInfo>
/// <summary>
/// Internal count of the items
/// </summary>
// Token: 0x1700043C RID: 1084
// (get) Token: 0x06001121 RID: 4385 RVA: 0x0002F639 File Offset: 0x0002E639
protected int InternalCount
{
    get
    {
        this.RegisterClearCacheCallback();
        if (this.mCount >= 0)
        {
            return this.mCount;
        }
        this.GetItem(0);
        if (this.mCount < 0)
        {
            this.mCount = 0;
        }
        return this.mCount;
    }
}

// CMS.DataEngine.InfoObjectCollection<TInfo>
/// <summary>
/// Gets the item on the specified index.
/// </summary>
/// <param name="index">Item index to get</param>
// Token: 0x0600115D RID: 4445 RVA: 0x0003085C File Offset: 0x0002F85C
protected TInfo GetItem(int index)
{
    this.RegisterClearCacheCallback();
    TInfo itemInternal = this.GetItemInternal(index);
    if (itemInternal != null)
    {
        itemInternal.Generalized.IsCachedObject = true;
    }
    return itemInternal;
}

// CMS.DataEngine.InfoObjectCollection<TInfo>
/// <summary>
/// Gets the item on the specified index.
/// </summary>
/// <param name="index">Item index to get</param>
// Token: 0x0600115E RID: 4446 RVA: 0x00030894 File Offset: 0x0002F894
private TInfo GetItemInternal(int index)
{
    if (index < 0)
    {
        index = this.InternalCount - index;
        if (index < 0)
        {
            return default(TInfo);
        }
    }
    if (this.mCount >= 0 && index >= this.mCount)
    {
        return default(TInfo);
    }
    TInfo tinfo = this.Items.ElementAtOrDefault(index);
    if (this.IsRemoved(tinfo))
    {
        return default(TInfo);
    }
    if (tinfo == null)
    {
        if (this.SourceData != null)
        {
            if (this.mCount < 0)
            {
                this.mCount = DataHelper.GetItemsCount(this.SourceData);
            }
            if (index >= this.mCount)
            {
                return default(TInfo);
            }
            DataRow dr = null;
            int i = 0;
            int num = index;
            while (i < this.SourceData.Tables.Count)
            {
                DataTable dataTable = this.SourceData.Tables[i];
                if (dataTable.Rows.Count > num)
                {
                    dr = dataTable.Rows[num];
                    break;
                }
                i++;
                num -= dataTable.Rows.Count;
            }
            tinfo = this.CreateNewObject(dr);
            this.EnsureItems(index + 1);
            this.Items[index] = tinfo;
        }
        else
        {
            int num2 = 0;
            int num3 = 0;
            if (this.mAllowPaging)
            {
                num2 = index / this.PageSize;
                num3 = this.PageSize;
            }
            else
            {
                this.mCount = 0;
            }
            IDisposable disposable = null;
            if (this.EnforceReadOnlyDataAccess)
            {
                disposable = new CMSTransactionScope(null);
            }
            DataSet data;
            using (disposable)
            {
                data = this.GetData(null, num2 * num3, num3, ref this.mCount);
            }
            if (!DataHelper.DataSourceIsEmpty(data))
            {
                int num4 = num2 * this.PageSize;
                foreach (object obj in data.Tables)
                {
                    foreach (object obj2 in ((DataTable)obj).Rows)
                    {
                        DataRow dr2 = (DataRow)obj2;
                        TInfo tinfo2 = this.CreateNewObject(dr2);
                        this.EnsureItems(num4 + 1);
                        List<TInfo> items = this.Items;
                        int index2 = num4;
                        TInfo value;
                        if ((value = this.Items[num4]) == null)
                        {
                            value = tinfo2;
                        }
                        items[index2] = value;
                        string objectName = this.GetObjectName(tinfo2);
                        if (!string.IsNullOrEmpty(objectName))
                        {
                            this[objectName] = tinfo2;
                        }
                        num4++;
                    }
                }
                if (!this.mAllowPaging)
                {
                    this.mCount = num4;
                }
            }
            tinfo = this.Items.ElementAtOrDefault(index);
        }
    }
    return tinfo;
}

// CMS.DataEngine.InfoObjectCollection<TInfo>
/// <summary>
/// Gets the data for the collection.
/// </summary>
/// <param name="where">Where condition</param>
/// <param name="offset">Starting offset for the data</param>
/// <param name="maxRecords">Maximum number of records to get</param>
/// <param name="totalRecords">Returning total number of records</param>
// Token: 0x06001141 RID: 4417 RVA: 0x0002FF10 File Offset: 0x0002EF10
protected virtual DataSet GetData(IWhereCondition where, int offset, int maxRecords, ref int totalRecords)
{
    if (this.IsDisconnected)
    {
        return null;
    }
    WhereCondition completeWhere = this.GetCompleteWhereCondition().Where(new IWhereCondition[]
    {
        where
    });
    IDataQuery dataQuery = this.Object.Generalized.GetDataQuery(this.UseObjectTypeCondition, delegate(DataQuerySettings s)
    {
        s.Where(new IWhereCondition[]
        {
            completeWhere
        }).OrderBy(new string[]
        {
            this.OrderByColumns
        }).TopN(this.TopN).Columns(new string[]
        {
            this.Columns
        });
    }, this.CheckLicense);
    dataQuery.IncludeBinaryData = this.LoadBinaryData;
    dataQuery.Offset = offset;
    dataQuery.MaxRecords = maxRecords;
    DataSet result = dataQuery.Result;
    totalRecords = dataQuery.TotalRecords;
    return result;
}

// CMS.DataEngine.DataQueryBase<TQuery>
/// <summary>
/// DataSet with the result
/// </summary>
// Token: 0x17000343 RID: 835
// (get) Token: 0x06000CBF RID: 3263 RVA: 0x00025E3C File Offset: 0x00024E3C
// (set) Token: 0x06000CC0 RID: 3264 RVA: 0x00025E94 File Offset: 0x00024E94
public DataSet Result
{
    get
    {
        if (!this.mDataLoaded)
        {
            object obj = this.lockObject;
            lock (obj)
            {
                if (!this.mDataLoaded)
                {
                    this.Execute();
                }
            }
        }
        return this.mResult;
    }
    protected set
    {
        this.mResult = value;
        this.mDataLoaded = (value != null);
    }
}

// CMS.DataEngine.DataQueryBase<TQuery>
/// <summary>
/// Executes the current query and returns it's results as a DataSet
/// </summary>
// Token: 0x06000D13 RID: 3347 RVA: 0x00026F26 File Offset: 0x00025F26
public DataSet Execute()
{
    this.mResult = this.GetData();
    this.mDataLoaded = true;
    return this.mResult;
}

// CMS.DataEngine.DataQueryBase<TQuery>
/// <summary>
/// Executes the query. Sets the total records number during execution.
/// </summary>
// Token: 0x06000D15 RID: 3349 RVA: 0x00026F4C File Offset: 0x00025F4C
protected virtual DataSet GetData()
{
    if (base.ReturnsNoResults)
    {
        this.TotalRecords = 0;
        return null;
    }
    object obj = this.lockObject;
    DataSet result;
    lock (obj)
    {
        result = (this.HasDataSource ? this.GetDataFromDataSource() : this.GetDataFromDB());
    }
    return result;
}

这里有一个判断，是从result = (this.HasDataSource ? this.GetDataFromDataSource() : this.GetDataFromDB());

// CMS.DataEngine.DataQueryBase<TQuery>
/// <summary>
/// Executes the query
/// </summary>
// Token: 0x06000D18 RID: 3352 RVA: 0x0002700C File Offset: 0x0002600C
protected virtual DataSet GetDataFromDB()
{
    IDataQuery executingQuery = this.GetExecutingQuery(null);
    if (executingQuery == null)
    {
        return null;
    }
    if (executingQuery == this)
    {
        return this.GetDataFromDBInternal();
    }
    int totalRecords = 0;
    DataSet results = this.GetResults(executingQuery, ref totalRecords);
    this.TotalRecords = totalRecords;
    return results;
}

// CMS.DataEngine.DataQueryBase<TQuery>
/// <summary>
/// Executes the current query and returns it's results as a DataSet
/// </summary>
// Token: 0x06000D19 RID: 3353 RVA: 0x00027044 File Offset: 0x00026044
private DataSet GetDataFromDBInternal()
{
    int totalRecords = 0;
    object obj = this.lockObject;
    DataSet result;
    lock (obj)
    {
        GeneralConnection connection = this.GetConnection();
        QueryParameters completeQueryParameters = this.GetCompleteQueryParameters(null);
        result = connection.ExecuteQuery(completeQueryParameters, ref totalRecords);
        this.TotalRecords = totalRecords;
    }
    return result;
}

// CMS.DataEngine.GeneralConnection
/// <summary>
/// Executes query and returns result as a DataSet. Returns the total number of result items.
/// </summary>
/// <param name="query">Query parameters</param>
/// <param name="totalRecords">Returns total records</param>
// Token: 0x0600184D RID: 6221 RVA: 0x00045C5C File Offset: 0x00044C5C
[HideFromDebugContext]
public virtual DataSet ExecuteQuery(QueryParameters query, ref int totalRecords)
{
    query = query.GetParametersForExecution(totalRecords != -2, false);
    DataSet dataSet = this.ExecuteQuery(query);
    totalRecords = 0;
    SqlHelper.ProcessPagedResults(dataSet, ref totalRecords);
    return dataSet;
}

// CMS.DataEngine.GeneralConnection
/// <summary>
/// Executes the query and returns the number of rows affected.
/// </summary>
/// <param name="query">Query parameters.</param>
// Token: 0x06001850 RID: 6224 RVA: 0x00045DEC File Offset: 0x00044DEC
public virtual int ExecuteNonQuery(QueryParameters query)
{
    query.ExecutionType = QueryExecutionTypeEnum.ExecuteNonQuery;
    IDataConnection dataConnection = this.GetExecutingConnection(query);
    int num;
    using (ExecuteQueryHandler<int> executeQueryHandler = SqlEvents.ExecuteNonQuery.StartEvent(query, dataConnection))
    {
        ExecuteQueryEventArgs<int> eventArguments = executeQueryHandler.EventArguments;
        dataConnection = eventArguments.Connection;
        num = eventArguments.Result;
        if (executeQueryHandler.CanContinue() && num == 0)
        {
            query.ResolveMacros();
            bool flag = false;
            bool flag2 = false;
            try
            {
                this.LogQueryStart(query, dataConnection);
                if (!dataConnection.IsOpen())
                {
                    dataConnection.Open();
                    flag = true;
                }
                if (query.RequiresTransaction && !dataConnection.IsTransaction())
                {
                    dataConnection.BeginTransaction();
                    flag2 = true;
                }
                num = dataConnection.ExecuteNonQuery(query.Text, query.Params, query.Type, false);
                eventArguments.Result = num;
                if (flag2)
                {
                    dataConnection.CommitTransaction();
                    flag2 = false;
                }
            }
            finally
            {
                if (flag2)
                {
                    dataConnection.RollbackTransaction();
                }
                if (flag)
                {
                    dataConnection.Close();
                }
                this.LogQueryEnd(num);
            }
        }
        executeQueryHandler.FinishEvent();
        num = executeQueryHandler.EventArguments.Result;
    }
    return num;
}

// CMS.DataEngine.QueryParameters
/// <summary>
/// Resolves the query macros in <see cref="P:CMS.DataEngine.QueryParameters.Text" /> property value.
/// </summary>
// Token: 0x06001216 RID: 4630 RVA: 0x00033216 File Offset: 0x00032216
public void ResolveMacros()
{
    this.ResolveMacrosInternal(true);
}

// CMS.DataEngine.QueryParameters
/// <summary>
/// Resolves the query macros in <see cref="P:CMS.DataEngine.QueryParameters.Text" /> property value.
/// </summary>
/// <param name="updateQueryText">If true, the query text is updated with resolved version</param>
/// <returns>Resolved query text</returns>
// Token: 0x06001217 RID: 4631 RVA: 0x00033220 File Offset: 0x00032220
private string ResolveMacrosInternal(bool updateQueryText)
{
    string text = this.Text;
    if (!this.mMacrosResolved)
    {
        if (this.Params != null)
        {
            text = this.Params.ResolveMacros(text);
        }
        if (this.Macros != null)
        {
            text = this.Macros.ResolveMacros(text);
        }
        if (updateQueryText)
        {
            this.Text = text;
            this.mMacrosResolved = true;
        }
    }
    return text;
}

public QueryMacros Macros { get; set; }

public QueryDataParameters Params { get; set; }

text = this.Macros.ResolveMacros(text);

// CMS.DataEngine.QueryMacros
/// <summary>
/// Resolves the given macros within a query
/// </summary>
/// <param name="queryText">Query text</param>
// Token: 0x06000758 RID: 1880 RVA: 0x00017027 File Offset: 0x00016027
public string ResolveMacros(string queryText)
{
    return SqlMacroHelper.ResolveQueryMacros(this, queryText);
}

// CMS.DataEngine.SqlMacroHelper
/// <summary>
/// Resolves the query macros.
/// </summary>
/// <param name="queryMacros">Query macros</param>
/// <param name="queryText">Query text</param>
// Token: 0x0600072C RID: 1836 RVA: 0x0001674B File Offset: 0x0001574B
public static string ResolveQueryMacros(QueryMacros queryMacros, string queryText)
{
    if (queryText == "SELECT ##DISTINCT## ##TOPN## ##COLUMNS## 
FROM ##SOURCE## 
WHERE ##WHERE## 
GROUP BY ##GROUPBY## 
HAVING ##HAVING## ORDER BY ##ORDERBY##")
    {
        queryText = SqlMacroHelper.BuildSelectQuery(queryMacros);
    }
    else
    {
        queryText = SqlMacroHelper.ResolveMacrosInQueryText(queryMacros, queryText);
    }
    return queryText;
}

// CMS.DataEngine.SqlMacroHelper
/// <summary>
/// Resolves the query macros in the given query text using regular expressions for the query macros and replacements. 
/// </summary>
/// <param name="queryMacros">Query macros</param>
/// <param name="queryText">Query text</param>
// Token: 0x0600072D RID: 1837 RVA: 0x00016770 File Offset: 0x00015770
private static string ResolveMacrosInQueryText(QueryMacros queryMacros, string queryText)
{
    queryText = SqlMacroHelper.ReplaceDistinct(queryText, queryMacros.Distinct);
    queryText = SqlMacroHelper.ReplaceTopN(queryText, queryMacros.TopN);
    queryText = SqlMacroHelper.ReplaceColumns(queryText, SqlMacroHelper.EncodeMacroInText(queryMacros.Columns));
    queryText = SqlMacroHelper.ReplaceSource(queryText, SqlMacroHelper.EncodeMacroInText(queryMacros.Source), SqlMacroHelper.EncodeMacroInText(queryMacros.DefaultSource));
    queryText = SqlMacroHelper.ReplaceWhere(queryText, SqlMacroHelper.EncodeMacroInText(queryMacros.Where), false);
    queryText = SqlMacroHelper.ReplaceGroupBy(queryText, SqlMacroHelper.EncodeMacroInText(queryMacros.GroupBy));
    queryText = SqlMacroHelper.ReplaceHaving(queryText, SqlMacroHelper.EncodeMacroInText(queryMacros.Having));
    queryText = SqlMacroHelper.ReplaceOrderBy(queryText, SqlMacroHelper.EncodeMacroInText(queryMacros.OrderBy));
    queryText = SqlMacroHelper.ReplaceValues(queryText, SqlMacroHelper.EncodeMacroInText(queryMacros.Values));
    queryText = SqlMacroHelper.ReplaceOutput(queryText, SqlMacroHelper.EncodeMacroInText(queryMacros.Output));
    queryText = SqlMacroHelper.DecodeMacroInText(queryText);
    return queryText;
}

// CMS.DataEngine.SqlMacroHelper
/// <summary>
/// Replaces columns macro in the query text
/// </summary>
/// <param name="queryText">Query text</param>
/// <param name="columns">Columns</param>
// Token: 0x06000739 RID: 1849 RVA: 0x00016D8C File Offset: 0x00015D8C
public static string ReplaceColumns(string queryText, string columns)
{
    columns = SqlMacroHelper.GetColumnsString(columns);
    if (!SqlMacroHelper.ReplaceMacro(ref queryText, "##COLUMNS##", columns, null) && columns != "*" && columns != "#NONE#")
    {
        throw new NotSupportedException(string.Concat(new string[]
        {
            "[SqlHelper.ReplaceColumns]: Missing ##COLUMNS## macro in the query text '",
            queryText,
            "', cannot apply the specified columns '",
            columns,
            "'."
        }));
    }
    return queryText;
}

来源QueryParameters completeQueryParameters = this.GetCompleteQueryParameters(null);

    private DataSet GetDataFromDBInternal()
        {
            int totalRecords = 0;
            object obj = this.lockObject;
            DataSet result;
            lock (obj)
            {
                GeneralConnection connection = this.GetConnection();
                QueryParameters completeQueryParameters = this.GetCompleteQueryParameters(null);
                result = connection.ExecuteQuery(completeQueryParameters, ref totalRecords);
                this.TotalRecords = totalRecords;
            }
            return result;
        }

// CMS.DataEngine.DataQueryBase<TQuery>
/// <summary>
/// Returns query parameter container filled with the complete settings of current query.
/// </summary>
/// <remarks>
/// Wraps distinct paged query as nested so the row number column required for paging doesn't thwart the distinct selection.
/// </remarks>
/// <param name="settings">Query settings to use</param>
// Token: 0x06000D1F RID: 3359 RVA: 0x00027254 File Offset: 0x00026254
public QueryParameters GetCompleteQueryParameters(DataQuerySettings settings = null)
{
    IDataQuery executingQuery = this.GetExecutingQuery(null);
    if (executingQuery == null)
    {
        return null;
    }
    if (executingQuery == this)
    {
        return this.GetCompleteQueryParametersInternal(settings);
    }
    return executingQuery.GetCompleteQueryParameters(settings);
}

// CMS.DataEngine.DataQueryBase<TQuery>
/// <summary>
/// Returns query parameter container filled with the complete settings of current query.
/// </summary>
/// <remarks>
/// Wraps distinct paged query as nested so the row number column required for paging doesn't thwart the distinct selection.
/// </remarks>
/// <param name="settings">Query settings to use</param>
// Token: 0x06000D20 RID: 3360 RVA: 0x00027284 File Offset: 0x00026284
private QueryParameters GetCompleteQueryParametersInternal(DataQuerySettings settings = null)
{
    DataQueryBase<TQuery> dataQueryBase = this;
    settings = (settings ?? dataQueryBase.GetCompleteSettings(null));
    if (settings.IsPagedQuery)
    {
        if (settings.SelectDistinct)
        {
            dataQueryBase = dataQueryBase.AsNestedInternal<TQuery>(settings, null);
            settings = dataQueryBase.GetCompleteSettings(null);
        }
        else
        {
            this.TranslateOrderByColumn(settings);
        }
    }
    return dataQueryBase.GetQueryParameters(settings);
}

settings = (settings ?? dataQueryBase.GetCompleteSettings(null));

// CMS.DataEngine.DataQueryBase<TQuery>
/// <summary>
/// Gets the complete parameters for the query execution. The parameters are always a new instance of DataQuerySettings which can be further modified without any impact to the query itself.
/// </summary>
/// <param name="executingQuery">Executing query for which the parameters are retrieved</param>
// Token: 0x06000CE3 RID: 3299 RVA: 0x00026284 File Offset: 0x00025284
public virtual DataQuerySettings GetCompleteSettings(IDataQuery executingQuery = null)
{
    DataQuerySettings dataQuerySettings = new DataQuerySettings();
    object obj = this.lockObject;
    lock (obj)
    {
        this.CopyPropertiesTo(dataQuerySettings);
        this.EnsureColumns(dataQuerySettings);
        this.EnsureDefaultOrderBy(dataQuerySettings);
        bool hasDataSource = this.HasDataSource;
        if (executingQuery != null)
        {
            hasDataSource = executingQuery.HasDataSource;
        }
        if (!hasDataSource)
        {
            this.EnsureDefaultSource(dataQuerySettings);
        }
    }
    return dataQuerySettings;
}

直接用Kentico11的源码调试得到的堆栈信息

CMS.DataEngine.dll!CMS.DataEngine.SqlMacroHelper.ResolveQueryMacros(CMS.DataEngine.QueryMacros queryMacros, string queryText) Line 137 C#
CMS.DataEngine.dll!CMS.DataEngine.QueryMacros.ResolveMacros(string queryText) Line 198 C#
CMS.DataEngine.dll!CMS.DataEngine.SqlHelper.PrepareWithQuery(string queryText, string where, string orderBy, int topN, string extraColumns) Line 1017 C#
CMS.DataEngine.dll!CMS.DataEngine.SqlHelper.PreparePagedQuery(string queryText, CMS.DataEngine.QueryMacros macros, int offset, int maxRecords, bool getTotal, bool subQuery) Line 943 C#
CMS.DataEngine.dll!CMS.DataEngine.QueryParameters.FinalizeForExecution(bool getTotal, bool subQuery) Line 336 C#
CMS.DataEngine.dll!CMS.DataEngine.QueryParameters.GetParametersForExecution(bool getTotal, bool subQuery) Line 293 C#
> CMS.DataEngine.dll!CMS.DataEngine.GeneralConnection.ExecuteQuery(CMS.DataEngine.QueryParameters query, ref int totalRecords) Line 311 C#
CMS.DataEngine.dll!CMS.DataEngine.DataQueryBase<CMS.DataEngine.DataQuery>.GetDataFromDBInternal() Line 1760 C#
CMS.DataEngine.dll!CMS.DataEngine.DataQueryBase<CMS.DataEngine.DataQuery>.GetDataFromDB() Line 1730 C#
CMS.DataEngine.dll!CMS.DataEngine.DataQueryBase<CMS.DataEngine.DataQuery>.GetData() Line 1678 C#
CMS.DataEngine.dll!CMS.DataEngine.DataQueryBase<CMS.DataEngine.DataQuery>.Execute() Line 1641 C#
CMS.DataEngine.dll!CMS.DataEngine.DataQueryBase<CMS.DataEngine.DataQuery>.Result.get() Line 125 C#
CMS.DataEngine.dll!CMS.DataEngine.DataQueryBase<CMS.DataEngine.ObjectQuery<CMS.CustomTables.CustomTableItem>>.GetResults(CMS.DataEngine.IDataQuery query, ref int totalRecords) Line 1778 C#
CMS.DataEngine.dll!CMS.DataEngine.ObjectQueryBase<CMS.DataEngine.ObjectQuery<CMS.CustomTables.CustomTableItem>, CMS.CustomTables.CustomTableItem>.GetResults(CMS.DataEngine.IDataQuery query, ref int totalRecords) Line 423 C#
CMS.DataEngine.dll!CMS.DataEngine.DataQueryBase<CMS.DataEngine.ObjectQuery<CMS.CustomTables.CustomTableItem>>.GetDataFromDB() Line 1735 C#
CMS.DataEngine.dll!CMS.DataEngine.DataQueryBase<CMS.DataEngine.ObjectQuery<CMS.CustomTables.CustomTableItem>>.GetData() Line 1678 C#
CMS.DataEngine.dll!CMS.DataEngine.ObjectQueryBase<CMS.DataEngine.ObjectQuery<CMS.CustomTables.CustomTableItem>, CMS.CustomTables.CustomTableItem>.GetData() Line 445 C#
CMS.DataEngine.dll!CMS.DataEngine.DataQueryBase<CMS.DataEngine.ObjectQuery<CMS.CustomTables.CustomTableItem>>.Execute() Line 1641 C#
CMS.DataEngine.dll!CMS.DataEngine.DataQueryBase<CMS.DataEngine.ObjectQuery<CMS.CustomTables.CustomTableItem>>.Result.get() Line 125 C#
CMS.DataEngine.dll!CMS.DataEngine.InfoObjectCollection<CMS.DataEngine.BaseInfo>.GetData(CMS.DataEngine.IWhereCondition where, int offset, int maxRecords, ref int totalRecords) Line 1437 C#
CMS.DataEngine.dll!CMS.DataEngine.InfoObjectCollection<CMS.DataEngine.BaseInfo>.GetItemInternal(int index) Line 2175 C#
CMS.DataEngine.dll!CMS.DataEngine.InfoObjectCollection<CMS.DataEngine.BaseInfo>.GetItem(int index) Line 2055 C#
CMS.DataEngine.dll!CMS.DataEngine.InfoObjectCollection<CMS.DataEngine.BaseInfo>.InternalCount.get() Line 901 C#
CMS.DataEngine.dll!CMS.DataEngine.InfoObjectCollection<CMS.DataEngine.BaseInfo>.GetEnumeratorInternal() Line 2456 C#
CMS.DataEngine.dll!CMS.DataEngine.InfoObjectCollection<CMS.DataEngine.BaseInfo>.GetEnumerator() Line 2436 C#
CMSApp.dll!Global.Global() Line 30 C#
[External Code]

相比而言，得到了更多的信息，可以知道queryText这个参数是怎么拼凑出来的

CMS.DataEngine.dll!CMS.DataEngine.QueryMacros.ResolveMacros(string queryText) Line 198 C#
CMS.DataEngine.dll!CMS.DataEngine.SqlHelper.PrepareWithQuery(string queryText, string where, string orderBy, int topN, string extraColumns) Line 1017 C#
CMS.DataEngine.dll!CMS.DataEngine.SqlHelper.PreparePagedQuery(string queryText, CMS.DataEngine.QueryMacros macros, int offset, int maxRecords, bool getTotal, bool subQuery) Line 943 C#
CMS.DataEngine.dll!CMS.DataEngine.QueryParameters.FinalizeForExecution(bool getTotal, bool subQuery) Line 336 C#
CMS.DataEngine.dll!CMS.DataEngine.QueryParameters.GetParametersForExecution(bool getTotal, bool subQuery) Line 293 C#

CMS.DataEngine.dll!CMS.DataEngine.GeneralConnection.ExecuteQuery(CMS.DataEngine.QueryParameters query, ref int totalRecords) Line 311 C#

query = query.GetParametersForExecution(totalRecords != -2, false);

// CMS.DataEngine.QueryParameters
/// <summary>
/// Gets the query parameters for query execution
/// </summary>
/// <param name="getTotal">If true, the query is configured to get total number of records when paging is enabled</param>
/// <param name="subQuery">If true, the query is used as a sub-query</param>
// Token: 0x06001218 RID: 4632 RVA: 0x00033278 File Offset: 0x00032278
internal QueryParameters GetParametersForExecution(bool getTotal = true, bool subQuery = false)
{
    QueryParameters queryParameters = this.Clone();
    queryParameters.FinalizeForExecution(getTotal, subQuery);
    return queryParameters;
}

// CMS.DataEngine.QueryParameters
/// <summary>
/// Finalizes the given query parameters for execution within SQL connection
/// </summary>
/// <param name="getTotal">If true, the query is configured to get total number of records when paging is enabled</param>
/// <param name="subQuery">If true, the query is used as a sub-query</param>
// Token: 0x0600121A RID: 4634 RVA: 0x00033324 File Offset: 0x00032324
internal void FinalizeForExecution(bool getTotal = true, bool subQuery = false)
{
    int maxRecords = this.MaxRecords;
    if (maxRecords > 0)
    {
        SqlHelper.CheckPagedQuery(this);
        QueryMacros macros = this.Macros;
        string text = SqlHelper.PreparePagedQuery(this.Text, macros, this.Offset, maxRecords, getTotal, subQuery);
        this.Text = text;
        this.Offset = 0;
        this.MaxRecords = 0;
    }
    this.IsForExecution = true;
}

// CMS.DataEngine.SqlHelper
/// <summary>
/// Prepares the query for paging, adds additional system columns and updates the columns list.
/// </summary>
/// <param name="queryText">Query text</param>
/// <param name="macros">Query expressions</param>
/// <param name="offset">Index of first record to get</param>
/// <param name="maxRecords">Maximum number of records to get</param>
/// <param name="getTotal">If true, the query should get the total number of records</param>
/// <param name="subQuery">If true, the query is used as a sub-query</param>
// Token: 0x06001067 RID: 4199 RVA: 0x0002D0F4 File Offset: 0x0002C0F4
public static string PreparePagedQuery(string queryText, QueryMacros macros, int offset, int maxRecords, bool getTotal = true, bool subQuery = false)
{
    string text = macros.Columns;
    if (string.IsNullOrEmpty(text))
    {
        text = "*";
    }
    SqlHelper.HandleEmptyColumns(ref text);
    string text2 = macros.OrderBy;
    SqlHelper.HandleEmptyColumns(ref text2);
    if (!string.IsNullOrEmpty(text2))
    {
        if (macros.TopN <= 0)
        {
            queryText = SqlMacroHelper.RemoveOrderBy(queryText);
        }
        string columns = text;
        string addColumns = new RowNumberColumn("CMS_RN", text2).ToString();
        text = SqlHelper.AddColumns(text, addColumns, false);
        string between = SqlHelper.GetBetween("CMS_RN", offset + 1, offset + maxRecords);
        string text3 = getTotal ? SqlHelper.GetTotalColumn(macros.Total, subQuery) : null;
        if (subQuery)
        {
            string columns2 = SqlHelper.AddColumns(columns, text3, false);
            queryText = SqlHelper.PrepareNestedQuery(queryText, between, null, 0, columns2);
        }
        else
        {
            queryText = SqlHelper.PrepareWithQuery(queryText, between, "CMS_RN", 0, text3);
        }
        text2 = null;
    }
    macros.OrderBy = text2;
    macros.Columns = text;
    return queryText;
}

对应的this.Macros.ToString()， this的类型是CMS.DataEngine.QueryParameters

TOPN: 0,
COLUMNS: *, ROW_NUMBER() OVER (ORDER BY [ItemID]) AS [CMS_RN],
SOURCE: Edenred_CardholderCardSettings,

this.Name

Company.CardholderCardSettings.selectall

Kentico11DataEngineQueryDataDataQueryBase.cs中的CustomQueryText是

SELECT ##DISTINCT## ##TOPN## ##COLUMNS##
FROM ##SOURCE##
WHERE ##WHERE##
GROUP BY ##GROUPBY##
HAVING ##HAVING## ORDER BY ##ORDERBY##

参数settings.ToString()

TOPN: 0,
COLUMNS: (COUNT(*)) AS [Count],
SOURCE: (
SELECT *
FROM CMS_Class
WHERE [ClassName] = @ClassName
) AS SubData,

settings.Parameters 里面有一个元素，里面存储的value是表名company.cardholdercardsettings

CustomQueryText为null，FullQueryName是company.cardholdercardsettings.selectall

// Get query
var qi = QueryInfoProvider.GetQueryInfo(FullQueryName, true);

// Prepare the query
query = new QueryParameters(qi, parameters, expressions);

FullQueryName是company.cardholdercardsettings.selectall 来进行查找

// CMS.DataEngine.DataQueryBase<TQuery>
/// <summary>
/// Represents a full query name of the query
/// </summary>
// Token: 0x17000346 RID: 838
// (get) Token: 0x06000CC5 RID: 3269 RVA: 0x00025EF4 File Offset: 0x00024EF4
public virtual string FullQueryName
{
    get
    {
        string text = this.QueryName;
        string className = this.ClassName;
        if (string.IsNullOrEmpty(text))
        {
            text = (QueryInfoProvider.QueryIsExplicitlyDefined(className, "selectall") ? "selectall" : "generalselect");
        }
        return string.Format("{0}.{1}", className, text);
    }
}

    public static QueryInfo GetQueryInfo(string name, bool throwException)
        {
            var result = ProviderObject.GetQueryInfoInternal(name);

            // Throw exception if required
            if ((result == null) && throwException)
            {
                throw new Exception("[QueryProvider.GetQuery]: Query " + name + " not found!");
            }

            return result;
        }

Kentico11DataEngineClassesQueriesQueryInfoProvider.cs

Kentico11DataEngineAbstractProvidersDictionariesBaseProviderDictionary.cs

infos.ByFullName中的  private readonly ICacheStorage<TKey, TValue> mCache;存储了数据  

// CMS.DataEngine.AbstractInfoProvider<TInfo, TProvider, TQuery>
/// <summary>
/// Loads all objects from the database to memory.
/// </summary>
// Token: 0x060017BF RID: 6079 RVA: 0x000443D4 File Offset: 0x000433D4
protected void LoadInfos()
{
    if (!this.HashtableSettings.UseHashtables)
    {
        return;
    }
    if (ProviderHelper.LoadTables(this.infos))
    {
        object obj = this.hashtableLock;
        lock (obj)
        {
            if (ProviderHelper.LoadTables(this.infos))
            {
                this.infos = this.CreateHashtables();
                this.infos.LoadDefaultItems();
            }
        }
    }
}

// CMS.DataEngine.AbstractInfoProvider<TInfo, TProvider, TQuery>
/// <summary>
/// Creates the provider hashtables
/// </summary>
// Token: 0x060017C0 RID: 6080 RVA: 0x00044450 File Offset: 0x00043450
private ProviderDictionaryCollection CreateHashtables()
{
    string objectType = this.TypeInfo.ObjectType;
    ProviderDictionaryCollection providerDictionaryCollection = new ProviderDictionaryCollection(objectType, this.HashtableSettings.Load, new Action<ProviderDictionaryCollection, object>(this.LoadData), null);
    if (this.HashtableSettings.Name)
    {
        string text = this.TypeInfo.CodeNameColumn;
        if (this.TypeInfo.SiteIDColumn != "[Unknown]")
        {
            text = text + ";" + this.TypeInfo.SiteIDColumn;
        }
        if (this.TypeInfo.GroupIDColumn != "[Unknown]")
        {
            text = text + ";" + this.TypeInfo.GroupIDColumn;
        }
        providerDictionaryCollection.ByCodeName = new ProviderInfoDictionary<string>(objectType, text, null, false, this.HashtableSettings.UseWeakReferences);
    }
    if (this.HashtableSettings.ID)
    {
        providerDictionaryCollection.ById = new ProviderInfoDictionary<int>(objectType, this.TypeInfo.IDColumn, null, false, this.HashtableSettings.UseWeakReferences);
    }
    if (this.HashtableSettings.GUID)
    {
        if (this.TypeInfo.SiteIDColumn == "[Unknown]")
        {
            providerDictionaryCollection.ByGuid = new ProviderInfoDictionary<Guid>(objectType, this.TypeInfo.GUIDColumn, null, false, this.HashtableSettings.UseWeakReferences);
        }
        else
        {
            string columnNames = this.TypeInfo.GUIDColumn + ";" + this.TypeInfo.SiteIDColumn;
            providerDictionaryCollection.ByGuidAndSite = new ProviderInfoDictionary<string>(objectType, columnNames, null, false, this.HashtableSettings.UseWeakReferences);
        }
    }
    IFullNameInfoProvider fullNameInfoProvider = this as IFullNameInfoProvider;
    if (this.HashtableSettings.FullName && fullNameInfoProvider != null)
    {
        providerDictionaryCollection.ByFullName = fullNameInfoProvider.GetFullNameDictionary();
    }
    return providerDictionaryCollection;
}

// CMS.DataEngine.QueryInfoProvider
/// <summary>
/// Creates new dictionary for caching the objects by full name
/// </summary>
// Token: 0x06000F3C RID: 3900 RVA: 0x0002AEA8 File Offset: 0x00029EA8
public ProviderInfoDictionary<string> GetFullNameDictionary()
{
    return new ProviderInfoDictionary<string>("cms.query", "QueryName;ClassID", null, false, false);
}

最后发现，sql语句是,通过组合筛选得到sql。而这里的sql不符合规则，导致了问题

SELECT b.ClassName,
       b.ClassDisplayName,
       a.*
FROM CMS_Query AS a
    INNER JOIN dbo.CMS_Class AS b
        ON a.ClassID = b.ClassID
WHERE a.QueryName = N'selectall'
      AND b.ClassName = 'className';