Check if object exists in database without loading object with NHibernate

Question

Is there a way in NHibernate to check if an object exists in the database without having to get/load the object?

Answer 1

You could use one of the following 3 queries (or you could use Criteria API Projections.RowCountInt64() from David answer):

bool exist = session.Query<Employee>()
    .Any(x => x.EmployeeID == 1);

bool exist = session.QueryOver<Employee>()
    .Where(x => x.EmployeeID == 1)
    .RowCount() > 0;

bool exist = session.Query<Employee>()
    .Count(x => x.EmployeeID == 1) > 0;

Just keep in mind that Any is worst of those three because it fetches entity. Here is sql query generated for each:

exec sp_executesql N'select TOP (1)  employee0_.EmployeeID as EmployeeID0_, employee0_.Name as Name0_ from Employee employee0_ where employee0_.EmployeeID=@p0',N'@p0 int',@p0=1

exec sp_executesql N'SELECT count(*) as y0_ FROM Employee this_ WHERE this_.EmployeeID = @p0',N'@p0 int',@p0=1

exec sp_executesql N'select cast(count(*) as INT) as col_0_0_ from Employee employee0_ where employee0_.EmployeeID=@p0',N'@p0 int',@p0=1

Answer 2

So I let myself to do some tests with your examples @Jamie Ide @Darius Kucinskas @Dmitry

So:

var exists = session
.CreateQuery("select 1 from Widget where _color = 'green'")
.SetMaxResults(1)
.UniqueResult<Int32?>()
.HasValue;

in my case was 18% faster than

bool exist = session.Query<Employee>()
    .Any(x => x.EmployeeID == 1);

14% than

bool exist = session.Query<Employee>()
    .Count(x => x.EmployeeID == 1) > 0;

and 8%

bool exist = session.QueryOver<Employee>()
    .Where(x => x.EmployeeID == 1)
    .RowCount() > 0;

So in my opinion even if hard coded query is fastest the

bool exist = session.QueryOver<Employee>()
    .Where(x => x.EmployeeID == 1)
    .RowCount() > 0;

is best option because of good habits and code clearness

Answer 3

Could always do a count.

I tend to use DetachedCriteria, so I'd have something like:

var criteria = // some criteria that will identify your object

var result = criteria
    .GetExecutableCriteria(Session)
    .SetProjection(Projections.RowCountInt64())
    .UniqueResult();

return result > 0;

Answer 4

To expand on Darius Kucinskas' excellent answer, you can avoid fetching the entity using Select:

bool exist = session.Query<Employee>()
                    .Where(x => x.EmployeeID == 1)
                    .Select(x => x.EmployeeID)
                    .Any();

As mentioned, the query performance should be the same however I would expect this to reduce network traffic.

Answer 5

I think you are looking for this...

var fooExists = session.Query<Foo>().Any(f => /*condition*/);