Finding Null IDs with HQL

I'm constructing a hql query that needs to return orders where the User's ID is null:

        IList<Order> rows = DataContext.LoadList<Order>(
           "from Order " +
           "where OrderDate < ? " +
           "  and User.ID is null" //have also tried 'and User is null'
           , DateTime.Today.AddMonths(-1));

This returns no records even though a direct query against the db returns a few dozen.

If I use the same query but replace the 'is null' with '33' (a valid userID), I'll get that user's orders. So the foundation of the query is valid - something's wrong with how I'm exp开发者_如何学Pythonressing the 'is null' bit.

Probably has something to do with the fact that, in the .NET project the Order object contains a complex User object where as in the database, the Orders table just holds integer (ID) that's a foreign key to the User table.

Still - since 'and User.ID = 33' works as expected I don't follow why 'is null' doesn't.

This answer suggests I need to switch of the Criteria route. This answer suggests that my current code should work.

thx

---

Rather than building a query statement, use a Query factory approach:

        Query q = new Query();            
        q.Criteria.Add(new Criteria("User.ID", CriteriaOperator.IsNull));

Works.