CODEWITHSUNDEEP
c#nhibernate
Sean Anderson
Sean Anderson

Reputation: 29251

Working with two non-directly related tables in one NHibernate query

I am new to NHibernate and am not sure if what I am asking makes sense.

I am trying to rewrite some code I currently have:

public IEnumerable<Order> GetByQueue(OrderStatus orderStatus, Queue queue)
{
    var criteria = NHibernateSession.CreateCriteria(typeof (TaskDevice), "TaskDevice");

    //Pull up all Tasks where a Task's TaskDevice's SourceSiteID or DestinationSiteID are represented in a Queue's QueueLocations.
    foreach(QueueLocation queueLocation in queue.QueueLocations)
    {
        criteria.Add(
                Expression.Disjunction()
                    .Add(Restrictions.Eq("OriginalLocationID", queueLocation.ComponentID))
                    .Add(Restrictions.Eq("LocationID", queueLocation.ComponentID))
            );
    }

    //Get a hold on all the Tasks returned from TaskDevices.
    List<Task> tasks = criteria.List<TaskDevice>().Select(taskDevice => taskDevice.Task).ToList();

    //Return all Orders of the given Tasks whose OrderStatus matched the provided orderStatus.
    return tasks.Where(task => task.Order.OrderStatus == orderStatus).Select(task => task.Order);
}

This code currently depends on a Queue object. I would like to change this code such that a queueID is provided instead of a Queue object. The table QueueLocation contains 'QueueID' for one of its columns.

This means that I now need to interact with another table in my database, QueueLocation, load the QueueLocation who has a QueueID matching the provided QueueID, and then emulate the adding of restrictions without iterating over a Queue object.

Task does not know of Queue and Queue does not know of Task. They are related by the fact that a Queue may contain a QueueLocation whose ComponentID matches a Task's OriginalLocationID or LocationID.

If I change my initial criteria declaration to:

var criteria = NHibernateSession
    .CreateCriteria(typeof (TaskDevice), "TaskDevice")
    .CreateCriteria("QueueLocation", "QueueLocation");

then an exception is generated indication that NHibernate could not find property QueueLocation on TaskDevice. This is a valid exception -- TaskDevice does not know of QueueLocation.

I am wondering how to load two non-related tables using NHibernate such that I may filter my restrictions fully through NHibernate in one query. Is this possible?

Upvotes: 0

Views: 141

Answers (1)

Diego Mijelshon
Diego Mijelshon

Reputation: 52745

Criteria is not a good API for queries with entities that are not related in the model.

Use HQL instead.

Upvotes: 1

Related Questions