Exclude specific value from a Min/Max aggregate function using ICriteria

I have a schedule (Voyages) table like this:

ID      Arrival        Departure    OrderIndex
1     01/01/1753    开发者_运维知识库  02/10/2009        0
1     02/11/2009      02/15/2009        1
1     02/16/2009      02/19/2009        2
1     02/21/2009      01/01/1753        3

2     01/01/1753      03/01/2009        0
2     03/04/2009      03/07/2009        1
2     03/09/2009      01/01/1753        2

By design i save '01/01/1753' as a default value if the user doesn't fill a the field on the capture screen and for the very first Arrival and the very last Departure which are never provided. Im using Nhibernate and Criteria, and im wondering whats the best way to query this data if i want to know the First departure and last arrival for each voyage in the table.

My first thought was a groupby (ID) and then do some Min and Max with the arrival and departure but the `'01/01/1753' VALUE is messing aronud.

...
.SetProjection(Projections.ProjectionList()
               .Add(Projections.GroupProperty("ID"), "ID")
               .Add(Projections.Min("DepartureDate"), "DepartureDate")
               .Add(Projections.Max("ArrivalDate"), "ArrivalDate")
               )
...

So is there a way to skip this value in the Min function comparison (without losing the whole row of data), or there is a better way to do this, maybe utilizing the OrderIndex that always indicate the correct order of the elements, maybe ordering ASC taking the 1st and then Order DESC and taking the 1 st again, but im not quite sure how to do that with criteria syntax.

The best (and sanest) way, of course, would be to use NULL values instead of minimum datetimevalues. If you had done that (or change your application to do that), then the code you wrote will work as written. The way it is now, I guarantee those bogus values will come back to haunt someone eventually (if this is a real application). Maybe not you, but the next guy. Of course, you should also normalize this table...

But anyways. More on that stuff later. You asked a specific question.

Here is the code that should work (not for lack of testing -- keep reading).

DateTime bogusDate = new DateTime(1753, 1, 1);

ICriteria criteria =
    session.CreateCriteria(typeof(Voyage))
    .SetProjection
    (
        Projections.ProjectionList()
        .Add
        (
            Projections.Min
            (
                Projections.Conditional
                (
                    Restrictions.Eq("Departure", bogusDate),
                    Projections.Constant(DateTime.MaxValue, NHibernateUtil.DateTime),
                    Projections.Property("Departure")
                )
            )
        )
        .Add(Projections.Max("Arrival"))
        .Add(Projections.GroupProperty("Id"))
    );

(The only reason I'm sending a DateTime.MaxValue is because NHibernate forces the conditional true/false results to the same type, and I couldn't figure out how to get a NULL into the true part.)

That code sends this query to the DB engine (I'm using SQL Server 2005 dialect backed by SQL Express 2005):

SELECT min((case when this_.Departure = ? then ? else this_.Departure end)) as y0_,
    max(this_.Arrival) as y1_,
    this_.Id as y2_
    FROM Voyages this_
    GROUP BY this_.Id;

@p0 = 1/1/1753 12:00:00 AM,
@p1 = 12/31/9999 11:59:59 PM

Which looks alright. Now, I say this should work, because when you plug in the parameters and run the query directly on the engine, it gives the desired results. However, on my machine, using NHibernate, it all blows up:

System.Data.SqlClient.SqlException: Incorrect syntax near '?'.

Which tells me that SQL Server no likie positional parameters in CASE statements. Braindead if true. This may not be an issue in 2008+, though I haven't tested -- I'm mentioning SQL Server specifically because I'm assuming that's what you're using since 1/1/1753 is the minimum date it allows in a datetime.

So, where does that leave this problem? There are options.

1. Fix up the database schema as mentioned in my very first paragraph (ideal). Note that you won't have to allow any NULL values into your data if the schema is normalized.
2. See if you can write the query using HQL instead (I'm not an expert, so I couldn't even say if that's possible).
3. Discover that this isn't an issue in SQL 2008+ and/or your target database engine(s) and that's all you're ever going to target.
4. Rewrite the query to bypass the crazy values entirely and exploit the OrderIndex column instead. I've written this in SQL -- I'm not sure how to write this using ICriteria (and if you want the punishment of that, do yourself a favour and spend the time on option #1 instead). Note that this is less than half as fast as the original query, which is nearly optimal AFAIK:

SELECT
    s.Id,
    v1.Departure,
    v2.Arrival
    FROM
    (
        SELECT DISTINCT
            Id,
            MAX(OrderIndex) AS MaxIndex,
            MIN(OrderIndex) AS MinIndex
            FROM Voyages
            GROUP BY Id
    ) s
    INNER JOIN Voyages v1 ON v1.Id = s.Id AND v1.OrderIndex = MinIndex
    INNER JOIN Voyages v2 ON v2.Id = s.Id AND v2.OrderIndex = MaxIndex