Background, using FluentNHibernate, lastest dev build working with NHibernate 3.0.
Here is the type declarations for WorkIncident:
// Enumeration used in class below.
public enum TicketStatus
{
Open = 1,
Closed = 10,
Hold = 20
}
// Ticket class.
public class WorkIncident
{
public virtual int EntryId { get; set; }
public virtual int TicketNumber { get; set; }
public virtual string ModifierNtId { get; set; }
public virtual DateTime ModifiedDate { get; set; }
public virtual TicketStatus Status { get; set; }
public virtual int Version { get; set; }
public virtual string Title { get; set; }
public virtual string Details { get; set; }
}
// FluentNHibernate mapping
public class WorkIncidentMap : ClassMap<WorkIncident>
{
public WorkIncidentMap()
{
Table("incident_details");
Id( wi => wi.EntryId, "wiid");
Map(wi => wi.TicketNumber, "workitem_number");
Map(wi => wi.Title, "workitem_title");
Map(wi => wi.Details, "workitem_comment");
Map(wi => wi.ModifiedDate, "workitem_modified_on");
Map(wi => wi.ModifierNtId, "modified_by_worker_nt_id");
Map(wi => wi.Status, "workitem_status_lookup_id").CustomType<EnumType<Status>>();
Map(wi => wi.Version, "workitem_version");
}
}
The mapping works fine, and I can do queries like the following with no problems:
session.QueryOver<AltirisIncident>()
.Where(ai => ai.ModifierNtId == worker.Name.Replace("\\", @"\"))
.AndRestrictionOn(ai => ai.ModifiedDate)
.IsBetween(DateTime.Today)
.And(DateTime.Today.AddDays(1))
.List<WorkIncident>();
This gives me all of the work items (basically help desk trouble tickets) touched by a specific user on the current date.
However, I have been having trouble translating the following HQL into a fluent declaration:
from WorkIncident as t1
where t1.ModifierNtId = :ntid
and t1.ModifiedDate between :startdate and :enddate
and t1.Status = :status
and (t1.Version = 1
or t1.TicketNumber in (
select t2.TicketNumber
from WorkIncident as t2
where t2.Status != t1.Status
and t2.TicketNumber = t1.TicketNumber
and t2.Version = t1.Version - 1))
This query gives me the list of all work items that were placed in a closed status by a worker. Given the way the tickets are stored in the database (each ticket has multiple records (one for each update) and supervisors will often add notes to a ticket after a worker has closed it, leads to situations where I can't just look at the last version number with a closed status to reliably tell me who closed a ticket.
Any help would be greatly appreciated, as I would prefer to move away from HQL and magic strings as much as possible.