I know, I know; you're thinking - "Why do I even need to write my own queries, when there's a perfectly awesome Query and Projection module in Orchard I can leverage? Well, if you're building an event calendar, for example, you might want to generate your queries on the fly based on query string parameters; in which case, I don't know if creating a bunch of Query items is the nicest way to go. (If there's a nicer, better way of working with parameters/tokens in Queries, let me know; ie if you can add your own custom Tokens / handling to the Query Edit page to have more than Url.Absolute, but I digress)

EDIT: Pretty much immediately after I wrote this post, I noticed that there is a Request / Query String you can hook into when using your queries.

Which pretty much makes this post a little redundant for the original problem it was supposed to solve, but I'll leave it if you're interested in the internals. But basically, you can create a Query that varies based on query string, which you can use in a Projection. Problem solved!

So after much step-through debugging and source code diving, I present to you "how Orchard handles Projections / Queries in the back end and how you can too, with HQL".

Let's say I want to get a bunch of content items, where the field MyDate lies in a specific month.

First of all, you create a HQL query:

var matchingEventItems = ContentManager.HqlQuery().ForType("EventItem").ForVersion(VersionOptions.Published);

Then you add some where conditions (you can also add sort conditions, but I leave that as an exercise to work out from the Orchard source code; it's pretty similar to the Where conditions)

        year = 2013; //just imagine I pulled these in from the query string ;)
        month = 3;

        long minTicks = new DateTime(year, month, 1).ToUniversalTime().Ticks;
        long maxTicks = new DateTime(year, month, 1).AddMonths(1).AddDays(-1).ToUniversalTime().Ticks;

        matchingItems = matchingItems.Where(
            a => a.ContentPartRecord<FieldIndexPartRecord>().Property("IntegerFieldIndexRecords", "MyContentTypeMyDate"),    //gone through "safename", whatever that is - see ContentFieldsFilter.cs in projections                
            p => p.And(
                x => x.Eq("PropertyName", "MyContentType.MyDate."),  //this comes from the following code in Orchard Source, but I don't know what storage name is for;   propertyName = String.Join(".", part.Name, field.Name, storageName ?? "");     
                y => y.And(
                    date => date.Ge("Value", minTicks),
                    date => date.Le("Value", maxTicks)              
                    )
                )                
        );

It's a little bit like CAML from SharePoint, in which you provide lots of Ands, Ors and Eqs to build up your query tree. Note that you'll need to add a reference to Orchard.Projections for your module (so don't forget to add a dependency too).

And finally, you slice it to actually execute and get some items for your query. You'll get back IEnumerable<ContentItem>, which you can then use to your hearts' content. For example, you could pull out the Title for all these items:

foreach(var item in items)
{
    string theTitle = item.As<TitlePart>().Title;
}

If you're looking to make different queries, the parameters / syntax for the HQL might be a little different - I suggest you put a breakpoint in ProjectionPartDriver and step through to see what's required.

Tags: HQL, query, Orchard

3 Comments

• Soren Ullidtz said Tuesday, 25 November 2014 4:41:37 p.m. Reply

  storageName is for when you're accessing data which isn't in the Value of the field. (Try querying the Text on a LinkField)

• rtpHarry said Tuesday, 25 August 2015 7:44:52 a.m. Reply

  Thank you so much, you have dug me out of a hole I had been stuck in for 2 days.
  
  I didn't think I would ever solve this issue but your snippet pointed me in the right direction!

• Roland said Friday, 28 August 2015 4:25:43 p.m. Reply

  Thanks for this. This is very useful, however I think instead of using HqlQuery(), you can use Query() instead if not for making the code a bit shorter. E.g:
  
  var matchingEvents = ContentManager.Query(VersionOptions.Published, "MyContentType")
  .Join<FieldIndexPartRecord>()
  .Where(
  fieldIndex => fieldIndex.IntegerFieldIndexRecords.Any(
  integerField => integerField.PropertyName == "MyContentType.MyDate."
  && integerField.Value >= minTicks
  && integerField.value <= maxTicks
  )
  )
  .List();