nhibernate with subquery on database

Question

I need to select a product for a user based on other data in the database. If the data is filtered out on the database that will require less data to be send to the server.

User (Id) Product (code) Access (User_Id, code) // Matching users to object codes

Will this query execute on the database sending back the minimal amout of data?

var products = QueryOver.Of<Access>()
    .Where(a => a.User_Id == User.Id())
    .Select(Projections.Property<Acces>(a => a.Code));

var access = QueryOver.Of<Product>()
    .WithSubquery.WhereProperty(h => h.Code)
    .In(products)
    .Future();

Try NHibernate Profiler: http://www.hibernatingrhinos.com/products/nhprof — , Dec 11 '13 at 16:31
It is difficult reply without trying, just activate teh query log and see what's happen. — Felice Pollano, Dec 11 '13 at 16:52
This question appears to be off-topic because it is about a locol situation that is only answerable if we are at the computer of the OP — rene, Dec 11 '13 at 18:54
Thanks, I tried reading the logs but have no idea what to look for. I dont have nhibernate profiler. — MrFox, Dec 12 '13 at 09:08

score 1 · Accepted Answer · edited May 23 '17 at 11:57

This is very reasonable way how to filter data. The result of your queries would look like one SELECT against the DB:

SELECT ...
FROM Product
WHERE Code IN (SELECT Code FROM Access WHERE UserId = @userId)

So, this will for sure be executed on the DB Server, less data will be transfered, and what's more, it also would allow you to do the correct paging (if needed) - this scenario is the way how to filter parent over its one-to-many relations (find Parents which child has...)

Maybe check these Join several queries to optimise QueryOver query, NHibernate QueryOver - Retrieve all, and mark the ones already "selected"

nhibernate with subquery on database

1 Answers1