How can I edit the EXISTS clause produced by NHibernate?

Question

How can we override the usage of the HqlExists class for it's usage creating an EXISTS clause in a given NHibernate configuration/dialect?

The driver being used requires that only a single column be returned when using an EXISTS clause. Because the target is using compound keys, multiple columns are selected in the subquery.

FROM EXISTS (SELECT a,b,c,d FROM ....

The end goal is to create the syntax

FROM EXISTS (SELECT 1 FROM ...

My current solution without modifying the source can be applied to a specific context target but adds roughly 2 seconds to the query time:

public class ExistsModifierInterceptor : EmptyInterceptor
{
    private readonly ExistsModificationVisitor existsModificationVisitor;
    public ExistsModifierInterceptor()
    {
        existsModificationVisitor = new ExistsModificationVisitor();
    }
    public override SqlString OnPrepareStatement(SqlString sql)
    {
        existsModificationVisitor.Syntax.Clear();
        sql.Visit(existsModificationVisitor);
        if (existsModificationVisitor.isModified)
        {
            return base.OnPrepareStatement(existsModificationVisitor.Syntax.ToSqlString());
        }
        else { return base.OnPrepareStatement(sql); }
    }

    internal class ExistsModificationVisitor : ISqlStringVisitor
    {
        public SqlStringBuilder Syntax { get { return _Syntax; } }
        public bool isModified { get; private set; }

        private readonly SqlStringBuilder _Syntax = new SqlStringBuilder();
        private const string ExistKeyword = "exists";
        private const string FromKeyword = "from";
        public ExistsModificationVisitor() { }
        void ISqlStringVisitor.Parameter(Parameter parameter)
        {
            _Syntax.AddParameter();
        }

        public void String(string text)
        {
            if (text.Contains("exists"))
            {
                isModified = true;
                var start = text.IndexOf(ExistKeyword, StringComparison.Ordinal);
                var end = text.IndexOf(FromKeyword, start);
                text = text.Remove(start, end - start);
                text = text.Insert(start, "EXISTS (SELECT 1 ");
            }
                _Syntax.Add(text);
        }
        public void String(SqlString sqlString)
        {
            this.String(sqlString.ToString());
        }
    }
}

I've modified/compiled HqlExists in the source with following modification to strip the token and replace it with HqlIntegerConstant whichs is a bit faster but I was hoping I wouldn't have to use my own fork for such a small requirement:

public class HqlExists : HqlBooleanExpression
{
    public HqlExists(IASTFactory factory, HqlQuery query) : base(HqlSqlWalker.EXISTS, "exists", factory, query)
    {
        var selectClauseNode =
                this.Children.FirstOrDefault()?
                    .Children.FirstOrDefault()?
                    .Children.Where(x => x.AstNode.Type == 111).FirstOrDefault();
                selectClauseNode.ClearChildren();
                //Note: AddChild is internal therefore not available outside HqlTreeNode.cs 
                selectClauseNode.AddChild(new HqlIntegerConstant(factory, "1"));
        
    }
    
}

Is there a simple way to target and replace explicity the way HqlExists is applied for a given dialect? I am using FluentNHibernate for configuration

Answer 1

This is mostly a duplicate of HQL "exists" generates invalid SQL, but there was no real or accepted answer there.

Have you tried the following?

... where exists (select 1 from YourEntity ...)

... where exists (select e.Id.OneProp from YourEntity e)

I.e. essentially what you would need to write if you wrote the SQL yourself, but in HQL.