fluent / nhibernate mapping question

Question

Hey Guys I would very much appreciate some help with the following. We're using fluent to control the mappings for nhibernate and its all gone well so far. We've basically got a scheduled based CMS system and I'm having problems using the HasMany mapping to exclude the non-live child categories.

So we have the following Data Tables, simplified slightly which map to sensible BO's.

[Category] Id Name Parent_Id Schedule_Id

[Schedule] Id IsPaused (Bit 0/1) StartDate StopDate

The CategoryMap looks a bit like this (vb.net sorry!).

Public Sub New()
    Id(Function(x) x.Id)
    Map(Function(x) x.Name)
    HasMany(Function(x) x.Children).Inverse().KeyColumn("Parent_id").Cascade.All()
    References(Function(x) x.Parent)
    References(Function(x) x.Schedule).ForeignKey("Schedule_id").Fetch.Join().Nullable()
End Sub

What I would like to do is add a filter on the HasMany mapping, but can't seem to get Where to work as I need to.

Could someone please point me in the right direction?

Many Thanks,

Mike.

Answer 1

You don't show the child class so I'm not sure what the filter might be. Assuming you want to filter on an IsActive bit column the mapping would be:

Public Sub New()
    Id(Function(x) x.Id)
    Map(Function(x) x.Name)
    HasMany(Function(x) x.Children).Inverse().KeyColumn("Parent_id").Cascade.All()
        .Where("IsActive = 1")
    References(Function(x) x.Parent)
    References(Function(x) x.Schedule).ForeignKey("Schedule_id").Fetch.Join().Nullable()
End Sub

The key point is that the Where clause contains a snippet of SQL not HQL, so you have to use database field names instead of property names.