NHibernate Mapping – Named queries <query/> and <sql-query/>
Queries are business logic, as such, they can be pretty complex, and they also tend to be pretty perf sensitive. As such, you usually want to have a good control over any complex queries. You can do that by extracting your queries to the mapping, so they don’t reside, hardcoded, in the code:
<query name="PeopleByName"> from Person p where p.Name like :name </query>
And you can execute it with:
using (var session = sessionFactory.OpenSession()) using (var tx = session.BeginTransaction()) { session.GetNamedQuery("PeopleByName") .SetParameter("name", "ayende") .List(); tx.Commit(); }
PeopleByName is a pretty standard query, and executing this code will result in:
Now, let us say that we discovered some performance problem in this query, and we want to optimize it. But the optimization is beyond what we can do with HQL, we have to drop to a database specific SQL for that. Well, that is not a problem, <sql-query/> is coming to the rescue.
All you need is to replace the query above with:
<sql-query name="PeopleByName"> <return alias="person" class="Person"/> SELECT {person.*} FROM People {person} WITH(nolock) WHERE {person}.Name LIKE :name </sql-query>
And you are set. You don’t need to make any changes to the code, but the resulting SQL would be:
Fun, isn’t it?
Comments
What do you think about this:
<linq-query
<return
class="Person"/>
With an interpreted-linq, I cant find right know where I saw linq in a plain string...maybe linqpad do something like this.
JFR,
And the question is... WHY?
This could return an IQueryable, so:
And so on..Then you have a namedquery in your mappings that is executed in the linq-way.
@JFR,
Wouldn't it be easier to just type the " && p.City.Name = "Iowa"? You can also use a filter.
The problem with mapped linq-queries is you won't be able to return strongly typed anonymous types (for joins, complex projections, etc) which IMO is the biggest benefit of LINQ.
Huh. Learn something every day. Thanks.,
ps what happened to JFHCI?
Great series, keep up the good work.
Recommendation for the next topic: <version>
version element
@Dmitry yes, my sample isn't good enough and I think that is not necesary to have in the mappings.
But, this could act as a Database - View in the way of represent a subset of the data contained in a table with a huge "where" clause.
Let's change the word "table" by object-repository.. Supose you have a "Orders" repository and them you have a repository called "HighPriorityOrders", HighPriority could mean "where mount > 10000 and lines.count > 5 and date < now.AddMonths(-5) and .. and .. and .. and .. and .."
Now, you could query the HighPriorityOrders repository in this way:
from order in highpriorityorders
where order.Customer.Id == 543
select order;
What is the profit? there is only one query to the database with all bunch of filters. But is not necessary have a named query for do that, so this is out of the scope of the post.
The "fluent" extension to querying possibilities of NHibernate: fknet.wordpress.com/.../fluently-with-nhibernate/
Comment preview