Ayende @ Rahien

Refunds available at head office

NHibernate Mapping – Named queries and

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:

image

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:

image

Fun, isn’t it?

Comments

JFR
04/17/2009 12:05 AM by
JFR

What do you think about this:

<linq-query

    from p in People

    where p.City.Name == :cityName

    select p

With an interpreted-linq, I cant find right know where I saw linq in a plain string...maybe linqpad do something like this.

JFR
04/17/2009 12:08 AM by
JFR 
sorry:
  
  
<linq-query name="PeopleByName">
  
<return  
  
from p in People
  
where p.City.Name == :cityName
  
select p
  
  
    </sql-query>
  
  
>
Ayende Rahien
04/17/2009 12:10 AM by
Ayende Rahien

JFR,

And the question is... WHY?

JFR
04/17/2009 12:35 AM by
JFR

This could return an IQueryable, so:

      from people in session.GetNamedLinqQuery("PeopleByCityName", "Iowa")

      where p.name == "JFR"

      select p;

And so on..Then you have a namedquery in your mappings that is executed in the linq-way.

Dmitry
04/17/2009 01:41 AM by
Dmitry

@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.

joe
04/17/2009 02:16 AM by
joe

Huh. Learn something every day. Thanks.,

ps what happened to JFHCI?

Matt
04/17/2009 12:09 PM by
Matt

Great series, keep up the good work.

Recommendation for the next topic:

Matt
04/17/2009 12:10 PM by
Matt

version element

JFR
04/17/2009 12:13 PM by
JFR

@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.

Comments have been closed on this topic.