Mapping many-to-many without a join table in Hibernate/NHibernate

Question

I have two tables:

CalendarEntry
  Id
  Date
  ...

Holiday
  Id
  Date
  ...

In my CalendarEntry class, I have a property like this

public ISet<Holiday> Holidays { ... }

which I want to associate to the Holiday instances that occur on the same Date as the CalendarEntry. However, I can't come up with how to do this.

I've tried mapping it as one-to-many, but one-to-many automatically assumes that it should perform the join using CalendarEntry's Id column (presumably since it's the only property that is guaranteed to be unique, which it must be to be one-to-many).

I've tried mapping it as many-to-many, but it seems that many-to-many requires a separate join table, which I don't want in this case.

My question is: is it possible to map this in NHibernate, and how should I do it? If it's not possible, why?

Answer 1

I think what you need to do is set up a query to get the Holiday entries. I'm not sure this can be done using a mapping.

---

Additional info from my original answer, which may not apply: You can have the many-to-many relationship in NHibernate without creating a separate entity class for the join table entries, but the underlying data still needs to exist somewhere in the database.

Answer 2

You should be able to do this using a property-ref, which I believe is available in NHibernate 2.1. I found it in the link text and here's the jira for adding it to NHibernate.

I'm not sure this would work on a date column and you're definitely out of luck if your date field contains time data.

You can always leave the relationship out of the model and access the collection through a repository method, i.e. GetHolidaysForDate. This would make more sense to me since holidays are distinct from calendar entries and culture specific. Unless you're using the non-US meaning equivalent to "vacation."

Answer 3

You could create a database view (or derived table query) containing the appropriate ids from CalendarEntry and Holiday, then map a many-to-many relationship using the view - this technically avoids having a join table, while allowing NHibernate to work the way it wants to.

You'll want to make sure NHibernate doesn't attempt to update the collections (probably some combination of inverse="true"and cascade="none") and avoid modifying the them in code - I presume you're okay with this given you don't want a table at all.