NHibernate - how to get an item that is not referenced by an item in another table

Question

Let's say I have a class Voucher:

public class Voucher
{
    public Guid Id {get;set;}
    public DateTime DateAvailable {get;set;}
}

and a class Entry

public class Entry
{
    public Guid Id {get;set;}
    public Voucher Voucher {get;set;}
    // ... other unrelated properties
}

How can I create an NHibernate Criteria query that finds the first available voucher that is NOT currently assigned to an Entry?

The equivalent SQL would be

select 
    v.Id, v.DateAvailable 
from 
    Voucher v
        left join Entries e on e.VoucherId = v.Id
where 
    v.DateAvailable <= getutcdate() and
    e.Id is null

Edit: I'm still unable to figure this one out. The Voucher table has no reference to the Entries table, but I need to find the first voucher (by date order) that has not been assigned to an entry. This seems like such a simple task, but everything I keep reading about using NHibernate criteria left joins requires the Voucher object to have a property that references the entry. Surely there's a way to invert the query or add a reference property to the Voucher object without modifying the database to have each table reference the other.

Edit 2: For what it's worth, I don't think it's possible to do what I was trying to do without some modifications. I eventually got a query to work using the Entry as the primary criteria with the Voucher as a sub-criteria, but then UniqueResult returned null, even if the data was there. I guess it just couldn't make the association.

In case anyone runs into this, I ended up making a foreign key in each table that references the other and using the References<> mapping to associate the two. It's not idea, because I have to manually set each entity's sub property to the other to make the association bidirectional, but it at least works without a ton of changes.

Answer 1

From memory, might not work:

session.CreateQuery<Entry>().CreateAlias("Voucher","v").Add(Restrictions.And(
    Restrictions.Lt("DateAvailable",DateTime.Now),
    Restrictions.Eq("v.Id",null")
).List<Voucher>();

Answer 2

The following should work using HQL:

IQuery query = session.CreateQuery(@"
    FROM
        Voucher v LEFT JOIN
        Entry e
    WHERE
        e.ID IS NULL AND
        v.DateAvailable <= :now
");

query.SetParameter("now", DateTime.Now);

// If you want to restrict to only the first result
query.SetMaxResults(1);
Voucher v = query.UniqueResult<Voucher>();

// Otherwise, get a list of results...
// List<Entry> results = query.List<Entry>();

Answer 3

Translating your SQL literally:

var voucher = NHibernateSessionManager.Session.CreateCriteria<Voucher>("v")
                     .Add(Restrictions.Le("v.DateAvailable", DateTime.UtcNow))
                     .CreateCriteria("Entries", "e")
                  .Add(Restrictions.IsNull("e.Id"))
                        .SetMaxResults(1)
                        .UniqueResult<Voucher>();

Now if I understand this correctly there may be an alterantive. If the statement: "..finds the first available voucher that is NOT currently assigned to an Entry..." is the same with the statment "finds the first available voucher that has no entries..." (since the voucher is the entity that has many entries... according to your classes) then you could do:

var voucher = NHibernateSessionManager.Session.CreateCriteria<Voucher>()
       .Add(Restrictions.IsEmpty("Entries"))
                .Add(Restrictions.Le("DateAvailable", DateTime.UtcNow))
                .SetMaxResults(1)
                .UniqueResult<Voucher>();

...assuming that you have mapped the Entries property in the Voucher entity.

But maybe I got it wrong...