Active Query Builder support area

How to add the "with (nolock)" hint to all datasources in the query?

Last modified:


Here is the code sample which iterates through all datasources in the query and adds the "with (nolock)" hint  to them.

private void button1_Click(object sender, EventArgs e)
{
    // process entire query
    var queryRoot = queryBuilder1.SQLQuery.QueryRoot;
    using (new UpdateRegion(queryRoot))
    {
        // get all dataSourceObjects which are not CTE references
        var dataSources = queryRoot.GetChildrenRecursive(true)
            .Where(ds => ds.SubQueryCTE == null);

        // process all dataSources
        foreach (var dataSource in dataSources)
        {
            // get syntax-specific extender
            var extender = (MSSQLFromSourceExtender) dataSource.DataSourceAST.Extender;

            // remove old hint
            var oldHint = extender.ObjectJoinHint;
            if (oldHint != null)
                oldHint.Dispose();

            // create new hint
            var newHint = new AstNodeWithList(dataSource.SQLContext);
            newHint.Keyword("With").Symbol("(").Keyword("NoLock").Symbol(")");

            // set new hint
            extender.ObjectJoinHint = newHint;

            // fire "dataSource inner AST updated"
            dataSource.NotifyUpdated();
        }
    }
}

Is this article helpful for you?