Hi,
I really searched for this for quite some time, so please bear with me if this question has been answered many times before.
What I want is actually pretty simple: I need an efficient way to determine whether there are ANY entities in the DB that match given criteria. I don't need the entities themselves, nor the count. Only an answer as simple as a yes or a now.
The corresponding (T-)SQL would look like this:
Code:
-- Idea: select 1 for each row that matches, but select only the first result, returns empty result otherwise
SELECT TOP 1 1 FROM Design this_ WHERE this_.Number = 1 and this_.Status = 0;
-- Idea: return 1 if subquery has any match, otherwise empty result
SELECT 1 WHERE EXISTS (SELECT * FROM Design this_ WHERE this_.Number = 1 and this_.Status = 0);
-- Idea: similar to previous one, but return 0 if no results found
SELECT CASE WHEN EXISTS (SELECT * FROM Design this_ WHERE this_.Number = 1 and this_.Status = 0) THEN 1 ELSE 0 END;
These three options should be more or less equally fast. Now comes the question: how do I map this most efficiently to the NH criteria API?
I tried this (resembling the first SQL option):
Code:
var crit = session
.CreateCriteria<Design>()
.Add(Restrictions.Eq("Number", designNumber))
.Add(Restrictions.Eq("Status", DesignStatus.Active))
.SetProjection(Projections.Constant(1))
.SetMaxResults(1);
var found = crit.UniqueResult() != null;
This works perfectly. But is there a better way to do this? Is this causing the least amount of overhead within NH?
BTW, the generated SQL is:
Code:
SELECT TOP 1 y0_ FROM
( SELECT @p0 as y0_, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row
FROM [Design] this_
WHERE this_.Number = @p1 and this_.Status = @p2) as query
WHERE query.__hibernate_sort_row > 0
ORDER BY query.__hibernate_sort_row;
@p0 = '1', @p1 = '1', @p2 = '0'
While the query plan is slightly more complex than the one of the SQL version posted above it is not considerably more expensive.
Thanks in advance for suggestions on this topc.
Peter
Login
FAQ
Search