NHibernate QueryOver exists any register

Question

What I've read is that the way to implement the "exists" is getting the first register that matches the condition (in SQL Server it will be SELECT TOP(1)...), but it must be compatible with Oracle too, and I'm not so sure that in Oracle, use rownumber is so efficient. Moreover, it must be scalable, I can have from 2 or 3 registers up to 2 or 3 millions...

My current solution is like this:

int? result = this.Session
  .QueryOver<Address>()
  .Where(a => a.Customer.Id == customerId && a.IsMain == true)
  .Select(a => a.Id)
  .Take(1)
  .SingleOrDefault<int?>();

return result != null;

This generates a select top (1) query on SQL Server that I think is more efficient that a count (specially in great volumes) but in Oracle, the query generated is like that:

SELECT y0_
FROM (
    SELECT this_.ADD_Id AS y0_
    FROM ADDRESSES this_
    WHERE (
            this_.CUS_Id = 3942425
            AND this_.ADD_ISMAIN = 'AAAAAAAAAA'
            )
    )
WHERE rownum <= 1

I'm not so sure of the efficiency of that query on Oracle.

Someone could help me to get the most efficient query on both environments with the same NHibernate instruction? Something like an Any() method that I found on some posts, but I can't found it on QueryOver...

score 0 · Answer 1 · edited May 23 '17 at 11:52

What you should do, is to check the proper dialect setting. Some link to the documentation:

3.5.1. SQL Dialects

You should always set the dialect property to the correct NHibernate.Dialect.Dialect subclass for your database. This is not strictly essential unless you wish to use native or sequence primary key generation or pessimistic locking (with, eg. ISession.Lock() or IQuery.SetLockMode()). However, if you specify a dialect, NHibernate will use sensible defaults for some of the other properties listed above, saving you the effort of specifying them manually....

So, with appropriate dialect, the most suitable syntax is used. Well... was used...

The downside of the NHibernate in these days is: "ZERO new development". So if you use some newer version of Oracle, while the latest dialect is Oracle10gDialect - you can (have to) create your own, and do some magic to improve that syntax. Some kick of example of creating custom dialect could be found here

Also, check syntax of other dialects (e.g. SQL 2012 have big improvement based on new support of BUILT-IN "standard SQL" paging - Implement paging (skip / take) functionality with this query)

Thanks, I've implemented yet a custom dialect for some operations in Oracle so there is no problem extending it, but I would know first if the current solution is efficient enough, to avoid custom implementations. — Marc, Sep 01 '15 at 06:25

NHibernate QueryOver exists any register

1 Answers1

3.5.1. SQL Dialects