How do I express this LINQ query using the NHibernate ICriteria API?

Question

My current project is using NHibernate 3.0b1 and the NHibernate.Linq.Query<T>() API. I'm pretty fluent in LINQ, but I have absolutely no experience with HQL or the ICriteria API. One of my queries isn't supported by the IQueryable API, so I presume I need to use one of the previous APIs -- but I have no idea where to start.

I've tried searching the web for a good "getting started" guide to ICriteria, but the only examples I've found are either far too simplistic to apply here or far too advanced for me to understand. If anyone has some good learning materials to pass along, it would be greatly appreciated.

In any case, the object model I'm querying against looks like this (greatly simplified, non-relevant properties omitted):

class Ticket {
    IEnumerable<TicketAction> Actions { get; set; }
}
abstract class TicketAction {
    Person TakenBy { get; set; }
    DateTime Timestamp { get; set; }
}
class CreateAction : TicketAction {}
class Person {
    string Name { get; set; }
}

A Ticket has a collection of TicketAction describing its history. TicketAction subtypes include CreateAction, ReassignAction, CloseAction, etc. All tickets have a CreateAction added to this collection when created.

This LINQ query is searching for tickets created by someone with the given name.

var createdByName = "john".ToUpper();
var tickets = _session.Query<Ticket>()
    .Where(t => t.Actions
        .OfType<CreateAction>()
        .Any(a => a.TakenBy.Name.ToUpper().Contains(createdByName));

The OfType<T>() method causes a NotSupportedException to be thrown. Can I do this using ICriteria instead?

score 2 · Accepted Answer · answered Nov 01 '10 at 14:10

2

try something like this. It's uncompiled, but it should work as long as IEnumerable<TicketAction> Actions and Person TakenBy is never null. If you set it to an empty list in the ticket constructor, that will solve a problem with nulls.

If you add a reference to the Ticket object in the TicketAction, you could do something like this:

ICriteria criteria = _session.CreateCriteria(typeof(CreateAction))
   .Add(Expression.Eq("TakenBy.Name", createdByName));

var actions = criteria.List<CreateAction>();

var results = from a in criteria.List<>()
   select a.Ticket;

In my experience, nhibernate has trouble with criteria when it comes to lists when the list is on the object side - such as is your case. When it is a list of values on the input side, you can use Expression.Eq. I've always had to find ways around this limitation through linq, where I get an initial result set filtered down as best as I can, then filter again with linq to get what I need.

answered Nov 01 '10 at 14:10

Josh

16,286
25
113
158

I'd rather not have to add a back-reference to a `TicketAction`'s `Ticket` if I can help it since that will introduce other problems, but thanks for the tip. :) – Brant Bobby Nov 01 '10 at 14:32
The other way we handled situations like this was to actually create the HQL in a string builder. We tried using Linq to Nhibernate expressions, but those too do not support collections when the collection is on the object side. Unfortunately, the "Contains" method doesn't translate through in Linq to nHibernate. – Josh Nov 01 '10 at 16:48
I went with your suggestion after all (adding a reference to the Ticket on TicketAction), but I end up with an error: "NHibernate.QueryException: could not resolve property: TakenBy.Name of: CreateAction" – Brant Bobby Nov 01 '10 at 21:13
I believe that is because TakenBy is null by default. In the constructor for TicketAction, set it to an empty Person. Also, with nhibernate, your properties/methods need to be virtual. – Josh Nov 02 '10 at 13:16

score 0 · Answer 2 · answered Nov 03 '10 at 07:45

OfType is supported. I'm not sure ToUpper is though, but as SQL ignores case it does not matter (as long as you are not also running the query in memory...). Here is a working unit test from the nHibernate.LINQ project:

var animals = (from animal in session.Linq<Animal>()
               where animal.Children.OfType<Mammal>().Any(m => m.Pregnant)
               select animal).ToArray();
Assert.AreEqual("789", animals.Single().SerialNumber);

Perhaps your query should look more like the following:

var animals = (from ticket in session.Linq<Ticket>()
               where ticket.Actions.OfType<CreateAction>().Any(m => m.TakenBy.Name.Contains("john"))
               select ticket).ToArray();

How do I express this LINQ query using the NHibernate ICriteria API?

2 Answers2