Date Filtered Collections without Functions

Question

Hi,

I have an entity similar to the below:

public class Entity
{
    public List<DateItem> PastDates { get; set; }
    public List<DateItem> FutureDates { get; set; }
}

public class DateItem
{
    public DateTime Date { get; set; }
    /*
     * Other Properties
     * */
}

Where PastDates and FutureDates are both mapped to the same type/table. I have been trying to find a way to have the Past and Future properties mapped automagically by Nhibernate. The closest I came was where clause on the mapping as follows

HasMany(x => x.PastDates)
            .AsBag().Cascade
            .AllDeleteOrphan()
            .KeyColumnNames.Add("EventId").Where("Date < currentdate()")
            .Inverse();

Where currentdate is a UDF. I do not want to have these database specific functions if I can avoid it, mostly because i can't then test my DAL with SQLite as it doesn't support functions or stored procedures.

At the moment I am building the past and future collections using Criteria and adding to my DTO manually.

Anyone know how this could be achieved without using any UDFs?

Many thanks,

Answer 1

I'm not certain, but I don't think the Where string ever gets parsed; it's just pure SQL and gets injected directly into the query. I could be wrong though, so it might be worth looking at something like extending the dialect with your own custom functions (which means you could do the same to your in-memory dialect too).