CODEWITHSUNDEEP
c#linqnhibernate
Foo
Foo

Reputation: 4596

Joining nhibertnate query in LINQ

I am trying to join the results of two nhibernate queries. The problem I am facing here is that it first gets all the rows for both the queries and then performs the where, which enormously slows it down. This is happening because I am doing a List(). But without it the join cannot be made.

var results=  from u in session.QueryOver<User>().List()
                        join ur in session.QueryOver<UserRights>().List()
                            on u.Id equals ur.User.Id
                        where u.Deleted == false && (ur.Role.Id == 10 || ur.Role.Id == 20)

How do I build the statement so that the query doesn't get all the users and user rights before the join and where?

Upvotes: 0

Views: 995

Answers (1)

Felipe Oriani
Felipe Oriani

Reputation: 38598

If you want to use linq with nhibernate your have to use the session.Query<T> instead session.QueryOver<T>. Try this:

// prepare the query
var query = from u in session.Query<User>()
            join ur in session.Query<UserRights>() on u.Id equals ur.User.Id
            where u.Deleted == false && (ur.Role.Id == 10 || ur.Role.Id == 20)
            select u;

// execute query and get list
var results = query.ToList();

Also, avoid to use .ToList() to create your query beacuse it will load all entities in memory (executing a complete query) and execute a Linq To Object in memory. Using like this, you will hit a query database where you need.

Upvotes: 3

Related Questions