Querying Orchard in HQL

Orchard has two APIs on IContentManager to query content items: Query, and HqlQuery. Query is the older API, but it’s also the simplest. It’s great when you want to perform a simple query such as “get all content items with part TitlePart where the title begins with A”. HqlQuery is a little more advanced, closer to nHibernate APIs, and allows for more complex queries. It was added to build the dynamic queries necessary for Projections. It is still, however, designed around the Orchard content type system, which makes it inadequate for queries that don’t trivially map to content items and parts.

There is a Repository<TRecord> API that is available that gives direct querying over a single table, but it was designed to be used internally by higher-level APIs, not really to be used directly. The repository pattern is not something that the core team considers like good practice, even if the API exists in the system. Plus, it won’t help if you need to query over more than one table. You should not use it if you can help it.

What I recommend for general-purpose querying where HqlQuery or Query are too inflexible is to go directly against the nHibernate session and write raw HQL. It’s definitely an advanced scenario, but the flexibility is unparalleled, and the code remains database-neutral.

The first thing to do if you want to go down that route is to get a reference of the nHibernate session. This is done by injecting ISessionLocator. You can then get the actual session by calling For on it:

var session = _sessionLocator.For(typeof (UserPartRecord));

The method takes a type as a parameter. What you put in there, weirdly, doesn’t matter much. I usually put in there the type of the main record that I want to get out as results.

Once you have the session, you can ask it to execute arbitrary queries. You’ll want to be familiar with the HQL syntax, of course, and you’ll want to make sure you use fully-qualified record class names where you’d use table names in SQL. Aliases help make queries more readable (User and UserRoles here):

var countQuery = session.CreateQuery(
    "SELECT COUNT(DISTINCT User.Id) "
  + "FROM Orchard.Users.Models.UserPartRecord User, "
  + "Orchard.Roles.Models.UserRolesPartRecord UserRoles "
  + "WHERE UserRoles.UserId = User.Id AND User.UserName IS NOT NULL");

Once you’ve built a query successfully (the above will cause an exception if nHibernate can’t make sense of your query), you can get results out of it by calling List().

Happy querying!

UPDATE: It turns out that Sql CE does not support count(distinct), and nHibernate does not support nested queries in FROM clauses. The workaround is to remove the DISTINCT keyword, and use a GROUP BY if necessary.