Nhiberate Criterion Expression Combining "ands" and "ors"

Question

I am having a little trouble with building my criteria object. Normally, I Build my cruteria similarly to this:

ISession session = GetSession();
ICriteria criteria = session.CreateCriteria(typeof(MyObject))
                .Add(Expression.Gt("StartDate", DateTime.Now.ToUniversalTime()))
                .Add(Expression.Eq("SubObject.SubObjectId", subObjectId))
                .AddOrder(new Order("StartDate", true));

What I need to do is create a criterion that looks more like this:

WHERE ((a.EndDate IS NOT NULL AND a.EndDate >= '{Now}') OR a.EndDate IS NULL) AND ((a.SubObject IS NOT NULL AND a.SubObject.SubObjectId = '{Id}') OR a.SubObject IS NULL) AND a.StartDate <= '{Now}'

Yes, I know I could just use the HQL, but if possible, I'd like to use the Critera instead.

The subobject and enddate can be null and if they are null I want to include them in the selection, but if they are not null, I need to compare them to values. The id of the subobject if it isn't null and the current time if enddate is not null.

I know I need a disjunction for the "OR"'s, but I'm just not sure on the placement and ordering of them as far as the criteria goes.

Answer 1

var now = DateTime.Now;
var myObjects = session
    .CreateCriteria<MyObject>()
    .CreateAlias("SubObject", "so")
    .Add(
        Expression.And(
            Expression.Or(
                Expression.And(
                    Expression.IsNotNull("SubObject"),
                    Expression.IdEq(10)
                ),
                Expression.IsNull("SubObject")
            ),
            Expression.And(
                Expression.Or(
                    Expression.And(
                        Expression.IsNotNull("EndDate"),
                        Expression.Ge("EndDate", now)
                    ),
                    Expression.IsNull("EndDate")
                ),
                Expression.Le("StartDate", now)
            )
        )
    )
    .List<MyObject>();